Search code examples
google-apps-scriptgoogle-sheetsgoogle-sheets-query

Checking if there is not record with that ID in in Google Sheets

I need to check values via existDataInSheet function by unique Id parametr in A column, if my List has record with this id - I don't add it again and void function appendDataInSheet? For example, my code adding records with the same ID every time, but I don't need it.

function saveQuery(data) {
  try {
    var lock = LockService.getPublicLock();
    lock.waitLock(2000);
    
    //function to check new Posting data
    var exist = existDataInSheet(data, 'Data');
    if (!exist) {
      data = [data];
      appendDataInSheet(data, 'Data');
    }
    
  } finally {
    lock.releaseLock();
  }
}


function existDataInSheet(data, sheetName) {  
    var cells = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getDataRange().getDisplayValues();
    var row = data.join(',');
    for (var i = 0; i < cells.length; i++) {
      var current = cells[i].join(',');
      if (row === current) {
        return true;
      }
    }
    return false;
}
  1. How can I add records in List with two unique params Id(column A) and Date(column D) once per day in existDataInSheet function? For example, I add a record with ID 111111 and date 27.06.2020 after that I can't add record with ID 111111 and Date, but tomorrow I can add new record in list with ID 111111 again with date 28.06.2020 and so on.
Solution

  • Answer:

    One way of doing this would be making use of the PropertiesService class to store which records have already been added today.

    More Information:

    There are a lot of ways of doing this, as Cooper said in his comment you can use Array.prototype.indexOf() to check if the ID is in the column, or if using V8 and you have large amounts of data so time is an issue, you can use a Set as Sunny Patel suggested as this is much quicker.

    Honestly though to save you checking the entire column it would be quicker to just store the IDs you've already entered today and just disallow those ones to be entered again.

    For example, when adding the record to the sheet, you can create a Script Property with the ID:

    function appendDataInSheet() {
  // some code goes here
  // assuming you use .appendRow() to enter the data:
  sheet.appendRow([id, date]);
  
  // then save the ID:
  PropertiesService.getScriptProperties().setProperty(id, true);
}

    What you can then do is check if the Property for that ID exists:

    function hasIdBeenEnteredToday(id) {
  var properties = PropertiesService.getScriptProperties().getProperties;

  if (properties[id] === true) {
    return true;
  }
  else {
    return false;
  }
}

    Then you can set up a time-based trigger to go off at midnight every day which clears all your properties ready for the day:

    function clearProperties() {
  PropertiesService.getUserProperties().deleteAllProperties();
}

    Setting up a Trigger:

    Follow the Edit > Current project's triggers menu item, and in the newly opened page, click the + Add Trigger button in the bottom right and set up the trigger settings as follows:

    • Choose which function to run: clearProperties
    • Choose which deployment should run: Head
    • Select event source: Time-driven
    • Select type of time based trigger: Day timer
    • Select time of day: Midnight to 1am

    And press save.

    This doesn't directly check the Sheet to see if the ID exists, but because you store all the IDs you've stored today you're circumventing this by just checking if it's already been entered.

    References:

    Other Reading: