How to check if current submission is editing response or a new response

Question

I am working with google forms via app script. I want to determine if the current form that is being submitted is in edit mode or a new response? How can I check this in the onSubmit event.

If yes that user is edit a previously submitted response than I want to change the value in my spread sheet to "yes".

below is a snippet of my code:

function testExcel2() {

  var email = "email";
  var s = SpreadsheetApp.openById("id");
  var sheet = s.getSheets()[0];
  var headers = sheet.getRange(1,1,1,sheet.getLastColumn() - 1).getValues()[0]; 
  var datarow = sheet.getRange(sheet.getLastRow(),1,1,sheet.getLastColumn() - 1).getValues()[0];
  var message = "";

  for(var i in headers)
  {
    message += "" + headers[i] + " : " + datarow[i] + "\n\n";
  }

  MailApp.sendEmail(email, "Submitted Data Test", message); 

  var af = FormApp.getActiveForm();
  //af.setCustomClosedFormMessage("The form is currently processing a submission, please refresh the page.");
  af.setConfirmationMessage('Thanks for responding!')
  //af.setAcceptingResponses(false);
  var rowKey = "o" + sheet.getLastRow();
  var editCell = sheet.getRange(rowKey).setValue('no');

}

Posh

I don't know of any perfect way to determine, from the Form, whether the submission is an edit or a new response. I've spent a lot of time trying to come up with something. I hope there is a way. I save the response ID to the spreadsheet, and look for the response ID of the Form submission in the spreadsheet. So, the only way that I know of to determine whether it's an edit or not, is to have a set of data outside of the Form. The timestamp of an edit gets updated in the Form data before you can compare old/new timestamps. So you can't figure it out that way. – Ennoble

Also, the edited response seems to be put at the end of the data, so you can't compare whether the current Form submission is at the end of the Data or not. Whether it's a new response or an edited response, it's always at the end of the data. It seems to be ordered by timestamp. I consider this a major flaw in Google Forms. I'm probably going to save a second set of data to a text file, and use that as a comparison for old and new data. – Ennoble

@SandyGood. I just did a test. In my test the responses are not sorted when a response is edited, so the edited response keeps its place. – Allx

@Rubén Keeps it's place where? Displayed in individual responses in the Form? I think that how the individual responses are displayed in the "Individual Responses" tab, and getting the data from FormApp in an array can be two different things. For example, FormApp.getActiveForm().getResponses(); Then get the length of the array that is returned, and get the last response. Is that how you tested getting the last response? If there is a way to get the current response, and then find it's index in the array, and it's not the last element, then it might work. – Ennoble

@SandyGood: I'm sorry, I did my test using my script bound to a spreadsheet instead to a form, so what I mean was that the responses keep their place on the responses sheet. – Allx

Answer 1

The Google Form form submit event doesn't have a field that could help to know if the response is a new response or if it's a response edit. As Sandy already said through question comments, the Form Servicer classes and methods, nor the response values include something that could help on this.

By the other hand, the Google Sheets submit event has a range field that could help. The following script bounded to a Google spreadsheet logs the response row:

function onFormSubmit(e){
  var response = e.range;
  Logger.log(response.getRow());
}

The above could be used to keep updated a column to hold a revision counter. If the corresponding cell is blank, then the response is a new response, other way it's a response edit.

The following script it to be bounded to the spreadsheet that receives the Form responses. It requires a on form submit installable trigger. Other instructions to adapt it are included on the script comments.

/*
 *
 * Global Variables
 *
 */

/*
 * Sheet name used as destination of the form responses
 */
var sheetName = 'Form Responses';

/*
 * Name of the column to be used to hold the response revision counter 
 * It should match exactly the header of the related column, 
 * otherwise it will do nothing.
 */
var revisionsColumn = 'Rev';

/*
 * Responses starting row
 */
var startRow = 2;

function setRevisionCounts(e){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues(); 
  var revisionsIndex = headers[0].indexOf(revisionsColumn);
  var data = sheet.getDataRange().getValues();
  var response = e.range;
  var rowIndex = response.getRow()-1;
  var rev = data[rowIndex][revisionsIndex]+1;
  sheet.getRange(rowIndex+1, revisionsIndex+1).setValue(rev); 
}

Allx

I am currently attempting your solution, I will update any progress – Posh

Answer 2

I am seeing that edited responses from a form create a 'note' automatically by google scripts.

The note says 'Responder updated this value.'

So you could check the range for notes by using the function getNotes() or getNote().

Midriff

Answer 3

This is not really an answer but rather a 'heads up'. I have found that if you resubmit (edit) a form that data that is not changed is resent unchanged.

Why does this matter? Let's say you have a branch in your form eg Do you like apples or bananas? If you like apples then the next question might be what is the colour of your car? Let's say 'red' of if you like bananas the next question might be how old are you? Let's say 35.

So you choose apples and red and submit the form. Then you edit your reply and instead choose bananas and 35.

When the form is resubmitted the new data still says car=red and (as expected) age=35.

I would expect/hope that now car=null and age=35 but this is not the case. Car=red still appears against the updated row in the destination sheet.

I hope this is useful to you and maybe someone will suggest a work around.

Veracruz

While the exact workaround would depend on the available tools (e.g., Javascript, Python), a proposed workaround (for the simple case you mentioned) that occurs during data analysis would be to write a script that checks for the chosen branch (apples or bananas) and only passes on that data (or set the relevant fields to null). – Nightingale

Answer 4

You can compare the response length vs row length(or the number of columns in the row). Unless the submissions edits/changes every field/response then you know it should be less than the total number of questions.

function onFormSubmit(e) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()

const response = (e.range)
const responseValues = response.getValues()
const responseRow = Number(response.getRow())

const rowValues = sheet.getRange(responseRow, 1, 1, sheet.getLastColumn()).getValues()


if(responseValues.length < rowValues.length){
//edited submission
}else{
//likely new submission 
}

Baring

Answer 5

Amateur here from Excel ugly but works and this is a simple version - looks for notes - selects that row, updates values on second sheet and deletes the notes.

    function mody() {
      const sh = SpreadsheetApp.getActiveSpreadsheet();
      const mn = sh.getSheetByName('Form Responses 1');
      const puty = sh.getSheetByName('putter');
      var lst = mn.getLastRow();
      var dd = puty.getLastRow();
      dd=dd+1
    //replace row with edited form response if any
    for (var z = 2;z< lst+1;z++){
     var nts = mn.getRange(z,2,1,6).getNotes()
       var ttt = ((nts.join()).length)
    if (ttt>5){
    mn.getRange(z,2,1,6).clearNote() //removes the notes on the modified 
    row
    dd = z
    lst = z
    }
    }   
   
 puty.getRange(dd,1,1,5).setValues(mn.getRange(lst,1,1,5).getValues());
    }

Jarl