On Form Submit trigger for simple code, but two forms linked to Google Sheets spreadsheet

0
votes

Issue:

I have a simple action that is required every time a new entry is added to my Google Sheets "Sales Form Reponses" page. The code for running it manually when I know this is needed, works perfectly well, and simply adds a new row to the sheet "Sales Data Clean", and copies all the formulas from the previous row. This is necessary because it adds some fields of information to the form entries.

function addRow() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('Sales Data Clean'), lRow = sh.getLastRow(); 
  var lCol = sh.getLastColumn(), range = sh.getRange(lRow,1,1,lCol);
  sh.insertRowsAfter(lRow, 1);
  range.copyTo(sh.getRange(lRow+1, 1, 1, lCol), {contentsOnly:false});
}

I am trying to use a trigger to make Google Sheets run this function whenever a new form entry is submitted.

Problem:

I have two forms linked to the overall Google Sheets document. The other sheet affected is called "Stock Form Responses", and the trigger won't allow to specify that the function AddRow should ONLY run if the Sales Form has a new entry. How do I do this? Help greatly appreciated.

2
google-apps-scriptgoogle-sheetsgoogle-forms
You can use e.range.getSheet() to figure out which sheet was being written to and thus which form just submitted – Cooper
Thank you for such a fast response. Forgive me, but where on earth do I put that in order to make it run only on one form? – Exceed Education
What is the name of the sheet for that form? – Cooper
The form I want to trigger my function AddRow, is linked to the sheet "Sales Form Responses". – Exceed Education

2 Answers

1
votes
function addRow(e) {
  const ash = e.range.getSheet(); 
  if{ash.getName()=='Sales Form Responses'){
    //Put your code in here
  }
}

Inspite of what @Nazia said the best answer is to attach the code to the spreadsheet and use e.values or e.namedValues instead of getLastRow() because if you get multiple submissions in quick succession that last row may the wrong one.

0
votes

Another approach is to add script on your sales form instead and modify the sheet from there.

Code:

function addRow() {
  // sheet ID found in URL between /d/ and /edit
  var ssId = '1EkC7D3AbYsOoEtI3OsngCtfmhYJNPfMquIoObeTtZWM'; 
  var ss = SpreadsheetApp.openById(ssId);
  var sh = ss.getSheetByName('Sales Data Clean');

  var lRow = sh.getLastRow(); 
  var lCol = sh.getLastColumn();
  var range = sh.getRange(lRow, 1, 1, lCol);
  sh.insertRowsAfter(lRow, 1);
  range.copyTo(sh.getRange(lRow+1, 1, 1, lCol), {contentsOnly:false});
}

Trigger:

trigger1 trigger2

Output:

output

  • Last row was duplicated on "Sales Data Clean" sheet
  • Will trigger every time that specific form (where your script is added) is submitted.