CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
BeckyW
BeckyW

Reputation: 47

Trying to combine two working scripts does does not work

I'm trying to make a quick macro to grab the current date and time and put it in a cell. I want to make this a static time. I made a quick macro by recording and it works as I recorded it, but when I ran the recorded macro it does nothing. I am just doing a now function and then copying it and pasting the value on top. If I split it into two separate macros, it works. I have no idea what I am doing wrong. Any help is appreciated. "Timestamp" does not work. "Now" works. "copypaste" works.

'''

function TimeStamp() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getCurrentCell().setFormulaR1C1('=now()');
  spreadsheet.getActiveRange().copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

function Now() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getCurrentCell().setFormulaR1C1('=now()');
  };

function copypaste() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getActiveRange().copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

'''

Upvotes: 2

Views: 57

Answers (1)

Kin Siang
Kin Siang

Reputation: 2699

You may use date object to return timestamp compared with using =now formula, which is faster and prevent script to skip your next line:

function TimeStamp() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getCurrentCell().setValue(new Date());
};

Reference

Get today date in google appScript

Edit: Original issue

In the original script, the script might not apply the changes after setting the formula =now() and before copy/paste, since spreadsheet operations are sometimes bundled together to improve performance. So it's treating the active range as empty, and after pasting this empty value, the cell will remain empty.

To avoid that, you could have used SpreadsheetApp.flush() after setting the formula =now().

Upvotes: 1

Related Questions