Identify specific charts on google sheets using google script

Question

I have a google Slides presentation containing a chart tied to a google Spreadsheets Sheet. I created a plug-in (google script), using which I am able to paste the sheets url and have all available charts by id.

I am unable to overcome the issue of identifying the chart I need solely using its id which is generated.

Right now I can do this:

  var spreadSheetContainingChartAndData = Sheets.Spreadsheets.get(sheetsId)
  var allSheets = spreadSheetContainingChartAndData.sheets
  var allChartsOnTheOnlyExistingSheet = dataSheet.sheets[0].charts
  var firstChart = allChartsOnTheOnlyExistingSheet[0]

  var chartId = firstChart.chartId

Ideally I would want to find the chart by some form of user defined identifier like this:

 var chartId = findByAnythingElse(allChartsOnTheOnlyExistingSheet, 'user-defined-tag')

The usecase is to have a spreadsheet template containing client data and a number of charts visualising said data. Whenever someone wants to make a presentation, he can click on the plugin menu and have certain slides to be populated with specific charts which will be cropped and resized to a predefined format.

Did my answer show you the result what you want? Would you please tell me about it? That is also useful for me to study. If this works, other people who have the same issue with you can also base your question as a question which can be solved. If you have issues for my answer yet, I apologize. At that time, can I ask you about your current situation? I would like to study to solve your issues. — Tanaike
@Tanaike apologies for the delay. Your answer is the closest I got. I was actually more interested in some form of metadata instead of title, subtitle etc, as I didn't want to modify the template with things which were not set by the designer of the template, and which might change due to appearance modifications (i.e I rely on the title of the chart but the design changes to not include a chart title in the slide). Though altText seems to be sufficient for now. — PaulB
Thank you for replying and the additional information. I'm glad your issue was resolved. Thank you, too. — Tanaike

Tanaike Tanaike · Accepted Answer · 2019-07-10T22:31:25

You want to retrieve the chartId from all charts in a Spreadsheet.
You want to search the chartId using the specific tag.
You want to achieve this using Google Apps Script.

If my understanding is correct, how about this sample script? Please think of this as just one of several answers.

In this answer, the chart's title, subtitle and alt title are used as the search tags.

Sample script:

Before you use this script, please enable Sheets API at Advanced Google Services. And when you test this script, please set the variables for searching the chart and run the function of run().

function findByAnythingElse(spreadsheetId, searchObj) {
  var obj = Sheets.Spreadsheets.get(spreadsheetId, {fields: "sheets(charts(chartId,spec(altText,subtitle,title)))"});
  var chartIds = [];
  for (var i = 0; i < obj.sheets.length; i++) {
    var charts = obj.sheets[i].charts;
    if (charts) {
      for (var j = 0; j < charts.length; j++) {
        var title = charts[j].spec.title;
        var subTitle = charts[j].spec.subtitle;
        var altText = charts[j].spec.altText;
        if (title == searchObj.searchTitle || subTitle == searchObj.searchSubTitle || altText == searchObj.searchAltText) {
          chartIds.push(charts[j].chartId);
        }
      }
    }
  }
  return chartIds;
}

// Please run this fnuction for testing.
function run() {
  var searchObj = { // Please set search values.
    searchTitle: "sample1",
    searchSubTitle: "",
    searchAltText: "",
  }
  var spreadsheetId = "###"; // Please set Spreadsheet ID here.

  var res = findByAnythingElse(spreadsheetId, searchObj);
  Logger.log(res)
}

In this sample script, when run() is run, one of searchTitle, searchSubTitle and searchAltText of searchObj is matched, the chart ID is retrieved.
- For example, the chart ID can be also retrieved from only searchTitle.
- When there are several charts with the same title, several chart IDs are returned.

Note:

This is a simple sample script. So please modify it for your situation.

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Identify specific charts on google sheets using google script

1 Answers

Sample script:

Note:

References: