Google Form “Cannot convert [object Object] to (class)”

Skip to first unread message

Mauro Mancini

Dec 11, 2019, 11:00:16 AM12/11/19
Morning everyone. I need your help. I need to run this script to delete duplicates in a google reply form sheet.

I have this sheet that is updated by filling out a google form and I need to keep people who have already signed up, so prevent duplicates.
The unique field could be the email address or the phone number.


This one below is a beautiful script that I found on the web that allows deletion but not automatically.

function updateExisting(columnWithUniqueIdentifier,sheetTabName) {
  var dataFromColumnToMatch,lastColumn,lastRow,rowWithExistingUniqueValue,rowOfDataJustSaved,
  // USER SETTINGS - if the values where not passed in to the function
  if (!columnWithUniqueIdentifier) {//If you are not passing in the column number
    columnWithUniqueIdentifier = 2;//Hard code column number if you want
  if (!sheetTabName) {//The sheet tab name was not passed in to the function
    sheetTabName = "compleanno2020";//Hard code if needed
  //end of user settings
  ss = SpreadsheetApp.openByUrl('');//Get the active spreadsheet - this code must be in a project bound to spreadsheet
  sh = ss.getSheetByName(sheetTabName);

  lastRow = sh.getLastRow();
  lastColumn = sh.getLastColumn();
  //Logger.log('lastRow: ' + lastRow)
  rowOfDataJustSaved = sh.getRange(lastRow,1,1,lastColumn).getValues();//Get the values that were just saved
  valueToSearchFor = rowOfDataJustSaved[0][columnWithUniqueIdentifier-1];
  //Logger.log('valueToSearchFor: ' + valueToSearchFor)
  dataFromColumnToMatch = sh.getRange(1,columnWithUniqueIdentifier,lastRow-1,1).getValues();
  dataFromColumnToMatch = dataFromColumnToMatch.toString().split(",");
  //Logger.log('dataFromColumnToMatch: ' + dataFromColumnToMatch)
  rowWithExistingUniqueValue = dataFromColumnToMatch.indexOf(valueToSearchFor);
  //Logger.log('rowWithExistingUniqueValue: ' + rowWithExistingUniqueValue)
  if (rowWithExistingUniqueValue === -1) {//There is no existing data with the unique identifier
  sh.getRange(rowWithExistingUniqueValue +1,1,1,rowOfDataJustSaved[0].length).setValues(rowOfDataJustSaved);
  sh.deleteRow(lastRow);//delete the row that was at then end

The script works very well in manual mode but if I associate it with a trigger with event sending the module I have this error:
Error Message: Cannot convert [object Object] to (class). (line 29, file "NOduplicate")
Trigger: formSubmit

I have been trying to find a solution for a long time but I can't get it to work in TRIGGER mode.

Thank you very much for all your help.
HELP ME !! : D

Alan Wells

Dec 11, 2019, 4:56:12 PM12/11/19
to Google Apps Script Community
The function is:

function updateExisting(columnWithUniqueIdentifier,sheetTabName)

If you use the function name "updateExisting" in the trigger definition, then it doesn't automatically get values assigned to the variables:
columnWithUniqueIdentifier and sheetTabName

What will happen, is that the first variable columnWithUniqueIdentifier will get assigned a value of the event object.

You need to look at the documentation for the event object for Google Sheets:

typically, the function to be run by a trigger would look like this:
function updateExisting(e)

So, the variables columnWithUniqueIdentifier,sheetTabName are replaced by "e".
There's nothing special about the letter "e", it could be any letter or any name.
People use "e" because it's the first letter in the word "event".
The code needs to get the values out of "e" that it needs.

So, look at the documentation, and try to match up what the event object provides to what your code needs.
Post back if you have a question.

Mauro Mancini

Dec 12, 2019, 9:55:55 AM12/12/19
to Google Apps Script Community
Alan Wells first of all thank you so much for answering me and giving suggestions.

Unfortunately not knowing the programming I do not know how to correct the error. Now I study the link you gave me and I do tests.
I hope to resolve ... if not, I send another answer.

Thank you all!

CBM Services

Dec 12, 2019, 12:20:43 PM12/12/19

If you are using google forms to get the data entries inyo the spreadsheet, there is a setting in forms that restrict entries to only 1 per user. This could help you avoid duplicates.

From: Mauro Mancini
Sent: ‎2019-‎12-‎12 6:55 AM
To: Google Apps Script Community
Subject: [Apps-Script] Re: Google Form “Cannot convert [object Object] to (class)”

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to
To view this discussion on the web visit

Mauro Mancini

Dec 12, 2019, 12:25:21 PM12/12/19
to Google Apps Script Community
Hi George and thanks for your comment.
We know this function but the limitation is only if the person is logged in with google.

This limitation for my project is not good, unfortunately! :-(


Dec 14, 2019, 5:25:47 AM12/14/19
Hi Mauro,

I understand that you want to avoid superfluous duplicate registrations. But isn't there a legitimate use case for users to re-do their registration if they made a mistake or need to update some detail?

If that is the case, it might make more sense to keep all the registrations, and simply copy the final table to another sheet so that from each user only the last registration is kept. This way, possible previous registrations by that user would be ignored but would remain in the form responses sheet for reference.

The copy-the-latest-registration-only stunt can be done with spreadsheet formulas, without resorting to scripting. Here is one potential formula recipe to put on another sheet:

=arrayformula( iferror( vlookup( unique('Responses'!B2:B), sort('Responses'!B2:H, 'Responses'!A2:A, false), column('Responses'!B2:H) - 1, false ) ) )

Cheers --Hyde

Mauro Mancini

Dec 16, 2019, 4:34:15 AM12/16/19
to Google Apps Script Community
Hi Hyde,

thank you for answering me and for giving me advice.

The module I am creating serves me to register customers (around 10,000) and give them a birthday present.

That's why I would like to limit the registration to only once per user (but not with google login)
The best thing would be to check as the only field not so much the email but the phone number.

Can I do it somehow?
Thanks a lot to everyone


Dec 16, 2019, 2:59:04 PM12/16/19
to Google Apps Script Community
Hi Mauro,

Did you try the spreadsheet formula I gave you?

=arrayformula( iferror( vlookup( unique('Responses'!B2:B), sort('Responses'!B2:H, 'Responses'!A2:A, false), column('Responses'!B2:H) column('Responses'!B2) + 1, false ) ) )

The formula can be modified to use the phone number column instead of email column when finding the latest registration by each user. If you need more help, please share a representative sample spreadsheet.

Cheers --Hyde

Marcos Gomes

Dec 16, 2019, 3:32:13 PM12/16/19
Hi Mauro,

I never use the response sheet in my workflow. I check and copy the data from the response sheet to another sheet.
Use the timestamp for this check.
You can create a filter that checks if the data row already exists, and not copy this data if it already exists.
PS: This check with a large number of answers can be slow.

"Error Message: Cannot convert [object Object] to (class). (line 29, file "NOduplicate")"
it's probably an array error. Errors messages in FormApp are not exact. 

I'm sorry for my English, it's not so good. 

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to
Reply all
Reply to author
0 new messages