Google Slides: Page, Refresh Charts
Google Slides: Page, Refresh Charts
Refreshes all embedded Google Sheets charts in the specified slide by replacing them with the latest version of the chart from Google Sheets. For example, you can build a system to automatically update the “sales summary” aggregated in Google Sheets.
Configs
  • U: Select HTTP_Authz Setting *
  • A: Set FILE-ID in Drive *#{EL}
  • B: Set Object-ID of Slide Page (Chars following “id.”)#{EL}
Script (click to open)
// GraalJS Script (engine type: 2)

//////// START "main()" /////////////////////////////////////////////////////////////////

main();
function main(){ 

//// == Config Retrieving / 工程コンフィグの参照 ==
const strAuthzSetting     = configs.get      ( "AuthzConfU" );   /// REQUIRED
  engine.log( " AutomatedTask Config: Authz Setting: " + strAuthzSetting );
const strInputfileId      = configs.get      ( "StrConfA1" );    /// REQUIRED
  if( strInputfileId    === "" ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {A1: FileID} is empty \n" );
  }
let   strPageidOriginal   = configs.get      ( "StrConfB1" );    // NotRequired


//// == Data Retrieving / ワークフローデータの参照 ==
// (Nothing. Retrieved via Expression Language in Config Retrieving)


//// == Calculating / 演算 ==

/// Get LastPage ID
/// Google Slides API
/// https://developers.google.com/slides/reference/rest/v1/presentations/get

if( strPageidOriginal === "" ){
  // request0, prepare
  let request0Uri = "https://slides.googleapis.com/v1/presentations/" + strInputfileId;
  let request0    = httpClient.begin(); // HttpRequestWrapper
      request0    = request0.authSetting( strAuthzSetting ); // with "Authorization: Bearer XX"
      // https://questetra.zendesk.com/hc/en-us/articles/360024574471-R2300#HttpRequestWrapper
  // request0, try
  const response0 = request0.get( request0Uri ); // HttpResponseWrapper
  engine.log( " AutomatedTask ApiRequest0 Start: " + request0Uri );
  const response0Code = response0.getStatusCode() + "";
  const response0Body = response0.getResponseAsString() + "";
  engine.log( " AutomatedTask ApiResponse Status: " + response0Code );
  if( response0Code !== "200"){
    throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
                      response0Code + "\n" + response0Body + "\n" );
  }
  // response0, parse
  const response0Obj = JSON.parse( response0Body );
  strPageidOriginal = response0Obj.slides[ response0Obj.slides.length - 1 ].objectId;
}

/// Get IDs of Charts
/// Google Slides API
/// https://developers.google.com/slides/reference/rest/v1/presentations.pages/get
let request1Uri = "https://slides.googleapis.com/v1/presentations/" +
                  strInputfileId + "/pages/" + strPageidOriginal;
let request1    = httpClient.begin(); // HttpRequestWrapper
    request1    = request1.authSetting( strAuthzSetting ); // with "Authorization: Bearer XX"
    // https://questetra.zendesk.com/hc/en-us/articles/360024574471-R2300#HttpRequestWrapper
// request1, try
const response1     = request1.get( request1Uri ); // HttpResponseWrapper
engine.log( " AutomatedTask ApiRequest1 Start: " + request1Uri );
const response1Code = response1.getStatusCode() + "";
const response1Body = response1.getResponseAsString() + "";
engine.log( " AutomatedTask ApiResponse Status: " + response1Code );
if( response1Code !== "200"){
  throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
                    response1Code + "\n" + response1Body + "\n" );
}
// response1, parse
/* 
engine.log( response1Body ); // debug
{
  "objectId": "SLIDES_API66619114_0",
  "pageElements": [
    {
      "objectId": "SLIDES_API66619114_1",
      "size": { ## },
      "transform": { ## },
      "title": "Revenue",
      "sheetsChart": { ## }
    },
    {
      "objectId": "SLIDES_API66619114_2",
      "size": { ## },
      "transform": { ## },
      "sheetsChart": { ## }
    },
    {
      "objectId": "SLIDES_API66619114_3",
      "size": { ## },
      "transform": { ## },
      "image": { ## }
    },
    {
      "objectId": "SLIDES_API66619114_4",
      "size": { ## },
      "shape": { ## }
    }
  ],
  "slideProperties": { ## },
  "revisionId": "opJuZBJWPCFPRw",
  "pageProperties": { ## }
}
*/
const response1Obj = JSON.parse( response1Body );
let   arrChartids = [];
for( let i = 0; i < response1Obj.pageElements.length; i++ ){
  if( response1Obj.pageElements[i].hasOwnProperty('sheetsChart') ){
    arrChartids.push( response1Obj.pageElements[i].objectId );
    engine.log( " AutomatedTask Founded Chart: ObjectID " + 
                      response1Obj.pageElements[i].objectId );
  }
}
if ( arrChartids.length === 0 ) {
  throw new Error( "\n AutomatedTask UnexpectedError:" + 
                   " Sheets Chart not found \n" );
}

/// Refresh all Charts (via batchUpdate Request)
/// Google Slides API
/// https://developers.google.com/slides/reference/rest/v1/presentations/request#refreshsheetschartrequest
// request2, prepare
let request2Uri = "https://slides.googleapis.com/v1/presentations/" +
                  strInputfileId + ":batchUpdate";
let request2Obj = {};
    request2Obj.requests = [];
    for( let i = 0; i < arrChartids.length; i++ ){
      request2Obj.requests[i] = {};
      request2Obj.requests[i].refreshSheetsChart = {};
      request2Obj.requests[i].refreshSheetsChart.objectId = arrChartids[i];
    }
let request2    = httpClient.begin(); // HttpRequestWrapper
    request2    = request2.authSetting( strAuthzSetting ); // with "Authorization: Bearer XX"
    // https://questetra.zendesk.com/hc/en-us/articles/360024574471-R2300#HttpRequestWrapper
    request2    = request2.body( JSON.stringify( request2Obj ), "application/json" );
// request2, try
const response2     = request2.post( request2Uri ); // HttpResponseWrapper
engine.log( " AutomatedTask ApiRequest2 Start: " + request2Uri );
const response2Code = response2.getStatusCode() + "";
const response2Body = response2.getResponseAsString() + "";
engine.log( " AutomatedTask ApiResponse Status: " + response2Code );
if( response2Code !== "200"){
  throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
                    response2Code + "\n" + response2Body + "\n" );
}
// response2, parse
/* 
{
  "presentationId": "1MsdTWR_pN4FQTCCqUhC1F_JWWsagogISvPF9WqOmaq8",
  "replies": [
    {}
  ],
  "writeControl": {
    "requiredRevisionId": "bvHyj2SJ0wwg4A"
  }
}
*/
// (nothing)


//// == Data Updating / ワークフローデータへの代入 ==
// (nothing)


} //////// END "main()" /////////////////////////////////////////////////////////////////


/*
Notes:
- All updates are executed for the Google Sheets charts in the specified page. GoogleSlidesRefresh
    - If no page is specified, all updates will be executed for the last page.
    - No errors or warnings are recorded even if each chart is already up to date.
- For example, if you want to automate the notification of "Monthly Sales Report", define as follows:
    - The template page is automatically duplicated (Google Slides: Page, Duplicate)
    - The report text part is automatically replaced (Google Slides: Page, Replace Strings)
    - The embedded graph is automatically updated (Google Slides: Page, Refresh Charts)
    - The slide page is automatically converted to a PNG image (Google Slides: Page, Generate PNG)
    - The email with the PNG image attached will be sent automatically. (ThrowEmail Event)
- Add reference permission for Google Spreadsheet to the Scope of HTTP Authen Setting. (See Appendix)
    - Only "presentations" will result in a "PERMISSION_DENIED" error.
    - Include one of the spreadsheets.readonly, spreadsheets, drive.readonly, drive, or drive.file scopes.
    - API communication: presentations.batchUpdate > refreshSheetsChart request
    - https://developers.google.com/slides/reference/rest/v1/presentations/batchUpdate#authorization-scopes

Notes-ja:
- 指定ページ内のGoogleSheetsグラフに対して、全更新の処理が実行されます。 GoogleSlidesRefresh
    - もしページ指定がない場合は、最終ページに対して全更新の処理が実行されます。
    - 既に各グラフが最新版の状態になっている場合も、エラーやワーニングは記録されません。
- たとえば「月次売上レポート」の通知業務を自動化したい場合は、以下のような業務プロセスを定義します。
    - 雛形ページが自動複製され、(※Google スライド: ページ, 複製)
    - 貼り付けられている埋め込みグラフが自動更新され、(※Google スライド: ページ, 文字列全置換)
    - 報告テキスト部が当月文に自動置換され、(※Google スライド: ページ, 全グラフ更新)
    - 当該スライドページがPNG画像に自動変換され、(※Google スライド: ページ, PNG画像生成)
    - PNG画像が添付されたメールが自動的に送信される。(※ ThrowEmail イベント)
- HTTP認証設定の認可スコープに「Googleスプレッドシートの参照権限」を追加しておく必要があります。(Appendix参照)
    - "presentations" だけでは "PERMISSION_DENIED" エラーになります。
    - 次のいずれかを加えます。 spreadsheets.readonly, spreadsheets, drive.readonly, drive, drive.file
    - API通信の内容: presentations.batchUpdate > refreshSheetsChart リクエスト
    - https://developers.google.com/slides/reference/rest/v1/presentations/batchUpdate#authorization-scopes

APPENDIX-en
- FILE-ID/PresentationID in Drive (Chars following "/d/")
    - docs.google.com/presentation/d/1p33hGJFUNYixBmMeaV81nsOVYGUUrZIFyErinFp3CI8
- Object-ID of Slide Page (Chars following "id.")
    - docs.google.com/presentation/d/1p33hGJFUNYixBmMeaV81nsOVYGUUrZIFyErinFp3CI8/edit#slide=id.g613777c84a_0_0
- Setting example of "HTTP Authentication" (OAuth2)
    - Authorization Endpoint URL:
        - https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=force
    - Token Endpoint URL:
        - https://accounts.google.com/o/oauth2/token
    - Scope:
        - https://www.googleapis.com/auth/presentations https://www.googleapis.com/auth/spreadsheets.readonly
    - Client ID, Consumer Secret:
        - ( from https://console.developers.google.com/ )
        - Redirect URLs: https://s.questetra.net/oauth2callback
APPENDIX-ja
- FILE-ID/PresentationID in Drive ("/d/" 以降の文字列)
    - docs.google.com/presentation/d/1p33hGJFUNYixBmMeaV81nsOVYGUUrZIFyErinFp3CI8
- Object-ID of Slide Page ("id." 以降の文字列)
    - docs.google.com/presentation/d/1p33hGJFUNYixBmMeaV81nsOVYGUUrZIFyErinFp3CI8/edit#slide=id.g613777c84a_0_0
- "HTTP認証"(OAuth2)の設定例
    - Authorization Endpoint URL:
        - https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=force
    - Token Endpoint URL:
        - https://accounts.google.com/o/oauth2/token
    - Scope:
        - https://www.googleapis.com/auth/presentations https://www.googleapis.com/auth/spreadsheets.readonly
    - Client ID, Consumer Secret:
        - ( from https://console.developers.google.com/ )
        - Redirect URLs: https://s.questetra.net/oauth2callback
*/

Download

2021-05-17 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/google-slides-page-refresh-charts-2021/
The Addon-import feature is available with Professional edition.

Notes

  • All updates are executed for the Google Sheets charts in the specified page. GoogleSlidesRefresh
    • If no page is specified, all updates will be executed for the last page.
    • No errors or warnings are recorded even if each chart is already up to date.
  • For example, if you want to automate the notification of “Monthly Sales Report”, define as follows:
    • The template page is automatically duplicated (Google Slides: Page, Duplicate)
    • The report text part is automatically replaced (Google Slides: Page, Replace Strings)
    • The embedded graph is automatically updated (Google Slides: Page, Refresh Charts)
    • The slide page is automatically converted to a PNG image (Google Slides: Page, Generate PNG)
    • The email with the PNG image attached will be sent automatically. (ThrowEmail Event)
  • Add reference permission for Google Spreadsheet to the Scope of HTTP Authen Setting. (See Appendix)

Capture

Refreshes all embedded Google Sheets charts in the specified slide by replacing them with the latest version of the chart from Google Sheets. For example, you can build a system to automatically update the "sales summary" aggregated in Google Sheets.
Refreshes all embedded Google Sheets charts in the specified slide by replacing them with the latest version of the chart from Google Sheets. For example, you can build a system to automatically update the "sales summary" aggregated in Google Sheets.

Appendix

See also

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: