CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-forms
Exceed Education
Exceed Education

Reputation: 1

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

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.

Upvotes: 0

Views: 216

Answers (2)

Cooper
Cooper

Reputation: 64040

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.

Upvotes: 1

NightEye
NightEye

Reputation: 11184

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.

Upvotes: 0

Related Questions