So, I've been trying to figure out a way to work on an individual cell of a given range, but I can't see how I can do this.

For instance, if I have set a variable to the range "A5:H25" and I want to change the values of one (or more) cells withing this range, how can one do this, keeping the other cell's values?

I know we can use an array of arrays to change multiple values, but what about the other values I want to keep untouched?

In my specific example, I have a range set this way:

var date = Utilities.formatDate(new Date(), "GMT 1", "dd/MM/yyyy");
var sprdsht = SpreadsheetApp.getActiveSpreadsheet();
var shtSessions = sprdsht.getSheets()[3];
var shtSessionslastRow = shtSessions.getLastRow();
var shtSessionslastCol = shtSessions.getLastColumn();

//Get table Ranges from the 4 sheets
var tblPushRange = shtPush.getRange(1, 1, shtPushlastRow, shtPushlastCol);
var tblSessionsRange = tblPushRange;

if (tblSessionsRange(1,1).getValue() == ""){ //<--- Check if 1st cell of the given range is empty
    tblSessionsRange.setValues([[date]]) //<-- Set that 1st cell value to date
}else{
    tblSessionsRange.offset(tblPushRange.getLastRow()   1,1).setValue(date);
    Logger.log(tblPushRange.getLastRow()   1)
}

So, how can I change values in individual cells and keep all the other values untouched, within this range I set?

Note: I think the code in the else block isn't correct either, but I'll address it later!

Thanks Psy

CodePudding user response：

Solution:

You can use getCell(row,column) on a range to get a specific single cell. Note that the first cell in the range is designated as (1,1).

Sample:

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A5:H25");

// set value of cell B7 to "A"
range.getCell(3,2).setValue("A");

Reference:

Class Range | getCell()