I have a script on a google form that renames files in a destination folder using data from the form submission. I want the google sheet logging the submissions to have a column with the same name as the file. The form submission leaves me with data in A:E, I'd like to add the new column in F. I could potentially use a formula in the sheet to achieve this but -

1. one parameter used for renaming my files is date/time using Utilities.formatDate (now), date/time, which seems to log a time slightly different from the time logged as the Timestamp on the google sheet - it seems like a 2 second difference usually, but in the event it's more/less, a formula using the Timestamp in the google sheet might not be accurate
2. form submissions bump down manually-input rows such that the formula doesn't stick to the cell in the newly-added row. I could manually add it, but that's a bit of a pain.

I'd love the sheet to automatically populate with the same output from the below in column F, which would avoid both problems.

var name = FormattedDate ' - ' source ' - ' gender ' - ' age file.setName(name)

Full code below -

function onFormSubmit1() {
  var form=FormApp.getActiveForm();
  
  var length=form.getResponses().length;
  var gender=form.getResponses()[length-1].getItemResponses()[0].getResponse();
  var age=form.getResponses()[length-1].getItemResponses()[1].getResponse();
  var source=form.getResponses()[length-1].getItemResponses()[2].getResponse();
  var id=form.getResponses()[length-1].getItemResponses()[3].getResponse();
  var now = new Date();
  var FormattedDate = Utilities.formatDate(now, Session.getScriptTimeZone(), "yyyy.MM.dd HH:mm:ss");
  
  
  var file=DriveApp.getFileById(id);
  name = file.getName();
  
  var name = FormattedDate   ' - '   source  ' - '   gender   ' - '   age 
  file.setName(name);
  
  var source_folder = DriveApp.getFolderById('folderidcoderemoved'); 
  
}

CodePudding user response：

I believe your current situation and your goal are as follows.

• Your script is put in the script editor of Google Form.
• Your script is run by OnSubmit trigger.
• When the form is submitted, you want to put the filename to the column "F".

In this case, how about the following modification?

Modified script:

Please set ssId and sheetName.

function onFormSubmit1() {
  var form = FormApp.getActiveForm();
  var length = form.getResponses().length;
  var gender = form.getResponses()[length - 1].getItemResponses()[0].getResponse();
  var age = form.getResponses()[length - 1].getItemResponses()[1].getResponse();
  var source = form.getResponses()[length - 1].getItemResponses()[2].getResponse();
  var id = form.getResponses()[length - 1].getItemResponses()[3].getResponse();

  var now = new Date(); // or form.getResponses()[length - 1].getTimestamp();

  var FormattedDate = Utilities.formatDate(now, Session.getScriptTimeZone(), "yyyy.MM.dd HH:mm:ss");
  var file = DriveApp.getFileById(id);
  name = file.getName();
  var name = FormattedDate   ' - '   source   ' - '   gender   ' - '   age
  file.setName(name);
  var source_folder = DriveApp.getFolderById('folderidcoderemoved');

  // And, I added below script.
  var ssId = "###"; // Please set the Spreadsheet ID of Spreadsheet that the submitted data is put.
  var sheetName = "###"; // Please set the sheet name of submitted data.
  var sheet = SpreadsheetApp.openById(ssId).getSheetByName(sheetName);
  sheet.getRange("F"   sheet.getLastRow()).setValue(name);
}

• In this modification, when the script is run, the value of name is put to the column "F" of the last submitted row.
• I couldn't understand your script of var source_folder = DriveApp.getFolderById('folderidcoderemoved');. If your showing script is not complete, please be careful about this.

Note:

• About the value of now, please choose var now = new Date(); or var now = form.getResponses()[length - 1].getTimestamp(); for your situation.