Update Sheets cell based on offset

Question

I'm trying to use a javascript call to update the value in one cell, based on an offset from a cell I've found to contain a particular value.

For example, I'd like to iterate through column A until I find "bar", then set the value of the B column in that row to 1:

I know how to read the values in the first place:

gapi.client.sheets.spreadsheets.values.get({
    spreadsheetId: SPREADSHEET_ID,
    range: 'Sheet1!A1:A3',
}).then(function(response) {
    var range = response.result;
    for (i = 0; i < range.values.length; i++) {
        if (range.values[i] == "bar") {
            // Update column B in this row to 1
        }

And I know I need some kind of invocation of spreadsheets.values.update:

gapi.client.sheets.spreadsheets.values.update({
    spreadsheetId: SPREADSHEET_ID,
    range: the range found above, offset by one column,
    valueInputOption: 'RAW',
    values: [ [ value ] ]
});

But how can I get the address offset from the range found in the code above?

[this answer](http://stackoverflow.com/a/30112523/2071807) about converting row/column to A1 reference style looks like a promising start. — LondonRob, Jan 13 '17 at 16:09

score 0 · Answer 1 · answered Jan 13 '17 at 14:42

There's a simple, but seemingly rather dumb way to do this, updating the entire range, rather than just the subset that has changed.

var address = 'Sheet1!A1:A3';
var range;

gapi.client.sheets.spreadsheets.values.get(
    {
        spreadsheetId: SPREADSHEET_ID,
        range: address,
    }
).then(function(response) {
    range = response.result; // available globally
    for (i = 0; i < range.values.length; i++) {
        if (range.values[i][0] == "bar") {
            range.values[i][1] = 1;
        }
    }
).then(function() {
    gapi.client.sheets.spreadsheets.values.update({
        // Update entire range back into spreadsheet.
        spreadsheetId: SPREADSHEET_ID,
        range: address,
        valueInputOption: 'RAW',
        values: range.values 
    }).then(function(response) {
        // Having a then clause seems to be necessary for the
        // update to happen. Not sure why.
        console.log(response);
    });

But this seems a bit weak to me. What if the range is huge, and you only want to update what's changed? Maybe someone can improve this.

Update Sheets cell based on offset

1 Answers1