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:
CodePudding user response:
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")
andchartOptions.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.