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?

About 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., can you provide your current script? — Tanaike
– Tanaike, Commented Jun 14, 2024 at 6:48
@Tanaike - I updated the code and and error. I am referring this page developers.google.com/slides/api/guides/add-chart#php — Dula
– Dula, Commented Jun 14, 2024 at 7:15
Thank you for replying. From your updated script, I understood that you wanted to insert a new chart from Spreadsheet to Slide. Here, can I ask you about the details of the relationship between your updated script and change the chart values dynamically? By the way, I think that the error of The specified chart could not be found in the spreadsheet." is due to the chart ID is not found in the Spreadsheet. So, I cannot still understand your expected result. Can I ask you about the details of it? — Tanaike
– Tanaike, Commented Jun 14, 2024 at 7:39
For example, do you want to simply refresh the chart inserted from Spreadsheet on Google Slide by Google Slides API? I cannot understand dynamically you expect. I deeply apologize for my poor English skill. — Tanaike
– Tanaike, Commented Jun 14, 2024 at 7:44
Students of the site should be able to download the report. So the report text as well as the chart data should be changed depending on the student downloading the report. In my Google slides, I have set the replaceable text like {{this_is_replace_text_1}}. Also I added a chart so I can change the data dynamically, but it doesn't work. — Dula
– Dula, Commented Jun 15, 2024 at 23:55

Dula · Accepted Answer · 2024-06-20 12:59:24Z

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;
}

Collectives™ on Stack Overflow

How to update a chart in Google Slides dynamically?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related