How to change the active range into an array which can be set by setValues

Question

I've been working on this simple code in google scripts to execute on a button.

I want the user to be able to select two cells side by side, then click the button. The 1st selected cell will be updated with the date, the cell to the right will be incremented by one. So far I've gotten this far:

function increment() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var activeSheet = ss.getActiveSheet();

var activeValues = activeSheet.getActiveRange().getValues();

var newValues = [[Utilities.formatDate(new Date(), "UTC-8", 
"yyyy/MM/dd"),activeValues[1]+1]]; 

activeValues.setValues(newValues);

}

I think that I need to use setValues on getActiveRange but I'm not sure how to change the active range into an array which can be set by setValues.

I don't think I'm calling the correct element (1st element, second column) with activevalues[1]. Not sure how to call it.

Right now I get error: Cannot find function setValues in object,1.

Answer 1

The error occurs because activeValues is an array of arrays / 2D array but setValues is a method of Class Range.

Assuming that activeValues has the same shape that newValues, to do the minimal changes to your code you could instead of

activeValues.setValues(newValues);

use

activeSheet.getActiveRange().setValues(newValues);

Answer 2

The element from activeValues should be one of these:

• Number(activeValues[0][1])
• Number(activeValues[1][0])

The choice depends on whether the cells are horizontal or vertical.

And the values are set with activeSheet.getActiveRange().setValues(newValues).