删除 Google 表单提交的文件

2022-01-10 00:00:00 google-drive-api google-apps-script javascript google-forms

此处的工作代码:

Google 生成的文件夹.文件提交到这个文件夹.

新文件夹中的重命名文件.原始文件从上面的文件夹中删除.

原始文件现在显示在云端硬盘中，而不是在文件夹中，而是在文件夹中.此文件的名称与最初上传的相同.进入passes"文件夹然后从该文件夹中删除的那个.

片段

//重命名通行证if (itemResponses[f].getItem().getTitle() == "PASSES") {var 文件 = itemResponses[f].getResponse();//Logger.log(files.length);if (files.length > 0) {for (var n in files) {var dFile = DriveApp.getFileById(files[n]);dFile.setName("LSS - " + year + " - " + teamName + " - " + "PASSES - " + 今天);teamFolder.addFile(dFile);//将提交的文件移动到该文件夹passFolder.removeFile(dFile);//从提交文件夹中删除DriveApp.getRootFolder().removeFile(dFile)//(不起作用)从驱动器文件夹中删除DriveApp.removeFile(dFile)//(不工作)从驱动器文件夹中删除}}

完整代码

函数 getLastResponse() {var form = FormApp.openById('ID');var today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yyyy hh:mm a");var year = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "YYYY");Logger.log(今天);var formResponses = form.getResponses();//Logger.log(formResponses.length);var formResponse = formResponses[formResponses.length-1];var respondentEmail = formResponse.getRespondentEmail()var itemResponses = formResponse.getItemResponses();Logger.log(itemResponses.length);var teamName = itemResponses[2].getResponse();//Logger.log("团队名称:" + teamName);//检查文件夹var dropbox = "Lititz Summer Showcase Team Check In (文件回复)";var 文件夹，文件夹 = DriveApp.getFoldersByName(dropbox);var teamBox = 团队名称；var teamFolder, teamFolders = DriveApp.getFoldersByName(teamBox);var passFolder = DriveApp.getFolderById('ID');var rosterFolder = DriveApp.getFolderById('ID');var teamInfoFolder = DriveApp.getFolderById('ID');var permissionToTravelFolder = DriveApp.getFolderById('ID');if (folders.hasNext()) {//检查驱动器是否有文件夹文件夹 = 文件夹.next();} else {//如果不创建文件夹文件夹 = DriveApp.createFolder(dropbox);}if (teamFolders.hasNext()) {//检查团队是否存在文件夹团队文件夹 = 团队文件夹.next();} else {//如果不创建文件夹teamFolder = 文件夹.createFolder(teamBox);teamFolder.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.COMMENT);}for (var f = 0; f < itemResponses.length; f++) {Logger.log(itemResponses[f].getItem().getType());Logger.log(itemResponses[f].getItem().getTitle());if (itemResponses[f].getItem().getType() == "FILE_UPLOAD") {Logger.log("有一个文件上传");//重命名通行证if (itemResponses[f].getItem().getTitle() == "PASSES") {var 文件 = itemResponses[f].getResponse();//Logger.log(files.length);if (files.length > 0) {for (var n in files) {var dFile = DriveApp.getFileById(files[n]);dFile.setName("LSS - " + year + " - " + teamName + " - " + "PASSES - " + 今天);teamFolder.addFile(dFile);//将提交的文件移动到该文件夹passFolder.removeFile(dFile);//从提交文件夹中删除DriveApp.removeFile(dFile);//从驱动器文件夹中删除}}//重命名名册} else if (itemResponses[f].getItem().getTitle() == "ROSTER") {var 文件 = itemResponses[f].getResponse();//Logger.log(files.length);if (files.length > 0) {for (var n in files) {var dFile = DriveApp.getFileById(files[n]);dFile.setName("LSS - " + year + " - " + teamName + " - " + "ROSTER - " + 今天);teamFolder.addFile(dFile);}}//重命名团队信息表} else if (itemResponses[f].getItem().getTitle() == "TEAM INFO SHEET") {var 文件 = itemResponses[f].getResponse();//Logger.log(files.length);if (files.length > 0) {for (var n in files) {var dFile = DriveApp.getFileById(files[n]);dFile.setName("LSS - " + year + " - " + teamName + " - " + "TEAM INFO SHEET - " + 今天);teamFolder.addFile(dFile);}}//重命名旅行许可} else if (itemResponses[f].getItem().getTitle() == "PERMISSION TO TRAVEL") {var 文件 = itemResponses[f].getResponse();//Logger.log(files.length);if (files.length > 0) {for (var n in files) {var dFile = DriveApp.getFileById(files[n]);Logger.log(ownerEmail);dFile.setName("LSS - " + year + " - " + teamName + " - " + "旅行许可 - " + 今天);teamFolder.addFile(dFile);}}}}//END '如果文件上传'}//结束循环}//结束函数

解决方案

这个答案怎么样?

问题:

从谷歌表单上传的流程如下.

在表单中上传文件时，文件会创建到根文件夹.
提交表单时，通过将文件名重命名为表单创建的文件夹来复制根文件夹中的文件.

在上述情况下，根文件夹中的文件与 Google Form 创建的文件夹中的文件不同.这样，您的SNIPPET"中的 DriveApp.getRootFolder().removeFile(dFile) 不起作用.这就是你的脚本问题的原因.

解决方法:

您要删除保留在根文件夹中的文件.

为了删除根文件夹中创建的文件，这个解决方法怎么样?

很遗憾，无法从表单响应中检索到原始文件名.但是复制到表单创建的文件夹中的文件的文件名格式为 {original filename} - ####.{extension}.在此解决方法中，从该文件名中检索原始文件名，并使用检索到的原始文件名将文件移动到回收站.

示例脚本:

此示例脚本由可安装的表单提交触发器运行.因此，当表单提交时，脚本运行并将上传的文件移动到目标文件夹，并将根文件夹中的原始文件移动到垃圾箱.

在运行脚本之前:

在此示例脚本中，假设该脚本是 Google Form 的容器绑定脚本.请注意这一点.

将脚本粘贴到脚本编辑器后，请将目标文件夹ID设置为脚本.
请安装可安装的表单提交触发器.如果触发器已安装，请将其移除并重新安装.
请使用 Google 表单上传并提交文件.这样，脚本就会运行.

脚本:

函数formsubmit(e) {var destFolderId = "###";//目标文件夹 ID如果(e){实用程序.sleep(3000);//这是必需的.var destfolder = DriveApp.getFolderById(destFolderId);var items = e.response.getItemResponses();for (var i = 0; i < items.length; i++) {if (items[i].getItem().getType() == "FILE_UPLOAD") {var files = items[i].getResponse();for (var j = 0; j < files.length; j++) {var file = DriveApp.getFileById(files[j]);var 文件名 = file.getName();//将上传的文件移动到目标文件夹.var uploadFile = DriveApp.getFileById(files[j]);var sourcefolder = uploadFile.getParents().next();destfolder.addFile(文件);源文件夹.removeFile(文件);//获取原始文件名.var p1 = 文件名.split(" - ");var extension = p1[p1.length - 1];p1.pop();var name = p1.join(" - ");变种 p2 = "";if (extension.indexOf(".") > -1) {p2 = "."+ extension.split(".")[1];}var orgFilename = 名称 + p2;//将上传的文件移动到垃圾箱.var orgFiles = DriveApp.getRootFolder().getFilesByName(orgFilename);if (orgFiles.hasNext()) {var orgFile = orgFiles.next();orgFile.setTrashed(true);}}}}} 别的 {throw new Error("此示例脚本由可安装的表单提交触发器运行.");}}

注意:

这是一个解释此解决方法的简单示例脚本.因此，请根据您的情况进行修改.
在我的环境中，发现 Utilities.sleep(3000) 是必需的.上传文件并复制文件后，将运行可安装的表单提交触发器.此时，如果不使用Utilities.sleep(3000)，则在复制文件完成之前移动文件.由此，发生错误.所以我用了它.
- 但我不确定 3 秒的等待时间是否最好.所以如果在你的环境中出现错误，请修改这个.
- 我认为当上传大文件时，这个值可能需要很大.或者我认为通过时间驱动的触发器运行脚本可能会更好.
在这个示例脚本中，我使用了可安装的表单提交触发器.并且当表单提交时，根文件夹中的原始文件被移动到垃圾箱.
- 但我认为你也可以通过时间驱动的触发器来运行脚本.在这种情况下，您可以通过打开表单来检索响应项.关于这一点，请根据您的情况进行选择.

参考资料:

可安装触发器
setTrashed(trashed)
睡眠(毫秒)

WORKING CODE HERE: https://jsfiddle.net/nateomardavis/e0317gb6/

ORIGINAL QUESTION BELOW

How do I remove a form-submitted file from Drive itself?

I'm having trouble sorting out why a google form is submitting files to both my drive (not in a folder) but also into an auto-generated submission folder.

I've been able to move the renamed file to a new folder and delete the copy in the auto-generated submission folder. I cannot figure out how to remove the copy that's just listed in "Drive", not in any folder.

THE PROCESS (EDIT)

Let me try to explain the process more. I have a form that collects files. Google automatically makes a folder and sub-folders. I have successfully renamed the submitted files, moved them to a new folder, and deleted them from the Google-generated folder. However, a copy of the original, unchanged file is going to Google Drive, the root folder. Steps 1-3 (below) work as expected. Step 4 is where I'm running into issues.

The original file being uploaded to a form. Note the file name.
The Google-generated folder. The file is submitted this folder.
The renamed file in a new folder. The original file is deleted from the folder above.
The original file is now showing up in Drive, not in a folder but there. The name of this file is the same as the originally uploaded one. The one which went to the "passes" folder and was then deleted from that folder.

SNIPPET

//RENAME PASSES
if (itemResponses[f].getItem().getTitle() == "PASSES") { 
var files = itemResponses[f].getResponse();
//Logger.log(files.length);
if (files.length > 0) {
  for (var n in files) {
    var dFile = DriveApp.getFileById(files[n]);
    dFile.setName("LSS - " + year + " - " + teamName + " - " + "PASSES - " + today );
    teamFolder.addFile(dFile);   //MOVE SUBMITTED DOCUMENTS TO THAT FOLDER
    passesFolder.removeFile(dFile); //REMOVE FROM SUBMISSION FOLDER
    DriveApp.getRootFolder().removeFile(dFile) // (DOES NOT WORK) REMOVE FROM DRIVE FOLDER
    DriveApp.removeFile(dFile) // (DOES NOT WORK) REMOVE FROM DRIVE FOLDER
  }
}

FULL CODE

function getLastResponse() {

  var form = FormApp.openById('ID');

  var today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yyyy hh:mm a");
  var year = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "YYYY");
  Logger.log(today);

  var formResponses = form.getResponses();
  //Logger.log(formResponses.length);
  var formResponse = formResponses[formResponses.length-1];
  var respondentEmail = formResponse.getRespondentEmail()
  var itemResponses = formResponse.getItemResponses();
  Logger.log(itemResponses.length);
  var teamName = itemResponses[2].getResponse();
  //Logger.log("team name: " + teamName);


  //CHECK FOLDERS
   var dropbox = "Lititz Summer Showcase Team Check In (File responses)";
    var folder, folders = DriveApp.getFoldersByName(dropbox);

    var teamBox = teamName;
    var teamFolder, teamFolders = DriveApp.getFoldersByName(teamBox);

    var passesFolder = DriveApp.getFolderById('ID');
    var rosterFolder = DriveApp.getFolderById('ID');
    var teamInfoFolder = DriveApp.getFolderById('ID');
    var permissionToTravelFolder = DriveApp.getFolderById('ID');

    if (folders.hasNext()) { //CHECK IF DRIVE HAS FOLDER FOR FORM
      folder = folders.next();
    } else { //IF NOT CREATE FOLDER
      folder = DriveApp.createFolder(dropbox);
    }

    if (teamFolders.hasNext()) {  //CHECK IF FOLDER FOR TEAM EXISTS 
      teamFolder = teamFolders.next();
    } else { //IF NOT CREATE FOLDER
      teamFolder = folder.createFolder(teamBox);
      teamFolder.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.COMMENT);
    }

for (var f = 0; f < itemResponses.length; f++) {
    Logger.log(itemResponses[f].getItem().getType());
    Logger.log(itemResponses[f].getItem().getTitle());
  if (itemResponses[f].getItem().getType() == "FILE_UPLOAD") {
    Logger.log("THERE IS A FILE UPLOAD");

    //RENAME PASSES
    if (itemResponses[f].getItem().getTitle() == "PASSES") { 
    var files = itemResponses[f].getResponse();
    //Logger.log(files.length);
    if (files.length > 0) {
      for (var n in files) {
        var dFile = DriveApp.getFileById(files[n]);
        dFile.setName("LSS - " + year + " - " + teamName + " - " + "PASSES - " + today );
        teamFolder.addFile(dFile);   //MOVE SUBMITTED DOCUMENTS TO THAT FOLDER
        passesFolder.removeFile(dFile); //REMOVE FROM SUBMISSION FOLDER
        DriveApp.removeFile(dFile); // REMOVE FROM DRIVE FOLDER
      }
    }
    //RENAME ROSTER
    } else if (itemResponses[f].getItem().getTitle() == "ROSTER") {
      var files = itemResponses[f].getResponse();
    //Logger.log(files.length);
    if (files.length > 0) {
      for (var n in files) {
        var dFile = DriveApp.getFileById(files[n]);
        dFile.setName("LSS - " + year + " - " + teamName + " - " + "ROSTER - " + today );
        teamFolder.addFile(dFile);

      }
    }
   //RENAME TEAM INFO SHEET
   } else if (itemResponses[f].getItem().getTitle() == "TEAM INFO SHEET") {
      var files = itemResponses[f].getResponse();
    //Logger.log(files.length);
    if (files.length > 0) {
      for (var n in files) {
        var dFile = DriveApp.getFileById(files[n]);
        dFile.setName("LSS - " + year + " - " + teamName + " - " + "TEAM INFO SHEET - " + today );
        teamFolder.addFile(dFile);
      }
    }

  //RENAME PERMISSION TO TRAVEL
  } else if (itemResponses[f].getItem().getTitle() == "PERMISSION TO TRAVEL") {
      var files = itemResponses[f].getResponse();
    //Logger.log(files.length);
    if (files.length > 0) {
      for (var n in files) {
        var dFile = DriveApp.getFileById(files[n]);
        Logger.log(ownerEmail);
        dFile.setName("LSS - " + year + " - " + teamName + " - " + "PERMISSION TO TRAVEL - " + today );
        teamFolder.addFile(dFile);
      }
    }
    }
}//END 'IF FILE UPLOAD'
}//END FOR LOOP


}//END FUNCTION

解决方案

How about this answer?

Issue:

The flow of upload from Google Form is as follows.

When the file is uploaded in the Form, the file is created to root folder.
When the form is submitted, the file in the root folder is copied by renaming the filename to the folder created by the form.

In above case, the file in root folder is different from the file in the folder created by Google Form. By this, DriveApp.getRootFolder().removeFile(dFile) in your "SNIPPET" didn't work. This is the reason of your issue of script.

Workaround:

You want to delete the file remained in the root folder.

In order to delete the file created in the root folder, how about this workaround?

Unfortunately, the original filename cannot be retrieved from the form response. But the file which was copied to the folder created by the form has the filename of the format like {original filename} - ####.{extension}. In this workaround, the original filename is retrieved from this filename, and move the file to the trash using the retrieved original filename.

Sample script:

This sample script is run by the installable form submit trigger. So when the form was submitted, the script is run and the uploaded file is moved to the destination folder and the original file in the root folder is moved to the trash.

Before run the script:

In this sample script, it supposes that the script is the container-bound script of Google Form. Please be careful this.

After cope and paste the script to the script editor, please set the destination folder ID to the script.
please install the installable form submit trigger. If the trigger has already been installed, please remove it and install again.
Please upload and submit a file using Google Form. By this, the script is run.

Script:

function formsubmit(e) {
  var destFolderId = "###"; // Destination folder ID

  if (e) {
    Utilities.sleep(3000); // This is required.
    var destfolder = DriveApp.getFolderById(destFolderId);
    var items = e.response.getItemResponses();
    for (var i = 0; i < items.length; i++) {
      if (items[i].getItem().getType() == "FILE_UPLOAD") {
        var files = items[i].getResponse();
        for (var j = 0; j < files.length; j++) {
          var file = DriveApp.getFileById(files[j]);
          var filename = file.getName();

          // Move uploaded file to the destination folder.
          var uploadedFile = DriveApp.getFileById(files[j]);
          var sourcefolder = uploadedFile.getParents().next();
          destfolder.addFile(file);
          sourcefolder.removeFile(file);

          // Retrieve original filename.
          var p1 = filename.split(" - ");
          var extension = p1[p1.length - 1];
          p1.pop();
          var name = p1.join(" - ");
          var p2 = "";
          if (extension.indexOf(".") > -1) {
            p2 = "." + extension.split(".")[1];
          }
          var orgFilename = name + p2;

          // Move uploaded file to the trash.
          var orgFiles = DriveApp.getRootFolder().getFilesByName(orgFilename);
          if (orgFiles.hasNext()) {
            var orgFile = orgFiles.next();
            orgFile.setTrashed(true);
          }
        }
      }
    }
  } else {
    throw new Error("This sample script is run by the installable form submit trigger.");
  }
}

Note:

This is a simple sample script for explaining this workaround. So please modify this for your situation.
In my environment, it was found Utilities.sleep(3000) was required. When the file is uploaded and the file is copied, the installable form submit trigger is run. At this time, if Utilities.sleep(3000) is not used, the file is moved before copying file is completed. By this, an error occurs. So I used it.
- But I'm not sure whether the wait time of 3 seconds is the best. So if in your environment, an error occurs, please modify this.
- I think that when a large file is uploaded, this value might be required to be large. Or also I think that running the script by the time-driven trigger might be better.
In this sample script, I used the installable form submit trigger. And when the form is submitted, the original file in the root folder is moved to the trash.
- But I think that you can also run the script by the time-driven trigger. In this case, you can retrieve the response items by opening the form. About this, please select for your situation.

References:

Installable Triggers
setTrashed(trashed)
sleep(milliseconds)

相关文章