0

I've written an apps script in GSheet to:

  1. Copy the last row of the Google Form sheet in Google Sheet file if a new entry arrived (trigger) by Google Form
  2. 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.

ross
  • 2,684
  • 2
  • 13
  • 22
Oliver
  • 3
  • 2
  • You may be getting spurious triggers. Take a look at [this](https://stackoverflow.com/questions/54834837/how-can-i-be-getting-multiple-unwanted-event-blocks-from-the-same-onformsubmit-t) – Cooper Apr 07 '19 at 15:03
  • 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 Apr 07 '19 at 16:33
  • Thnx a lot. I changed 'formdate'. I checked the executions: The trigger started the script multiple times. Any idea what I can do? – Oliver Apr 07 '19 at 19:20
  • Did you see my answer to the question? Can you do something like that? – Cooper Apr 07 '19 at 19:49
  • 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 Apr 07 '19 at 20:40
  • Here's the link to my answer: https://stackoverflow.com/a/54860085/7215091 – Cooper Apr 07 '19 at 21:12
  • Looking for the last line rather than using the values array can be problematic in situations where you get a lot of submissions as you may find that the last line in the linked sheet may not be the line that triggered the onFormSubmit. – Cooper Apr 07 '19 at 21:16

2 Answers2

1

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

  }
}
Mikah
  • 75
  • 8
0

you can use either this :

function appendEntry(){
var ss = SpreadsheetApp.getActiveSpreadsheet()
var ws = ss.getSheetByName("data") // this is the sheet where new entry arrive
var ws2 = ss.getSheetByName("target") // this is the sheet where the entries will go
var data = ws.getDataRange().getValues()
var data2 = ws2.getDataRange().getValues()
 for(var i=0;i<data.length;i++){
   var row = data[i]
   for(var k=0;k<data2.length;k++){
     var row2 = data[k]
     if(row2[0] != row[0]){

      }   // This will loop through both sheet 
          // If entry is already mentioned in other sheet the data will not append
         }
       }
   ws2.appendRow(data[k]) //other wise entry will append to the next sheet
 }
Kuldeep Singh
  • 69
  • 10