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.

Answer 1

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.