Time stamp update

Hi, I’m ussing this code to record the date and its working for one row, but how do i make it to record the dates of 3 diferent rows? i want to check when i turn a cell in F on and record that date, then another day i check on I and record that date, and same with L. I have very basic, more like non about coding.
As you can see, it is working for the first row.
Thanks a lot in advance!

So have you enabled a trigger for the script? It should work for any rows.

In var COLUMNTOCHECK = 6; it works for F, but how do I make it work for the other ones at the same time? that is what i cant figure out. I have been looking, but English is no my first language so maybe is that i dont know how to look for the answer. sorry to bother you

So apart from F you want to check for updates on the L column as well?

1 Like

Yes, I want to check updates on F, I and L, and record the dates of this changes individualy in the colums next to them.

Right now im ussing this code. If I take out that first part that I saw somewhere it doesnt work. I also Changed the activators to on change like @Mrinal_Chakraborty showed before.

function createSpreadsheetChangeTrigger() {

var ss = SpreadsheetApp.getActive();

ScriptApp.newTrigger('onChange')

  .forSpreadsheet(ss)

  .onChange()

  .create();

}  

// The column you want to check if something is entered.

var COLUMNTOCHECK = 6;

// Where you want the date time stamp offset from the input location. [row, column]

var INITIAL = [0,1];

var LATEST = [0,2];

// Sheet you are working on

var SHEETNAME = ‘Datos’

function onChange(e) {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getActiveSheet();

//checks that we’re on the correct sheet.

if(sheet.getSheetName() == SHEETNAME) {

var selectedCell = ss.getActiveCell();

//checks the column to ensure it is on the one we want to cause the date to appear.

if(selectedCell.getColumn() == COLUMNTOCHECK && selectedCell.getValue() == true) { 

  var initialcell = selectedCell.offset(INITIAL[0],INITIAL[1]);

  var latestcell = selectedCell.offset(LATEST[0],LATEST[1]);

  if(initialcell.getValue() !== ''){

  latestcell.setValue(new Date());}

  else {

  initialcell.setValue(new Date());}

  }

}

}

Would have to ask @Darren_Murphy on this. I believe the right approach here is to check if selectedCell.getColumn() is one of F, I or L (hence check if the value in getColumn is 6, 9 or 12).

Can we do a nested or/and in the if condition for Google Scripts? (to combine || and &&)

2 Likes

Okay, please let me know if I have the logic correct:

  • You want an onChange trigger that detects changes in this sheet
  • If the state of any row in column F changes from FALSE to TRUE, then write the current date to the same row in column G
  • If the state of any row in column I changes from FALSE to TRUE, then write the current date to the same row in column H
  • If the state of any row in column L changes from FALSE to TRUE, then write the current date to the same row in column ??? (K?? M??, which?)

Questions:

  • Should anything happen if the values in columns F, I or L change from TRUE to FALSE?
  • What should happen if the date is already there? Should it be overwritten, or left as it is?

No offence, but that script you posted is pretty awful. If you clarify the above, I’ll put something a bit better together for you.

Edit: hahaha, I just read back a bit and realised that the original script was posted by @ThinhDinh. Apologies my friend, I didn’t mean to diss your code :slightly_smiling_face: Maybe just the fact that it was very poorly formatted when re-posted turned me off it :rofl: :rofl: :rofl:

2 Likes

Thanks for your help, I just tried reussing some code i found since I have no idea how to do it, this is just something im tring to do to make my work a bit easier, I have never made any macros or code or anything.
I work with diferent varieties of strawberry plants, Colum F is to record when the plant makes stolens, column I when the plant flowers, and column L when the plant gets its berries. I want to record when each diferent plant does this diferent things.

The logic is as you said, column F is a checkbox in glide, which is false on default and turns true when checked. I want to save the date I checked the box on the column next to it, in this case G. I was having two rows, just because the code from @ThinhDinh was like that and I copypasted it. I just added that first part like I said becuase without it, it wont work.
For the Columns I and and L its the same record the date when they turn true on the next column so I → J and L → M.

The only reason to turn a value back to False is because you turned it True by accident, or if the value was on True and on accident you turn it false. The way that @ThinhDinh code works makes thsi a little fail prove, since you have bothe the first time you turn it true and the last, that way if you make on of these mistakes you can check both dates. Since this are one time events, I want t o record when they Start to do this things.

If its a lot of work I figured out I could just put a date box in Glide, I was going the other way since I have +1300 diferent plants to check one by one, so the less clicks I have to do I can save more time. people were doing this by writing down on paper the things and then into excel one by one so either way I already save a lot of time.

A script solution is easy enough, however after reading your use case I’d recommend that you keep this within Glide. You don’t need a date selector - here is one way you can do it:

  • Assume that your plants are presented as some sort of online list
  • Tapping on a list item takes you to a View Details screen for the tapped item
  • This screen can include your 3 checkboxes, and a submit/update button
  • Configure a custom (multi-step) action to assign to the button
  • This action would check the state of each checkbox, and if it’s changed from false to true, then it does a set column action on the corresponding date field, using the current date/time special value
  • You could also include a notification to indicate “success”, and a Go Back action to return to the list

So the user experience would be:

  • tap on a plant from the list
  • check the appropriate checkbox
  • tap the update button

The big advantage of doing it this way instead of using a script is that the change would be instantaneous (and you don’t have the overhead of trying to maintain a script).

Would that work for you?

2 Likes

Hahaha no worries Darren. I posted that more than half a year ago, if you feel like there can be better ways to do the same thing then I must listen to your word on it. You’re the script wizard on here.

2 Likes

Most of it is set up already like you told me, up to the 3 check boxes inside of the item.
Im trying to figure out the button part right now.

I make a normal button to put at the bottom, and made an action for it. The first problem is how do i just check that the value changed from the last time? since the events i record can haven weeks or months apart. If I make a condition for, if column F is True, then set column G (“the column where i record this date”) to curent date. I can make it record the date every time I push the button, but it updates the date every time. I haven’t used logic comands since highschool, but i think what i need is liek a elseif option? but i cand find how to do this.
I have checked glide oficial documentation but that didnt help at all.


This only works then the other ones are True before them

I could make what you said with 3 buttons one to update each date, and still have the visual indicator of the checkbox. be

Are you using User Specific Columns as temporary storage for your checkboxes?
If not, this will be your answer.
You can use those to compare to the current value of each boolean when the button is tapped.

So the pseudo-logic is something like:

If usc-checkbox-a is true AND checkbox-a is not true
then set checkbox-a column timestamp

Repeat for the other two checkboxes.

hmmm, but… that logic will short-circuit the first time the condition is true, so you may not be able to hit all 3 checkboxes with a single pass. That is, you may need a separate button for each checkbox.

I’ll have a think a bit more about this, and will see if I can suggest a better solution.

1 Like

Thinking more on this, do you even need the checkboxes at all?

Why not just have a date column for each of your 3 stages?
If the date is empty, then it hasn’t happened yet.

So when the user views the details screen for each plant, you can present 3 sections representing each of the 3 stages. In each one:

  • If the date is empty, show a button. If they tap on the button, it sets the date to the current date, the button disappears, and is replaced with the date.
  • If the date is not empty, then just show the date with no button

I think this would be a much simpler solution, no?

1 Like

In fact, you don’t even need buttons, or complex actions.
You can just use Action Text components, and visibility conditions.

I just knocked up a quick example, take a look:

2 Likes

Thanks a lot for your time! I just didnt know how do do it, and started with checkboxes into =today() but that didnt work, so I just kept trying to fix that. This seems so cool, I will try to implement it later today.

Thank you to you and @ThinhDinh for helping so much!

2 Likes

No problem. If you want, I can make that example copyable so you can see how I set it up.

@ThinhDinh I am using the script, But i need a small change and can use help with it. Before seeing this post i was working on a similar code for a week now.

What i am trying to do is if a col has a text included in a cell then update put the time stamp otherwise ignore. I need it to go row by row down. On a time.
My Raspberry pi edits the sheet every 15 min. I thought the edit will work. however i am the only authorized user. and the pi works with a (API EMAIL) so this code is useless unless i figure out how to ignore lines.

image
The raspberry updates the colors and puts a comment of the services on the left, then i have a script running to update the Up And Checks, So if Check exists update col BA instead from the app itself.

Do you need it to be a specific text, or any text will do?

@ThinhDinh

Also going to add this code that may help us to integrate with what i am trying to do.

 function Copy_Note_Tocell() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('NoIP');
GetNotes = sheet.getRange('C5:G').getNotes();
GetTime = sheet.getRange('C5:C').getNotes();  // Values I want to use
CurrentTime = sheet.getRange('b1').getValues();  // Values I want to use

  const repl = {
    "syn-ack": "✅",
    "conn-refused": "🤔 refused",
    "nmap timeout":"",
    "down/filtered": "🛑 Somthing is wrong",
    "no-response": "👎 no-response"
  };

  GetNotes.forEach((r,i) => r.forEach((c, j) => {
    for (const [key, value] of Object.entries(repl)) {
     if(c.includes(key))
     GetNotes[i][j] = `${value} ${GetNotes[i][j].slice(key.length+1)}`; }
     sheet.getRange('H5:L').setValues(GetNotes);
 }));

Update to the code i now use the .includes("✅") == true) Since .include gives a true or false.
Still looking forward for a fix for the other issue.

Would have to ask @Darren_Murphy here. I assume that getting the value from the cell and checks if the value contains a checkbox (using “includes”) would work? Is it the case here? Thank you for your time Darren.