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

[Mauro Mancini]

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,
      sh,ss,valueToSearchFor;
  // 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('https://docs.google.com/spreadsheets/d/1vdHMZkDnE8Ua4XWcv45cmNkYPqqcK8THkRhVfMo0a78/edit#gid=xxxxxxxxxx');//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
    return;
  }
  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]

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:

https://developers.google.com/apps-script/guides/triggers/events#form-submit

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]

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!

Mauro

[CBM Services]

Mauro,

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 google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/a3a33d3c-7ca8-445e-87a1-02254009d156%40googlegroups.com.

[Mauro Mancini]

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! :-(

[--Hyde]

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]

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

[--Hyde]

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]

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 google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/61bdd9ce-e1e4-4b81-937e-97668d39dd65%40googlegroups.com.