How to Append and Format Row at same time Google sheet API

Question

I want to appended in google sheet. i write a code and it successfully worked in JavaScript.

function appendMajor() {
  var responseJson ='{values : [["3/2/2017 13:38:32","3/2/2017","12:33:00 PM","ABC","xyz","pqr","bca"]] }';
  gapi.client.sheets.spreadsheets.values.append({
    spreadsheetId: 'spreadSheetId',
    range: 'A1:B',  
    resource: responseJson,
    valueInputOption: 'USER_ENTERED',
  }).then(function (response) {
    appendPre(response);
  }, function (response) {
    appendPre('Error: ' + response.result.error.message);
  });
}

I want to change background color of appended row. First three cell will be in blue color and another four cell will be in grey color.

I try to do it with BatchUpdate also but it overwriting the row of the given range, not appending in sheet file. if anyone know how to append row using BatchUpdate then please answer me.

score 6 · Answer 1 · answered Feb 12 '19 at 15:04

To get this in one shot, you will have to use batchUpdate with the AppendCellsRequest. Unfortunately, appending values in this way is a bit more long winded than spreadsheets.values.append, but it will get you what you want. Also, instead of specifying a range (your 'A1:B'), you will need the sheetId (0 for the default sheet). Here is an example applying your desired styling to the default sheet:

const values = ['3/2/2017 13:38:32','3/2/2017','12:33:00 PM','ABC'];
const colors = [
    [0.0, 0.0, 1.0],    //Blue
    [0.0, 0.0, 1.0],    //Blue
    [0.0, 0.0, 1.0],    //Blue
    [0.5, 0.5, 0.5]     //Grey
];
gapi.client.spreadsheets.batchUpdate({
    spreadsheetId: 'spreadsheetId',
    resource: {
        requests: [{
            appendCells: {
                sheetId: 0,
                rows: [{
                    values: values.map((v, i) => ({
                        userEnteredValue: {
                            stringValue: v
                        },
                        userEnteredFormat: {
                            backgroundColor: {
                                red: colors[i][0],
                                green: colors[i][1],
                                blue: colors[i][2]
                            }
                        }
                    }))
                }],
                fields: '*'
            }
        }]
    }
}, (err, resp) => {
    if (err) throw err;
    console.log(resp.data);
});

Working with Google APIs is always an adventure ^_^ Hope this helps.

If you use `AppendCellsRequest` there is no way to know the index of the inserted rows. That might be a major PITA for some, I know it was for me. :/ — Mattias, Mar 10 '19 at 20:40

score -1 · Answer 2 · answered Mar 10 '17 at 08:42

The only available built-in method for appending in the API is spreadsheets.values.append

Appends values to a spreadsheet. The input range is used to search for existing data and find a "table" within that range. Values will be appended to the next row of the table, starting with the first column of the table.

HTTP request

POST https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}:append

If you want sample codes, check the Append Values sample.

How to Append and Format Row at same time Google sheet API

2 Answers2