Time stamp update

Ask for Help
63

I’m quite busy at work today. I can have a look this evening if help is still required then.

2 Likes
64

@abe.sherman how are you doing with this? Do you still need some help?

65

Yes i do, i need to be able to do a bulk script instead of active sheet.

Link to discord spreadsheet community: Spreadsheets Discord Community

66

Okay. I can take a look, but to make it a bit easier can you please share with me:

  • a copy of the current script you’re using
  • some sample data I can work with
  • a clear explanation of exactly what you need the script to do
1 Like
67

@Darren_Murphy We can use this script and combined it with what @ThinhDinh showed us? I made another video. Sorry if its a long one.

68

@Darren_Murphy Copy of Glide NOIP Sample - Google Sheets

69

Okay, so if there is a checkmark in column H, then you want a current timestamp in column AZ.
Is that it?

70

Yes

71

Okay, gimme a few minutes.

72

Look in your script editor for a file called darren.gs

Tell me if that does what you want.

73

Looks good so far, Can we do col Name instead of col Number, to avoid issues when i move tabs?

74

Okay…

75

Try it now.
It looks for ‘HTTP_up’ and ‘Monitoring Since’

76

Thank you, you work fast. Wow a code like that would take me a few days to a month.
I will share the code here so others can find it useful. Hats off to you @Darren_Murphy

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('NoIP');
  var headers = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues();
  var source_col = headers[0].indexOf('HTTP_up') + 1;
  var target_col = headers[0].indexOf('Monitoring Since') + 1;
  var data = sheet.getRange(2,source_col,sheet.getLastRow()-1,1).getValues();
  var timestamps = [];
  var now = new Date();
  data.forEach(function (row) {
    var val = row[0];
    if (val.match(/✅/) ) {
      timestamps.push([now]);
    }
    else {
      timestamps.push(['']);
    }
  });
  sheet.getRange(2,target_col,sheet.getLastRow()-1,1).setValues(timestamps);
}
1 Like
77

You’re welcome.
When I looked earlier today, my gut told me that you had overcomplicated the problem, but I didn’t have the time then to have a close look.

1 Like
78

I usually over complicate stuff when it comes to coding. I am not good at it. but i play with it. need to run, Picking up my son from school now. Catch up soon.

79

Thanks for the trick about using column headers, would be very useful in many cases for me.

1 Like
80

yeah, I’ve started using that in my trigger scripts that remove “deleted” rows. Having hard-coded column numbers always made me super nervous. I might make a post about that, as some of the examples we have in the forum (including some that I’ve posted) aren’t really that good.

2 Likes
81

Sorry to bother you. @Darren_Murphy But I realized if the the CHECKMARK dose not exist it clears the cell. Anyway to avoid that?

82

Yeah that can be fixed, but requires reworking the function a little bit.
I still have access to that sheet that you shared, so I can make the changes there.
Do you have a backup of that? (just in case I screw up) :wink:

1 Like