how to add a constant to an entire column in google sheets

Question

i have some data that i want to add a time stamp to. Currently I'm doing this by running a loop, and adding the data to colomn 51 with the code below:

var date = Utilities.formatDate(new Date(), "GMT-4", "yyyy-MM-dd HH:mm")   
var NewLastRow = ss.getLastRow();
for (var i = 3; i < NewLastRow; i++) {
ss.getRange(i,51).setValue(date);
ss.getRange(i,52).setValue("New_Orders");
   }

this works, but seems super slow to run over 5000+ rows. Is there a better way to do this where i don't have to use a for loop and just assign the entire column with the a constant date value?

thanks

Why haven't you searched for similar questions? Why didn't you read best practices? See [tag info page](https://stackoverflow.com/tags/google-apps-script/info) for more details. — TheMaster, Jul 27 '20 at 20:28

Marios · Accepted Answer · 2020-07-28T05:41:58.110

1

Here is the solution you are looking for:

function myFunction() {
  
var ss = SpreadsheetApp.getActive().getSheetByName("Sheet1")
var date = Utilities.formatDate(new Date(), "GMT-4", "yyyy-MM-dd HH:mm")
var NewLastRow = ss.getLastRow()

ss.getRange(3, 51, NewLastRow , 1).setValue( Array(NewLastRow).fill(date) )
ss.getRange(3, 52, NewLastRow , 1).setValue( Array(NewLastRow).fill("New_Orders") )

}

You should avoid using the function setValue() inside of a loop.

edited Jul 28 '20 at 05:41

answered Jul 27 '20 at 20:28

Marios

26,333
8
32
52

There is a better top voted answer there – TheMaster Jul 27 '20 at 20:32
I know, but push gives the flexibility to add also different values. It is a more generic approach in GAS and it is often overlooked. – Marios Jul 27 '20 at 20:34
In your latest script, where `setValue` is used, You don't need a array. Just a string/date object will do. – TheMaster Jul 28 '20 at 03:54

how to add a constant to an entire column in google sheets

1 Answers1