How to update a chart in Google Slides dynamically?

Question

I am trying to update the chart dynamically that is embedded in a Google Slides, but it throws an error at me.

This is the function that I came up with to replace some specific text that is placed in my base Google slide.

 public function getGoogleDrivePdf($fileId, $substitutions)
{
    $client = $this->getGoogleClient();
    $driveService = new \Google_Service_Drive($client);
    $slidesService = new \Google_Service_Slides($client);
    $copy = new \Google_Service_Drive_DriveFile([
        'name' => $substitutions['student_name'] . ' presentation',
    ]);
    $driveResponse = $driveService->files->copy($fileId, $copy);
    $slideFileId = $driveResponse->id;
    $requests = [];
    foreach ($substitutions as $key => $value) {
        $requests[] = new \Google_Service_Slides_Request(
            [
                'replaceAllText' => [
                    'containsText' => [
                        'text' => '{{'.$key.'}}',
                        'matchCase' => true
                    ],
                    'replaceText' => $value
                ]
            ]
        );
    }

$requests[] = new \Google_Service_Slides_Request(
        [
            'createSheetsChart' => [
                'spreadsheetId' => 'N_St1Lm-oxd4aWOjQ6o.......',
                'chartId' => ...040....,
                'linkingMode' => 'NOT_LINKED_IMAGE',
                'elementProperties' => [
                    'pageObjectId' => '.....9fd6d_.....',
                    'size' => [
                        'height' => $emu4M,
                        'width' => $emu4M
                    ],
                    'transform' => [
                        'scaleX' => 1,
                        'scaleY' => 1,
                        'translateX' => 100000,
                        'translateY' => 100000,
                        'unit' => 'EMU'
                    ]
                ]
            ]
        ]
    );

    $batchUpdateRequest = new \Google_Service_Slides_BatchUpdatePresentationRequest([
        'requests' => $requests,
    ]);
    $updateResponse = $slidesService->presentations->batchUpdate($slideFileId, $batchUpdateRequest);
    $response = $driveService->files->export(
        $slideFileId,
        'application/pdf',
        [
            'alt' => 'media',
        ]
    );

    $content = $response->getBody()->getContents();
    $driveService->files->delete($slideFileId);
    // Download
    $response = new Response();
    $response->headers->set('Content-Type', 'application/pdf');
    $response->headers->set('Content-Disposition', 'attachment; filename="' . $substitutions['slide_filename'] . '"');
    $response->setContent($content);

    return $response;
}

This function replaces the text I want that are in the Google Slide and downloads the slide as a PDF file without an issue.

But, now I want to add a chart to the slide and change the chart values dynamically. I tried to accomplish this by changing the values in source Google Sheet to replaceable text but It throws me this error.

{ "error": { "code": 400, "message": "Invalid requests[9].createSheetsChart: The specified chart could not be found in the spreadsheet.", "errors": [ { "message": "Invalid requests[9].createSheetsChart: The specified chart could not be found in the spreadsheet.", "domain": "global", "reason": "badRequest" } ], "status": "INVALID_ARGUMENT" } }

Any idea how to fix this or any workarounds?

Answer 1

Finally, after many many searches I found the solution.

First you have to update the Google Sheet values that sources the graph from a separate request prior to doing anything with the presentation/Google Slide.

Following is my function to update the Google Sheet values.

/**
 * @param $sheetId - Example: loi973hhbshbh3gf3f765f
 * @param $valueNrange  - Example: [['range' => 'A1', 'value' => '20']]
 * @param $valueInputOption - https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption
 */
public function updateGoogleSheetValues ($sheetId, $valueNrange, $valueInputOption = "RAW") {

    try {
        $client = $this->getGoogleClient();
        $driveService = new \Google_Service_Drive($client);
        $sheetsService = new \Google_Service_Sheets($client);
        $data = [];

        foreach ($valueNrange as $dataItem) {
            $data[] = new \Google_Service_Sheets_ValueRange([
                'range' => $dataItem['range'],
                'values' => [[$dataItem['value']]]
            ]);
        }

        $dataBody = new \Google_Service_Sheets_BatchUpdateValuesRequest([
            'valueInputOption' => $valueInputOption,
            'data' => $data
        ]);

        $result = $sheetsService->spreadsheets_values->batchUpdate($sheetId, $dataBody);
    }
    catch (\Exception $exception) {
        return false;
    }

    return true;
}

After the Google Sheet values are updated, you can refresh the graph/chart in the presentation/Google Slide and download.

Following is my function to refresh the chart and download the slide as PDF.

public function getGoogleSlidesPdf($fileId, $substitutions, $elementOptions = [])
{
    $client = $this->getGoogleClient();
    $driveService = new \Google_Service_Drive($client);
    $slidesService = new \Google_Service_Slides($client);
    $copy = new \Google_Service_Drive_DriveFile([
        'name' => $substitutions['student_name'] . ' presentation',
    ]);
    $driveResponse = $driveService->files->copy($fileId, $copy);
    $certificateFileId = $driveResponse->id;
    $requests = [];
    $emu4M = ['magnitude' => 4000000, 'unit' => 'EMU'];
    foreach ($substitutions as $key => $value) {
        $requests[] = new \Google_Service_Slides_Request(
            [
                'replaceAllText' => [
                    'containsText' => [
                        'text' => '{{'.$key.'}}',
                        'matchCase' => true
                    ],
                    'replaceText' => $value
                ]
            ]
        );
    }
    
    $requests[] = new \Google_Service_Slides_Request([
        'refreshSheetsChart' => [
            'objectId' => 'g*********_0_0'
        ]
    ]);

    
    $batchUpdateRequest = new \Google_Service_Slides_BatchUpdatePresentationRequest([
        'requests' => $requests,
    ]);

    $updateResponse = $slidesService->presentations->batchUpdate($certificateFileId, $batchUpdateRequest);

    // uncomment the below line and set a breakpoint to find the "objectId" of chart or object from sheet objects if the chart or object is moved or edited (move front or back)
    // because the "objectId" will change when the chart or element is moved
    //$presentationObjects = $slidesService->presentations->get($certificateFileId);

    $response = $driveService->files->export(
        $certificateFileId,
        'application/pdf',
        [
            'alt' => 'media',
        ]
    );

    $content = $response->getBody()->getContents();
    $driveService->files->delete($certificateFileId);
    // Download
    $response = new Response();
    $response->headers->set('Content-Type', 'application/pdf');
    $response->headers->set('Content-Disposition', 'attachment; filename="' . $substitutions['certification_filename'] . '"');
    $response->setContent($content);

    return $response;
}