Extract Alt Text From Sheet Chart - Apps Script

user_19426259 0
Viewed 44

Is there a way to extract the alt text of a Google Sheet Chart in Apps Script?

function extractAltText() {
  var chart = SpreadsheetApp.getActiveSheet().getCharts()[0]
  var chartOptions = chart.getOptions()
  var altText = chartOptions.get("altText") //What I'm trying to figure out

  console.log(altText)
}

The above code doesn't work. It returns Null even though I have alt text on the chart[0].

I've looked through all of the documentation, but I can't seem to find a way.

I know that there's a method of extracting the alt text of a chart in Google Slides, but I'm specifically looking for a solution for a Google Sheet chart.

References/Documention:

google-apps-script google-sheets-api google-sheets
1 Answers

Also, in my environment, chartOptions.get("altText") returns null. And, also, when the description is added with setOption("altText", "sample"), the chart is broken, and I have confirmed the infinite loop for reopening the Spreadsheet occurred. So, in this answer, I would like to propose retrieving the description of the chart using Sheets API. When Sheets API is used, the script is as follows.

Sample script:

This script uses Sheets API. So, please enable Sheets API at Advanced Google services.

function extractAltText() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var chart = sheet.getCharts()[0];
  var id = chart.getChartId();
  var charts = Sheets.Spreadsheets.get(ss.getId(), { ranges: [sheet.getSheetName()], fields: "sheets(charts)" }).sheets[0].charts;
  if (charts.length == 0) return;
  var c = charts.find(({ chartId }) => chartId == id);
  if (!c) return;
  var { altText } = c.spec;
  console.log(altText)
}

Note:

  • This is a workaround for retrieving the description from a chart. I thought that the valid value except for chartOptions.get("altText") and chartOptions.get("description") for retrieving the description might be existed. So, I would like to look for it. If I found it, I would like to add it.

Reference:

user_71086530
Related