LinkedIn
Twitter
Hello everyone, I am new to Script writing in Sheets (or anywhere else for that matter!!). I have set up an applet in If this, then that (IFTTT) to record my time started at work and finished at work, along with duration at work and hours rested (not at work). Are there any geniuses that could check over my script to see where it is going wrong please? The data was being written in to sheets ok - start or finish work, date and time but I am now getting a parsing error for the Time after adding +or- time in to the script.
To give you a bit of background, I work on a tug boat in Australia and I’m on call 24hrs a day for 28 days straight and then I have 28 days off. I need to track the hours I come through the work gate/leave the gate. I also need to minus 40 minutes from the time I start work (through the gate) and add 30 minutes to the time I leave work. I keep getting a parsing error for the time and N/A (Error, wrong number of arguments to value. Expected 1 argument but got 2 arguments) for the duration.
I was originally getting a parsing error for the date but since I changed “substitute” for Regexreplace then it started working. I have set the columns to date, time and duration and changed the locale to Australia then back to USA after it didn’t fix it. I’ve copied my code below.
Also, because I work on tug boats, I need to log the hours I rested in a 24hr period (not at work) - it’s an international maritime law- is there any code I could add to the below code I have in IFTTT to do this for me, along with total hours rest (not worked) I’ve had in 7 days?
Because I work on call 24hrs a day, I arrive/leave work several times a day depending on how many ships I need to bring in/take out of the port. The way IFTTT works is, it adds a new row with the data each time it is triggered by me going through a geofence. I tried adding a formula in to the cells for hours rested (=24 - total hours worked cell) but because there was data in the cell, any new times I arrived/left work were input in the row below so that was no good, the script needs to be writen in to the below script so it is added in to the row by IFTTT when it is triggered.
If you could help in any way it would be really appreciated.
Thanks!
=IF("{{EnteredOrExited}}"="entered","Start work","Finished work")
||| =DATEVALUE(REGEXREPLACE(" {{OccurredAt}}", " at .*", ""))|||=TIMEVALUE(SUBSTITUTE("{{OccurredAt}}"," at ", " "))-time(0,40,0),TIMEVALUE(SUBSTITUTE("{{OccurredAt}}"," at ", " "))+
time(0,30,0))|||=if(indirect("b" & row()-1)=indirect("b"&row()),if(indirect("a"& row()-1) =
"Start work",VALUE(TEXT(indirect("c"&row())-indirect("c"&row()-1)),"h:mm"),""),"")
