google-sheetsgoogle-apps-scriptcharts

How do I update the font color of a key value of a Scorecard chart?

I've tried several ways to change the font color of the key value of a scorecard chart in Google Sheets, but I'm unable to do so. option titleTextStyle.color changes the Title color only. I've tried other options such as textStyle.color, titleTextStyle.color, comparisonTextStyle.color, labelTextStyle.color, but to no avail.

I tried all options I can find. I just need the color of the font to update, and I'm planning to set a time trigger to do so.

Scorecard Chart

    function updateScorecardColors() {
      const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
      const sheets = spreadsheet.getSheets();

      // Color thresholds
      const colors = {
        high: '#34a853',    // Green (>10)
        medium: '#fbbc04',  // Yellow (>5 and <=10)
        low: '#ea4335'      // Red (<=5)
      };

      sheets.forEach(sheet => {
        const charts = sheet.getCharts()[0];

        if (charts) {
          const value = sheet.getRange('A5').getValue();
          let color;

          if (value > 10) color = colors.high;
          else if (value > 5) color = colors.medium;
          else color = colors.low;

          const updatedChart = chart.modify()
            .setOption('textStyle.color', color)          

          sheet.updateChart(updatedChart);
        }
      });
    }
Solution

  • Let's start by saying that the Google Apps Script Spreadsheet Service does not support all the options supported by Google Sheets web app.

    As there is no documentation in the Spreadsheet Service reference about the Scorecard chart, it's fair to say that this service can't be used to set the key value format for this chart; however, the Sheets API has it --ref. KeyValueFormat. This means that it's possible to use the Advanced Sheets Service to set the key value format.

    You will need to craft a updateChartSpec request and send it using BatchUpdate Request.

    Related