phpchartsgoogle-slidesgoogle-slides-api

How to update a chart in Google Slides dynamically?


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?


Solution

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