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
4 IDs scanned and sent to spreadsheet at 24/9/11 17:00
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,
- I have to edit this function for each line which can be quite tedious and risky if there are 400 IDs.
- It overides the previous timestamp with
FALSE
if there was no match. - 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.
2
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)))
3
Compare new IDs with a column of existing IDs and allowcate a timestamp if two IDs match
I tried using appscript and checked this if it’s correct based on the problem given. I use the onEdit() function to trigger the scanned column only and use ternary for condition statements.
This is the output of the the code:
Code:
function onEdit(e) {
const sheets = SpreadsheetApp.getActive().getSheetByName('--SheetName--');
const sheetArray = sheets.getRange(2, 1, sheets.getLastRow() - 1).getValues()
e.range.getColumn() == 2 ? sheetArray.flat().includes(e.range.getValue()) ? sheets.getRange(e.range.getRow(), 3).setValue(new Date().toLocaleString("en-US")) : null : null;
}
Reference:
- includes
- ternary
- onEdit
3