CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-forms
nlw
nlw

Reputation: 11

Moving apps script from Spreadsheet to Form

I am trying to create an event to trigger on form submit. I have been using this script below and it has been working fantastically. However, each time the response is edited, it duplicates the row. After researching, I know I need to move the script to be attached to the form instead of the spreadsheet. I am very new to apps script and can't figure out how to change the script to work from the form.

function timerEvent(e){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sourceSheet = e.range.getSheet();
    if(sourceSheet.getSheetName() === 'Form Responses'){
        var row = e.range.getRow();
        var rowRange = sourceSheet.getRange(row, 1, 1, sourceSheet.getLastColumn());
        var rowValues = rowRange.getValues()[0];
        if(rowValues[21]!= ""&&rowValues[17]!="Yes"){
            var targetSheet = ss.getSheetByName("UENA");
            targetSheet.insertRowBefore(2);  
            var targetRange = targetSheet.getRange(2, 4);
            rowRange.copyTo(targetRange);
  targetSheet.getRange('4:4').copyTo(targetSheet.getRange('2:2'), SpreadsheetApp.CopyPasteType.PASTE_CONDITIONAL_FORMATTING, false);
  targetSheet.getRange('4:4').copyTo(targetSheet.getRange('2:2'), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
  targetSheet.getRange("A2:AD").sort([{column: 8, ascending: true}, {column: 7, ascending: true}]);
        targetSheet.getRange('B2').setDataValidation(SpreadsheetApp.newDataValidation()
          .setAllowInvalid(false)
          .requireValueInList(['AA', 'BB', 'CC', 'DD', 'EE', 'FF', 'GG', 'HH', 'II', 'KK', 'LL'], true)
          .build());
        targetSheet.getRange('C2').insertCheckboxes();    

        }     
    } 
}

Upvotes: 0

Views: 112

Answers (1)

Tedinoz
Tedinoz

Reputation: 7959

You have a spreadsheet linked to a Google Form and responses are recorded in the spreadsheet. You have a function installed as an Installable onFormSubmit trigger; this function makes a duplicate of each response in a second sheet. But... users have the ability to edit their submissions. Though the entry on the response sheet is not duplicated (the existing response row is editted), your function is triggered by the response and a duplicate entry is inadvertently made on the target sheet.

Solution#1

The solution to this problem is found in how Google Sheets treats an edited response. When an edited response is submitted a Note is inserted in the edited cell. So, one tests for whether there are any Notes in the response range. The response range can be identified by using Event Objects.

When the response is copied to "UENA" sheet, it is inserted at the top of the page, and then (later) the data is sorted by two other columns. This makes it impossible to write a copyTo command that accurately reflects the location of the original submission on UENA.

However, the following snippet shows the code that would be used if the destination on UENA was predictable.

function timerEvent(e){

  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sourceSheet = ss.getSheetByName("Form Responses 1")

  // use this to show all the Event Objects  
  Logger.log(JSON.stringify(e)) // DEBUG

  // get the response range using Event Objects
  var respRange = e.range
  Logger.log("DEBUG: the response range = "+respRange.getA1Notation())

  // get any notes in the response range and flattened into a 1d array
  var notes = respRange.getNotes()
  var flatNotes = notes.flat()
  Logger.log(flatNotes) // DEBUG
  
  // test whether any notes exist
  var found = flatNotes.find(element => element.length > 0)
  // if undefined, then there are no notes
  if (found === undefined){
    Logger.log("DEBUG: This is an original submission")
    // insert relevant code to copy response to target
  }
  else{
    Logger.log("DEBUG: this is an edited submission. The edited row is "+e.range.rowStart)
    var respLR = sourceSheet.getLastRow()
    Logger.log("DEBUG: last row: "+respLR)
    var rowDiff = respLR-e.range.rowStart
    var targetSheet = ss.getSheetByName("UENA")
    var targetStartRow = 2
    var targetEditRange = targetSheet.getRange(rowDiff+targetStartRow,4)
    Logger.log("DEBUG: target"+targetEditRange.getA1Notation())

    // copy to UENA
    // can't use this line since the original submission has been sorted
    //respRange.copyTo(targetEditRange) 
    
    }
}

Solution#2

There is a second solution.

Instead of copying each response one-by-one, as they are received, copy the entire range from the response sheet whenever a single response is received. This will ensure that there are no duplicates on UENA, and also that the sort order on UENA is retained.

Upvotes: 1

Related Questions