google-apps-scriptgoogle-sheets-apigoogle-slides-api

Cannot insert Sheets chart to presentation via Google Slides API


I am trying to insert chart to Google Slides presentation using CreateSheetsChartRequest.

Request fails with error:

Execution failed: GoogleJsonResponseException: Invalid value at 'requests[0].create_sheets_chart.chart_id' (TYPE_INT32), "u1935822328711"

Sample code:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var chartSheet = ss.getSheets()[0];

// get chart id via Sheets API
// returns string like 'u1935822328711'
var chartId = chartSheet.getCharts()[0].getId();

var slidesReqs = [];

slidesReqs.push({
  createSheetsChart: {
    objectId: 'someSlideChartId',
    elementProperties: {
      pageObjectId: slideId,
      size: {
        width: {
          magnitude: (7/9)*900,
          unit: 'PT'
        },
        height: {
          magnitude: (7/9)*560,
          unit: 'PT'
        }
      },
      transform: {
        scaleX: 1,
        scaleY: 1,
        translateX: 100000,
        translateY: 100000,
        unit: 'EMU'
      }
    },
    spreadsheetId: ss.getId(),
    chartId: chartId,
    linkingMode: 'NOT_LINKED_IMAGE'
  }
});

// throws error
// Execution failed: GoogleJsonResponseException: Invalid value at 'requests[0].create_sheets_chart.chart_id' (TYPE_INT32), "u1935822328711"
Slides.Presentations.batchUpdate({'requests': slidesReqs}, presentationFileId);

I see that CreateSheetsChartRequest expects field chartId to be integer, but Google Sheets API returns string like "u1935822328711" for chart id.

How do I get Google Sheets chart inserted to slide?


Solution

  • Short answer

    Use getChartId() instead of getId().

    Explanation

    EmbeddedChart.getId() returns an ID that is only useful in the context of UiApp, a deprecated service for building component-based UIs.

    However, as of today, there is now a method on the EmbeddedChart class called getChartId() that returns a unique, stable integer identifying the chart.

    If you change the one line in your code to

    var chartId = chartSheet.getCharts()[0].getChartId();
    

    your CreateSheetsChartRequest should now work.

    Source: https://issuetracker.google.com/issues/79784012#comment2