Search code examples
google-sheetsgoogle-apps-scriptgoogle-sheets-apigoogle-workspace

How do I compare new IDs with a column of existing IDs and allowcate a timestamp if two IDs match on Google Sheets?

I would really appreciate it if someone could help me get this tricky function right on google sheets.

Everytime stock is scanned I would like to see the timestamp next to that stock ID on google sheets. I am using an ESP32 to send scanned IDs (RFIDs) to google sheets. On my google sheets spreadsheet I already have all the existing stock IDs in a column. When the scanned IDs are sent to my spreadsheet I would like to compare those scanned IDs with the ones on my stock list and alocate a timestamp next to the stock ID if it was scanned in that time period.

Here is an example of what I would like to achieve. If one of the IDs in column B matches the ID in column A then display the current timestamp else do nothing. (Please see the attached images)

5 IDs scanned and sent to spreadsheet at 24/9/11 16:00 firsstScannedIDs

4 IDs scanned and sent to spreadsheet at 24/9/11 17:00 SecondScannedIDs

I managed to get this right with app script and a for loop but I had to use "new data" as a trigger to initiate the function when new IDs came in. I think that caused a nasty loop and was taking about 5 seconds to return a value for each cell of row C.

I also came close to getting this right with google sheets itself by comparing cell A2 with each cell of row B, =IF(OR(A2=B2,A2=B3,A2=B4,A2=B5),"timestamp").

Unfortunately,

  1. I have to edit this function for each line which can be quite tedious and risky if there are 400 IDs.
  2. It overides the previous timestamp with FALSE if there was no match.
  3. I also havent figured out how to store the current time as a timestamp on GS.

I would like to be able to do this on google sheets alone if that is possible.

Solution

  • You can do that by adding two columns:

    Column D holds timestamps that show when a value in column B was entered. This column is filled in by a script such as my yetAnotherTimestamp_ script.

    Column E looks up timestamps in D by matching keys in A to keys in B with this formula in cell E1:

    =arrayformula(ifna(vlookup(A1:A, B1:D, columns(B1:D), false)))