Google Scripts: Testing if value already exists in google sheets row

Question

I am new to Google Scripts and I am trying to only add data to a Google Sheet if that value doesn't already exist in a row. The following code always adds the data to the row (it is working and functional):

function doPost(e){
  return handleResponse(e);
}

function handleResponse(e) {
  var lock = LockService.getPublicLock();
  lock.waitLock(30000);  // wait 30 seconds before conceding defeat.
  try {
// set where I write the data
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
var column = sheet.getRange("A:A");  // like A:A
var values = column.getValues(); 
var addToSheet = false;
// header row is 1st row
var headRow = e.parameter.header_row || 1;
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var nextRow = sheet.getLastRow()+1; // get next row
var row = []; 
// loop through the header columns
for (i in headers){
  if (headers[i] == "Timestamp"){ 
    // special case if you include a 'Timestamp' column
    row.push(new Date());
  } else { 
    // else use header name to add to the array of values to add
    row.push(e.parameter[headers[i]]);
  }
}
// set all values at once
  sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);

// return json success results
return ContentService
      .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
      .setMimeType(ContentService.MimeType.JSON);
} catch(e){
// if error return json error
return ContentService
      .createTextOutput(JSON.stringify({"result":"error", "error": e}))
      .setMimeType(ContentService.MimeType.JSON);
  } finally { 
    //release lock
    lock.releaseLock();
  }
}

I know I need to add something like

if(!values.includes(e.parameter[headers[i]][j])){       
    row.push(e.parameter[headers[i]]);
}

To test if any values passed in already exist in the row, but this isn't working in Google Scripts. Any advice? Thanks!

Can I ask you about the value and [j] of e.parameter[headers[i]][j]? — Tanaike
There isn't a shortcut. Write a helper function that does the exhaustive check you want, e.g. function checkEveryCell(forTheseValues) and returns either true or false as appropriate. If the columns to be checked for the values depends on the headers supplied, then you would need to work the logic on that too — tehhowch

CalamitousCode CalamitousCode · Accepted Answer · 2018-12-14T09:45:23

You could read the row values as a string then check the index of the value you're looking for. If it's not there, the index is -1.

If you're checking for numbers, I'd suggest converting valueToCheckFor to a string. (Though I haven't checked to see if that's necessary and I couldn't tell you off the top of my head)

This is a cheeky way to avoid checking all cells individually. Depending on what your data is, it may be unsuitable. If you're only checking text, it should be fine.

var stringValues = valuesToCheck.toString();
//The row values become a comma-separated string.

var stringExists = stringValues.indexOf(',' + valueToCheckFor + ',') > -1;
//check if value exists in string
//commas added to ensure whole cell is matched
//without commas, searching for 'dog' will match cells containing 'dogs', 'dogo', 'corn dog', etc.

if(!stringExists) {       
    row.push(valuesToPush);
}

Google Scripts: Testing if value already exists in google sheets row

1 Answers