CODEWITHSUNDEEP
javascriptgoogle-drive-apigoogle-docsgoogle-docs-api
Rasmus Puls
Rasmus Puls

Reputation: 3219

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

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:

enter image description here

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.

Upvotes: 1

Views: 1465

Answers (1)

ziganotschka
ziganotschka

Reputation: 26796

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.

Upvotes: 1

Related Questions