0

excuse my English. I'm a new user and I don't really understand the programming language very well! :-)

I need your precious help. I'm dealing with a google form where I have to register customers to choose their birthday present.

Before writing I read a lot of posts here about the subject but I didn't find the solution to my problem. The closest is this: Overwriting Google sheets (for form response) rows if duplicate entered

I tried running the scripts I found but when I run them it gives me this problem: TypeError: Could not call the "getSheetByName" method of null. (line 16, file "UPDATEcontact")

The sheet name is: compleanno2020

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.getActiveSpreadsheet();//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
}

What should I do: I would like to have the customer register only once - without activating the login - or if it is not possible I would like to have the repeated data overwritten.

The variable to check is definitely the email address but if it were possible there should be a double check, or email address associated with the birthday date. Below I attach my sample sheet.

my google-sheet

Do you have any solution? I really hope so ... and I thank you in advance.

Thank you all.

Mauro Mancini
  • 33
  • 7
  • So I believe you are saying that have a Google Form that customers can use to choose a birthday present and you wish to prevent them from submitting forms that generate identical emails. And unfortunately, there is nothing you can do about the submission. But you can remove the duplicates from your final list. – Cooper Nov 21 '19 at 21:28
  • If you provide me with a sample linked sheet then I can show you how to get that accomplished. Note: I don't want an image. I want a sample sheet that I can copy the data from for testing. – Cooper Nov 21 '19 at 21:30
  • 3
    Actually, I was looking at the Google Form Tool and I guess if you can require sign in(i.e. they must have a google email account) then you can limit them to 1 response. – Cooper Nov 21 '19 at 22:10
  • First of all, thanks for the reply. I thank you for the suggestion, how to remove the duplicates from the sheet I know how to do ... what I would like is that it is done automatically through a script when you click on the send button of the module. I would like if a contact is already present (email) or overwritten or prevented from re-registering. PS: I can't understand why the scripts I found on this site don't work for me. TypeError: Could not call the "getSheetByName" method of null. (line 16, file "UPDATEcontact") – Mauro Mancini Nov 22 '19 at 10:18
  • Using access with google is impossible for me. We are talking about 12,000 contacts and I can't expect everyone to register on google. – Mauro Mancini Nov 22 '19 at 10:51
  • The error is probably due to the fact you haven't declared the `sheetTabName` variable. Declare it or use a string in `getSheetByName`, as it needs the name of your Sheet to work. I recommend you to look at the [Quickstart](https://developers.google.com/sheets/api/quickstart/apps-script) to learn the basics of Apps Scripts and Sheets, because what you are asking can be a bit tricky for beginners. – Jescanellas Nov 22 '19 at 10:58
  • @Jescanellas thank you My Sheet tab name = compleanno2020 sheetTabName = "compleanno2020";//Hard code if needed It's wrong? Where am I wrong? – Mauro Mancini Nov 22 '19 at 13:19
  • At short sight they look the same, could you edit your post with your code? – Jescanellas Nov 22 '19 at 13:40
  • Edit done, I entered the script code – Mauro Mancini Nov 22 '19 at 13:58
  • You are passing the `sheetTabName` as parameter to the function `updateExisting`. Where is it coming from? – Jescanellas Nov 25 '19 at 14:24
  • @Jescanellas thank you for your help but I don't understand much about programming. I simply copied and pasted this script I found on this site and it was intended to prevent duplicate inscriptions. I modified it based on the parameters of my spreadsheet but I have this problem: By clicking on the script - it works If I start it automatically (as I would like) with a trigger unfortunately it gives me an error! :-( – Mauro Mancini Nov 25 '19 at 14:48

0 Answers0