Unwanted multiple row entries / copies (Google Form --> Google Sheet file, sheet1 --> Google Sheet file, sheet 2)

Question

I've written an apps script in GSheet to:

Copy the last row of the Google Form sheet in Google Sheet file if a new entry arrived (trigger) by Google Form
Paste this row to another sheet in the same GSheet file to the last row

Unfortunately it copies the last row of the Form sheet more than one time to the appropriate sheet. Sometimes two times, sometimes four times. I cannot see my mistake in the code.

I couldn't find a solution so far. I included a pause before appendRow with Utilities.sleep(5000) but w/o effect.

function myFunction() {

// Source sheet: Form is the source sheet of the Google Form
var source = 
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form");

// Get last row in Form
var lastrow = source.getLastRow();

// Get just the date of the Form input 
var formdate = source.getRange(lastrow,7,1,7).getValue();

// Change month number to string (e.g. April)
var currentD2 = Utilities.formatDate(formdate, 
Session.getScriptTimeZone(), "MMMMM");

// Identify target sheet in same Google sheet file
var target = 
SpreadsheetApp.getActiveSpreadsheet().getSheetByName(currentD2);

// Identify the appropriate range of the last row of the Form sheet 
// (source) 
var sourceData = source.getRange(lastrow, 2,1,8).getValues();

// Append the last row of the Form sheet to the last row in the target 
// sheet
target.appendRow(sourceData[0])
}

I expect that the function copies just one time the last row of the Form sheet and not multiple times.

You may wish to change this: var formdate = source.getRange(lastrow,7,1,7).getValue(); to this: var formdate = source.getRange(lastrow,7).getValue(); since you apparently only want one value. — Cooper
Thnx a lot. I changed 'formdate'. I checked the executions: The trigger started the script multiple times. Any idea what I can do? — Oliver
Did you see my answer to the question? Can you do something like that? — Cooper
I checked your answer about spurious triggers but I couldn't catch the solution from the post you linked to. I found another simple solution with "if" statement. I added a simple if statement to compare specific cells of the last row (source, target), if equal it will stopp. It will not avoid multiple executions of the script but at least it will avoid multiple copy&paste's. — Oliver

Mikah Mikah · Accepted Answer · 2019-08-06T06:15:35

Hello I guess that you are using an onEdit trigger to start your function.

That means that when you change the "Google Form sheet" it triggers your function which changes another sheet in the same spreadsheet which starts your trigger again.

I recommend to rename your function onEdit(e) ref(https://developers.google.com/apps-script/guides/triggers/events)

and then condition your action with: if e.range belongs to your "google form sheet" then do something otherwise not.

function onEdit(e) {
  if (e.range.getSheet().getName()='your google form sheet') {
   // insert here the contents of your function

  }
}

Unwanted multiple row entries / copies (Google Form --> Google Sheet file, sheet1 --> Google Sheet file, sheet 2)

1 Answers