How to get cell color from Google Sheets nodejs

Question

I'm trying to get cell color from google sheets cell. I'm currently getting the value with the following code

const api = google.sheets({ version:'v4', auth: client });

const opt = {
  spreadsheetId: '??',
  range: 'Sheet!C1:C2'
}

var returnedData = await api.spreadsheets.values.get(opt);
// use returnedData here

The problem is that it returns range, majorDimension and values. I cannot get the cell color with any of these. I can't really find any help regarding this. I have looked into Cells (https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells), but can't figure out how to obtain them.

Thank you!

godzsa godzsa · Accepted Answer · 2020-07-22T13:55:30

You can use the effectiveFormat which is a CellFormat property of a Cell and there you have a backgroundColor property if that is what you look for.

In this example in the documentation you can see how they query the rows. That should contain RowData which will contain cells with CellData. I have not tried it myself, but if you want to query a specific cell just use it in the range and query the api. Then you can just index the rows array with 0 and allso the cells 0:

https://github.com/googleapis/google-api-nodejs-client/blob/master/samples/sheets/quickstart.js

As a warning, if you would like to run this with multiple cells, you should query them together in one range and then find them in the result with your code to make the app run faster.

How to get cell color from Google Sheets nodejs

1 Answers