How to get a timestamp with GAS?

[Osvaldo]

Hi all,

I run a script whose sole function is to copy paste a report that I download from JIRA. The reason for this is that such report fails way more often that I'd expect it to. Therefore I copy and paste the whole sheet by range into another sheet, being this sheet with the copied values the one from which I pull data for a dashboard.

One interesting thing I wanted to do is to set a timestamp so that managers could know when was the last time that the data was refreshed.

For that I included this chunk of code after the copypasting:

sheet2.getRange('BO2').setFormula("=now()");

sheet2.getRange('BO2').activate();

sheet2.getRange('ForDashboard!BO2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES,false);

However, it is not working.

Here you can see the whole script

function Dashboard() {

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var sheet1 = spreadsheet.getSheetByName('Dashboard');

  var sheet2 = spreadsheet.getSheetByName('ForDashboard');

  var range = sheet1.getRange(1,1).getValues();

  var Avals = sheet1.getRange("B1:B").getValues();

  var Alast = Avals.filter(String).length;

    if( range != '' && Alast > 7000) 

  {

  sheet2.getRange(1, 1, sheet2.getMaxRows(), sheet2.getMaxColumns()).activate();

  sheet2.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});

  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('ForDashboard'), true);

  spreadsheet.getRange('Dashboard!A1:BR').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES,false);

  sheet2.getRange('BO2').setFormula("=now()");

sheet2.getRange('BO2').activate();

sheet2.getRange('ForDashboard!BO2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES,false);

  }

  else {  

  }

};

Can anybody tell me why?

I've also tried this, but it is not working because it is throwing me an error:

function onEdit(e) {

  if (e.range.getA1Notation() === 'BO2') {

    // put your code in

  }

}

Basically through that last chunk of code, another script would be run whenever the value changed for the cell containing the timestamp of the last refresh. Not working though...

Answers are extremelly appreciated :)

Best

[laurie.nason]

Hi,

With regard to putting a date in to the sheet - rather than this:

sheet2.getRange('BO2').setFormula("=now()”);

Use :

sheet2.getRange('BO2’).setValue(new Date());

When you set it to Now() that will automatically update to the latest time on opening the sheet and every time there’s an edit. You want a fixed date/time in the cell (which you can format on the sheet however you want to)

Hope this helps.

Laurie

------ Original Message ------

From "Osvaldo" <osval...@gmail.com>

To "Google Apps Script Community" <google-apps-sc...@googlegroups.com>

Date 2/28/2023 10:53:05 AM

Subject [Apps-Script] How to get a timestamp with GAS?

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/b9f7ab67-6ba7-43ab-b352-08946873532fn%40googlegroups.com.

[Osvaldo]

That was indeed useful,

The only doubt I still have is... how can I set the timestamp of the moment that command was executed?

Thank you so so much! :)

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/Hqzq_mfNFck/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/em89251eef-41d0-4936-9562-dde71e658fbb%404b70aa6a.com.

[laurie.nason]

This will put in the time that that line of code is called - I would think it’s probably accurate enough for you - you could put the line earlier in the script, but I tend to do the following - 

Before I start doing any changes to the data - clear the value in the field to show that the process has started (you could always have 2 fields where you put a start time in one, then an end time in the other)

After I have done my data manipulation put in the finish time - that way I know the process actually completed if there is a date in there. And if you really want to you could calculate the update time using a regular sheets function on the appropriate tab.

Laurie

------ Original Message ------

From "Osvaldo" <osval...@gmail.com>

To google-apps-sc...@googlegroups.com

Date 2/28/2023 12:30:41 PM

Subject Re: [Apps-Script] How to get a timestamp with GAS?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CACguRvBiyy3%2BmWjDtDGvJj%2Bpc%3DRBHA-YDX0KEhEBi554AwyK2g%40mail.gmail.com.