How to check if current form submission is editing response

Question

I need to return the row number of a Google Form submission that has been resubmited.

I followed all of the information at: Google Forms App Script : How to check if current submission is editing response or a new response, but I get this error for trigger formSubmit:

Exception: Cannot convert '15.0' to int.

So it is correctly finding the 15th row as edited and appears to be giving me a number, but i keep getting the error when i try to use that number.

I've tried to convert it using +num, Number(), place the row # into a cell and change the format manually, and copy it to another cell using script and formula but I still continue to get this error when formSubmit occurs. Can you help me determine how to fix this error?

function email(e) { 
var sh   = SpreadsheetApp.getActiveSheet();
var ss   = SpreadsheetApp.getActiveSpreadsheet();  
var gid  = sh.getSheetId().toString(); 
var last = ss.getLastRow();
var valueColumnLocation;

for(k=1;k<=ss.getLastColumn();k++) {
  if (sh.getRange(1,k).getValues() == "Select Worksite") {
    var valueColumnLocation = k;
    break;
  }
};


...
if (...){...}

  else{
  var rowrev = e.range.getRow();
  //sh.getRange("rownumberrev").setValue(rowrev);

  PropertiesService.getScriptProperties().setProperty('mykey',rowrev); 

  if (sh.getRange(rowrev,valueColumnLocation,1,1).getValues()=="USA"){
    uploadrev()
    pdfFCrev()
  }

    else{if(sh.getRange(rowrev,valueColumnLocation,1,1).getValues()=="GLOBAL"){
      uploadrev()
      pdfGLrev()           
      }

    } 
  }
}

Adding more detail to original post The spreadsheet that the google form populates has columns A - CZ I'm looking to find the row that was edited and then look for a value in a specific column of that row. If that value==USA, do this or if value==Global, do that.

So in this picture, row 15 was resubmitted by the user and the change was in column E. I want to use the knowledge that row 15 was edited and we earlier found the column # of "Select Worksite" which now resides in column Z and check if the value in range(15,26,1,1)==USA or Global.

Maybe there's a completely different or better way to do this that's i'm not thinking of, but when i try to use the e.range value in the sh.getRange() i get the error it cannot convert 15.0 to int.

Please, post the exact error message or at least point to where the message points to. Additionally, what *appears to be giving me a number* is substantiated - did you use `typeof` and got a `number` back? — Oleg Valter is with Ukraine, Jun 07 '20 at 13:36
Thanks, was confused by the explanation in the question. Thomas, the error you provided happens when you try to use a string as one of parameters of `getRange`, indicating type mismatch. Are you sure you are telling us everything relevant? I noticed that you save the `rowrev` to Properties - if you later retreive it, do you use the value directly? If so, please, note that it is coerced to string before saving to properties. Other than that, I don't see anything wrong — Oleg Valter is with Ukraine, Jun 07 '20 at 13:54
Thomas, please, tell us what the *type* of the `rowrev` is *when the function is run in the context of the submit event*. The error you get is very clear on what is wrong, also it *always* comes with a direct pointer to the offending line. Are you sure that what you provided in the `else` block is *everything* that's going on? Also, where in the workflow the `mykey` property is used? — Oleg Valter is with Ukraine, Jun 07 '20 at 17:01
Oleg, I cannot figure out how to find the typeof when i tried the following if( typeof (e.range.getRow()) === "string" ){ Browser.msgBox("string"); } if( typeof (e.range.getRow()) === "number" ){ Browser.msgBox("number"); } if( typeof (e.range.getRow()) === "object" ){ Browser.msgBox("object"); } — Thomas Pribicko, Jun 07 '20 at 17:40
?? Couldn't you just log it what `typeof` returns? `Browser` won't work in the context of the trigger. Just add a `console.log()` call and check the result in stackdriver logging. — Oleg Valter is with Ukraine, Jun 07 '20 at 17:47

Cooper · Answer 1 · 2020-06-07T14:54:46.090

1

Looks to me like you can do the entire function with this:

function email(e) { 
  PropertiesService.getScriptProperties().setProperty('mykey',e.range.getRow()); 
  if(e.namedValues["Select Worksite"]=="USA"){
    uploadrev();
    pdfFCrev();
  }
  if(e.namedValues["Select Worksite"]=="GLOBAL"){
    uploadrev()
    pdfGLrev()           
  }
}

It pays to read the documentation while your writing your code.

on FormSubmit Event Object

edited Jun 07 '20 at 14:54

answered Jun 07 '20 at 14:00

Cooper

59,616
6
23
54

Cooper thanks for the assistance and yes that code works, but it think it only works if the named value that changed is what you designated. This form has a number of questions and i want it to find the row of resubmission and then step through the if else statements to match SELECT WORKSITE to USA or GLOBAL. So if i a different row,col value is changed, this doesn't perform correctly. How do you check the typeof? I tried Logger.log(typeof e.range.getRow()); but nothing went to the logger and i got an error. – Thomas Pribicko Jun 07 '20 at 15:54
Meant to type...if i a different column value of the same row is changed, this doesn't step into the if statements – Thomas Pribicko Jun 07 '20 at 16:01
I don't really understand what you are asking perhaps you could share an image of your linked sheet and explain how you locate a resubmission. – Cooper Jun 07 '20 at 16:01
I don't think that most of the volunteers on this site use Google Forms much. In my case I use them for simple forms but if I need anything complex I'm a lot more likely to just create one of my own. – Cooper Jun 07 '20 at 16:03
I posted some clarification and a snippet of the table back in the original post at the top – Thomas Pribicko Jun 07 '20 at 16:39
In your example how did you determine that column E was changed? – Cooper Jun 07 '20 at 16:48
@ThomasPribicko - what error have you got when trying to use `typeof` operator? Did you do it in context of the editor or during a form submit event? Please, always be specific - debugging isn't magic, there is always an exact reason why an error happened. – Oleg Valter is with Ukraine Jun 07 '20 at 16:55

score 0 · Answer 2 · answered Jun 07 '20 at 17:50

0

35th try is the charm. I don't think i needed to but i restated variables sh & ss. Then i changed: sh.getRange(newsub,valueColumnLocation).getValue() & now it works

else{

  var sh   = SpreadsheetApp.getActiveSheet();
  var ss   = SpreadsheetApp.getActiveSpreadsheet();   

  var newsub = e.range.getRow(); 

  if (sh.getRange(newsub,valueColumnLocation).getValue()=="USA"){
    uploadrev()
    pdfFCrev()
  }

    else{if(sh.getRange(newsub,valueColumnLocation).getValue()=="Global"){
      uploadrev()
      pdfPArev()           
         }

     } 

  }

answered Jun 07 '20 at 17:50

Thomas Pribicko

13
4

Thomas, you do realise that *redeclaration* of the variables does nothing (apart from an unfortunate side-effect, see below), besides it is a bad practice (it makes sense with `const` and `let` as they are block-scoped, but not with `var`)? See this sample: `function test () { var a = 0; if(true) { var a = 1; }; console.log(a); } 20:56:52.077 `. From the standpoint of what you provided - that change did *nothing*, so the answer is likely to confuse passers by. But the fact it worked got me thinking - could you please show us all of the places where `rowrev` was referenced before the change? – Oleg Valter is with Ukraine Jun 07 '20 at 18:02
All the instances of rowrev were in the original post – Thomas Pribicko Jun 07 '20 at 18:16
Thomas, I am insistent on this because either we are talking about a bug that should be escalated or about an issue with the code in your script. The `getRow()` method returns a `number` (`Integer` in docs, but it is just a wrapper for Number in type declaration file). The error message you get is specific to passing a string to `getRange` method. Therefore, `rowrev` are either at some point start to contain strings or the method lies about its public contract. Given the fact that `rowrev` -> `newsub` fixes the error ... – Oleg Valter is with Ukraine Jun 07 '20 at 18:30
..., there are three possibilities: 1. `e.range.getRow()` is overridden (chance is negligible), 2. `rowrev` is overridden ( but this must take place after declaration & before first `if` ). 3. `getRow()` returns inconsistent output (= bug). There is a chunk of the code above the `esle` part we do not see - could you post it (in the OP)? – Oleg Valter is with Ukraine Jun 07 '20 at 18:37

How to check if current form submission is editing response

2 Answers2

Linked