Change table background color using google docs API (node.js)

Question

I am generating some reports using google docs and drive API. I want dynamically set the background color in my table cells based on how high or low a number is, the lower the number the more red color, the higher the number the more green.

Take a look at this example:

I am able to update the numbers from my node service like this:

const auth = new google.auth.GoogleAuth({
  keyFile: 'credentials.json',
  scopes: ['https://www.googleapis.com/auth/documents', 'https://www.googleapis.com/auth/drive']
});

// Create client instance for auth
const client = await auth.getClient();
const docs = google.docs({ version: 'v1', auth: client });
const drive = google.drive({ version: 'v3', auth: client });

const copyMetaData = {
  name: `Some report`,
  parents: ['1b78gdfgdf8gdiokkg4XG2-9WFWGGla'] // some folder id
};

const copiedFile = await drive.files.copy({
  auth,
  fileId: '1VMKs89fg9AGP1xs-1F545gKgkvf2Daza7UZMRsdf789', // some file id
  requestBody: copyMetaData
});

const requests = [
  ['{{data-1}}', '447'],
  ['{{data-2}}', '378'],
  ['{{data-3}}', '102'],
  ['{{data-1-p}}', '91'],
  ['{{data-2-p}}', '78'],
  ['{{data-3-p}}', '23']
].map(update => {
  const [handleBar, replaceText] = update;
  return {
    replaceAllText: {
      containsText: {
        text: handleBar,
        matchCase: true
      },
      replaceText
    }
  };

I Just can't figure out how I can set background color, or even identify my table cells. Or whether this is even possible or not, but it seems like a quite trivial use case to me, as in all the googles examples they are generating invoices and you would easily think that one would want to make a negative balance bold and red, but but for example black if its a positive balance.

ziganotschka · Accepted Answer · 2021-07-20 15:05:12Z

1

Sample request to update table cell colors for Google Docs:

{
  "requests": [
    {
      "updateTableCellStyle": {
        "tableCellStyle": {
          "backgroundColor": {
            "color": {
              "rgbColor": {
                "green": 1
              }
            }
          }
        },
        "fields": "backgroundColor",
        "tableRange": {
          "columnSpan": 1,
          "rowSpan": 1,
          "tableCellLocation": {
            "columnIndex": 0,
            "rowIndex": 0,
            "tableStartLocation": {
              "index": 2
            }
          }
        }
      }
    }
  ]
}

In my case the index of the table location in the document is 2 - adapt accordingly to your location.

answered Jul 20, 2021 at 15:05

ziganotschka

26.9k2 gold badges18 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Rasmus Puls Over a year ago

Thanks for you answer :) This looks promising, I will try this and get back to you.

Rasmus Puls Over a year ago

Alright, so I'm getting this error: Invalid requests[18].updateTableCellStyle: The provided table start location is invalid. which means that I don't understand this tableStartLocation property. I would assume my tableStartLocation would be index: 0, as I only have 1 table in my document body. I'm reading here: developers.google.com/docs/api/reference/rest/v1/documents/… I do have a header, and some text before my table starts, would that mean anything? How can I identify this index?

ziganotschka Over a year ago

You can use the method documents.get and retrieve the startIndex of the first table row by setting fields to body/content/table/tableRows/startIndex. The tableStartLocation index will be one less than the tableRows/startIndex.

Rasmus Puls Over a year ago

By using the document.get endpoint, and inspecting the content, I find this object, that i assume must be the table:

{   startIndex: 88,   endIndex: 1358,   table: {     rows: 15,     columns: 4,     tableRows: [....],     tableStyle: { tableColumnProperties: [Array] }   } }

But when I provide 88 or 87 as tableStartLocation I still get the same error: The provided table start location is invalid. Any idea?

Rasmus Puls Over a year ago

In the end it worked out, however, the tableStartLocation index must be the same as the startIndex from the inspected document, (not minus 1). I rewrote my code and in the end it worked. I think something weird happened with race condition, I was replacing text while also updating table cells, and I think the indexes might have shifted meanwhile.

|

Collectives™ on Stack Overflow

Change table background color using google docs API (node.js)

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related