CODEWITHSUNDEEP
jsongoogle-apps-scriptgoogle-sheetsbackground-colorgoogle-sheets-api
LANCE
LANCE

Reputation: 111

Calling Background colors from google sheets using Google sheets api is missing data

From a previous question linked here ( Previous Question ) I learned about Sheets.SpreadSheets.get calling a JSON of sheet data that would allow me to get the backgroundcolors of a sheet within my project. Id previously been doing this with var BackgroundColors = ActiveWeekSheet.getDataRange().getBackgrounds(); but was told that the JSON method would be a faster read/write method. They directed me to do some reading on Javascript objects but after that I'm still confused.

I've got the following code. TestArray = Sheets.Spreadsheets.get("1irmcO8yMxYwkcLaxZd1cN8XsTIhpzI98If_Cxgp1vF8"); which seems to call a JSON with sheet specific data. A logger statement of TestArray returns this: testArrayObject: {"properties":{"gridProperties":{"rowCount":1000,"columnCount":26},"sheetType":"GRID","index":0,"sheetId":0,"title":"Awesome"}}

Community members previously suggested I could then find the background colors at: sheets[].data[].rowData[].values[].cellData.effectiveFormat.backgroundColor

I've highlighted one of the cells yellow but when reviewing the above JSON i can't seem to find anything that references color. There definitely isn't any multileveling of the JSON to refer to sheets->data->rowData->values->celldata.effectiveFormat.backgroundColor.

What am I missing here? Do I need to format things someway? Am I not calling the right JSON to start with?

Thanks!

Upvotes: 0

Views: 715

Answers (2)

TheMaster
TheMaster

Reputation: 50382

As written in the documentation,

By default, data within grids will not be returned. You can include grid data one of two ways:

  • Specify a field mask listing your desired fields using the fields URL parameter in HTTP
Sheets.Spreadsheets.get(spreadsheetId, {
  ranges:"Sheet1!A1:A5",
  fields:"sheets(data(rowData(values(effectiveFormat.backgroundColor))))"
})
  • Set the includeGridData URL parameter to true. If a field mask is set, the includeGridData parameter is ignored
Sheets.Spreadsheets.get(spreadsheetId, {
  ranges:"Sheet1!A1:A5",
  includeGridData: true
})

In a nutshell,

  • multiple different fields are comma separated, and
  • subfields are dot-separated.
  • For convenience, multiple subfields from the same type can be listed within parentheses.
  • You may test the API here

Upvotes: 1

Diego
Diego

Reputation: 9571

There are optional parameters in the spreadsheets.get method that will give you that data, but you need to explicitly include them:

  • ranges – The ranges to retrieve from the spreadsheet.
  • includeGridData – The cell data within specified range.

This specifies a range of just one cell (A1 in Sheet1), but you can specify a larger range and navigate through the array if you need to.

var TestArray = Sheets.Spreadsheets.get(SS_ID, {ranges: "Sheet1!A1", includeGridData: true});

Really important that you keep in mind this returns a Color object with RGBA values that range from 0-1, but elsewhere apps script uses hex color or the conventional 0-255 RGB values.

Upvotes: 0

Related Questions