-1

I have a Google Sheets script which copies elements of a submitted form data to another spreadsheet and then transforms some of the data for separate use. The script piece works perfectly when I run it manually, but I want it to run automatically.

There are a number of forms connected to this spreadsheet, all submitting data at different times.

So, I sort of have two questions:

  1. How do I get my script to run automatically? I have created a trigger for From Spreadsheet and On Form Submit, but it never runs.
  2. How do I make sure it runs only when: either the specific form is submitted or when the specific sheet has a new row added?

There were a number of articles around the web which had pieces of an answer, but many were from 3 years ago, and it seems that the way Sheets, Forms and the Script Editor work have changed much since then.

Panda
  • 6,955
  • 6
  • 40
  • 55
Tim Dickinson
  • 33
  • 6

1 Answers1

2

You need an onFormSubmit function along with the installable trigger.

If you are using a Html Service Form, you can get the name of the destination sheet from the form event output. You can't do that with forms created from the spreadsheet. The best I have been able to do is to compare the event timestamp to the timestamps on the last rows of the form response sheets to determine which sheet was updated. This is not totally fool proof since it is possible for mulitiple forms to be submitted at the same time (but not likely).

Try this:

//installable trigger also needed.
function onFormSubmit(e){
var ts=e.namedValues.Timestamp
findResponseForm(ts)
}

function findResponseForm(ts) {
var ss=SpreadsheetApp.getActiveSpreadsheet()
var s=ss.getSheetByName('Form Responses 1') //get 'Form Responses 1' sheet
var lr=s.getLastRow() //get last row number of 'Form Responses 1' sheet
var s1=ss.getSheetByName('Form Responses 2') //get 'Form Responses 2' sheet
var lr1=s1.getLastRow() //get last row number of 'Form Responses 2' sheet
var time1=s.getRange(lr, 1, 1,1).getValue()  //get 'Form Responses 1'last  Timestamp
var ftime1=Utilities.formatDate(time1, "GMT-6", "d/M/yyyy' 'HH:mm:ss")   //convert 'Form Responses 1'last Timestamp format to form Timestamp format
var time2=s1.getRange(lr1, 1, 1,1).getValue() //get 'Form Responses 2'last  Timestamp
var ftime2=Utilities.formatDate(time2, "GMT-6", "d/M/yyyy' 'HH:mm:ss")  //convert 'Form Responses 1'last Timestamp format to form Timestamp format
if(ts==ftime1){ //if date/time match
  Logger.log('Form Responses 1')
  //call function to prosses 'Form Responses 1' data
}
  else if(ts==ftime2){ //if date/time match
   Logger.log('Form Responses 2')  
   //call function to prosses 'Form Responses 2' data
   //Add more 'else if' if more forms are involved.
  }
}
Ed Nelson
  • 10,015
  • 2
  • 27
  • 29
  • Hi Ed, Thank you for your reply, I have updated my code and can test that when onFormSubmit runs, it runs the script for the correct sheet. The challenge I'm having is that when I submit a form using Google Forms, the form will populate the spreadsheet in the usual manner, but my script will not run. What else do I need to do to get the script to run? Do I have to deploy/publish it somehow? I have tried to publish it, but the available options don't match with what I am trying to do. – Tim Dickinson Jan 10 '17 at 04:47