(?) Google Form & Drive - how to change name of document upload automatically

[timbo343]

I'm looking at trying to automate a few things at the moment, now i'm looking at when staff upload training certificates to Google Drive via Google Forms - you know all that mandatory training that we need to do.

 

I've got a few ideas going round my head but to start with, i'm looking at this script (https://developers.google.com/apps-script/samples/automations/upload-files) so we can ask staff to upload files to different folders via one form, this would be used instead of one form with lots of "file upload" questions - i'm not sure which will be easier at the moment until i've tested my options.

 

Then, i'm hoping that we integrate this process (https://medium.com/@yashashreepatel/how-to-rename-google-forms-responses-in-the-google-drive-automatically-94f033d90b24) into the mix but i feel i may need an IF function depending on which folder is chosen - and tat's where i need some help but first of all i need to get this script working without adding something else into the mix.

 

I'm hoping to build the file name of the uploaded document with COURSE NAME - NAME OF STAFF MEMBER - UPLOADDATE / DATE ON CERTiFICATE (this would be a question on the form).

 

Really, i'm posting this to see if anyone else is doing anything like this before i start re-inventing the wheel.

[timbo343]

After studying what is going on with one of the scripts, i have something that might work for us, it's not 100% perfect but it will do.

 

There is one thing i cannot get my head around though, that's the line for (var i = 0; i < formResponses.length; i++). Why do i need to change the var i = 0 to var i = 11 (or another value) because numbers 1, 2, 3, 4, 5 etc don't work? I've got one script up at 8, a few up at 11 and I think one of the scripts is up to var i = 14 for some reason?

 

function FILE1Upload() {
var form = FormApp.openById('FORM_ID'); //DDD
var formResponses = form.getResponses();
var baseString = 'https://drive.google.com/file/d/';
var endString = '/view?usp=drivesdk';
var title = form.getTitle();
console.log(itemResponses)
 
var folder = DriveApp.getFolderById('DRIVE_FOLDER_ID');
var files = folder.getFiles();

 while (files.hasNext()) {
   var file = files.next();
   for (var i = 0; i < formResponses.length; i++) {
     var formResponse = formResponses[i];
     var itemResponses = formResponse.getItemResponses();
     var itemResponseFirstName = itemResponses[0];
     var itemResponseLastName = itemResponses[1];
     var itemResponseCertificate = itemResponses[2];
     var itemResponseFileuploadDate = itemResponses[3];
     var itemResponseFile = itemResponses[4];

     var fileID = itemResponseFile.getResponse();
     var newName = itemResponseFirstName.getResponse() + " " + itemResponseLastName.getResponse() + " - " + itemResponseCertificate.getResponse() + " - " + itemResponseFileuploadDate.getResponse();
     var url = baseString + fileID + endString;
     var urlCheck = file.getUrl();
     if ( url == urlCheck) {
       var modName = newName;
       file.setName(modName);
     }
   }
 }
}

 

function FILE2Upload() {
var form = FormApp.openById('FORM_ID'); //DDD
var formResponses = form.getResponses();
var baseString = 'https://drive.google.com/file/d/';
var endString = '/view?usp=drivesdk';
var title = form.getTitle();
console.log(itemResponses)
 
var folder = DriveApp.getFolderById('DRIVE_FOLDER_ID');
var files = folder.getFiles();

 while (files.hasNext()) {
   var file = files.next();
   for (var i = 11; i < formResponses.length; i++) {
     var formResponse = formResponses[i];
     var itemResponses = formResponse.getItemResponses();
     var itemResponseFirstName = itemResponses[0];
     var itemResponseLastName = itemResponses[1];
     var itemResponseCertificate = itemResponses[5];
     var itemResponseFileuploadDate = itemResponses[6];
     var itemResponseFile = itemResponses[7];

     var fileID = itemResponseFile.getResponse();
     var newName = itemResponseFirstName.getResponse() + " " + itemResponseLastName.getResponse() + " - " + itemResponseCertificate.getResponse() + " - " + itemResponseFileuploadDate.getResponse();
     var url = baseString + fileID + endString;
     var urlCheck = file.getUrl();
     if ( url == urlCheck) {
       var modName = newName;
       file.setName(modName);
     }
   }
 }
}

Edited May 2, 2023 by timbo343

[jthompson]

After studying what is going on with one of the scripts, i have something that might work for us, it's not 100% perfect but it will do.

 

There is one thing i cannot get my head around though, that's the line for (var i = 0; i < formResponses.length; i++). Why do i need to change the var i = 0 to var i = 11 (or another value) because numbers 1, 2, 3, 4, 5 etc don't work? I've got one script up at 8, a few up at 11 and I think one of the scripts is up to var i = 14 for some reason?

 

function FILE1Upload() {
var form = FormApp.openById('FORM_ID'); //DDD
var formResponses = form.getResponses();
var baseString = 'https://drive.google.com/file/d/';
var endString = '/view?usp=drivesdk';
var title = form.getTitle();
console.log(itemResponses)
 
var folder = DriveApp.getFolderById('DRIVE_FOLDER_ID');
var files = folder.getFiles();

 while (files.hasNext()) {
   var file = files.next();
   for (var i = 0; i < formResponses.length; i++) {
     var formResponse = formResponses[i];
     var itemResponses = formResponse.getItemResponses();
     var itemResponseFirstName = itemResponses[0];
     var itemResponseLastName = itemResponses[1];
     var itemResponseCertificate = itemResponses[2];
     var itemResponseFileuploadDate = itemResponses[3];
     var itemResponseFile = itemResponses[4];

     var fileID = itemResponseFile.getResponse();
     var newName = itemResponseFirstName.getResponse() + " " + itemResponseLastName.getResponse() + " - " + itemResponseCertificate.getResponse() + " - " + itemResponseFileuploadDate.getResponse();
     var url = baseString + fileID + endString;
     var urlCheck = file.getUrl();
     if ( url == urlCheck) {
       var modName = newName;
       file.setName(modName);
     }
   }
 }
}

 

function FILE2Upload() {
var form = FormApp.openById('FORM_ID'); //DDD
var formResponses = form.getResponses();
var baseString = 'https://drive.google.com/file/d/';
var endString = '/view?usp=drivesdk';
var title = form.getTitle();
console.log(itemResponses)
 
var folder = DriveApp.getFolderById('DRIVE_FOLDER_ID');
var files = folder.getFiles();

 while (files.hasNext()) {
   var file = files.next();
   for (var i = 11; i < formResponses.length; i++) {
     var formResponse = formResponses[i];
     var itemResponses = formResponse.getItemResponses();
     var itemResponseFirstName = itemResponses[0];
     var itemResponseLastName = itemResponses[1];
     var itemResponseCertificate = itemResponses[5];
     var itemResponseFileuploadDate = itemResponses[6];
     var itemResponseFile = itemResponses[7];

     var fileID = itemResponseFile.getResponse();
     var newName = itemResponseFirstName.getResponse() + " " + itemResponseLastName.getResponse() + " - " + itemResponseCertificate.getResponse() + " - " + itemResponseFileuploadDate.getResponse();
     var url = baseString + fileID + endString;
     var urlCheck = file.getUrl();
     if ( url == urlCheck) {
       var modName = newName;
       file.setName(modName);
     }
   }
 }
}

 

Perhaps some of the first responses are no longer present because they were just test responses that have since been deleted.

[timbo343]

Ah could have been. I'll see how i get on with the scripts and make a mental note this could be the issue.