Google Slides: Page; Refresh All Chart

Refreshes all embedded charts in the specified slide by replacing them with the latest version from Google Sheets.

2019-09-03 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/google-slides-page-refresh-all-chart/

Configs
  • A: Select OAuth2 Config Name (at [OAuth 2.0 Setting]) *
  • B: Set PRESENTATION-ID to Retrieve (44 chars in File URI) * #{EL}
  • C: Set OBJECT-ID of SLIDE (Chars following “id.”) * #{EL}
Script (click to open)

// (c) 2019, Questetra, Inc. (the MIT License)

//// == OAuth2 Setting example ==
// 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:
//  ( from https://console.developers.google.com/ )
// Consumer Secret:
//  ( from https://console.developers.google.com/ )
//  *Redirect URLs: "https://s.questetra.net/oauth2callback"


//////// START "main()" ////////
main();
function main(){ 

//// == Config Retrieving / 工程コンフィグの参照 ==
const oauth2   = configs.get( "conf_OAuth2"  ) + "";
const presentationId = configs.get( "conf_PresentationId") + "";
const objectId = configs.get( "conf_ObjectId") + "";
// 'java.lang.String' (String Obj) to javascript primitive 'string'

engine.log( " AutomatedTask Config: Presentation ID: " + presentationId );
engine.log( " AutomatedTask Config: Object ID: " + objectId );
if( presentationId === "" ){ // check because of el-enabled
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Presentation ID is empty \n" );
}
if( objectId === "" ){ // check because of el-enabled
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Object ID is empty \n" );
}


//// == Data Retrieving / ワークフローデータの参照 ==
// (nothing)


//// == Calculating / 演算 ==
/// obtain OAuth2 Access Token
const token   = httpClient.getOAuth2Token( oauth2 );

/// get IDs of Charts
let apiRequest = httpClient.begin(); // HttpRequestWrapper
apiRequest     = apiRequest.bearer( token );
const apiUri = "https://slides.googleapis.com/v1/presentations/" +
                presentationId + "/pages/" +
                objectId;
engine.log( " AutomatedTask Trying: GET " + apiUri );
const response = apiRequest.get( apiUri );
const responseCode = response.getStatusCode() + "";
engine.log( " AutomatedTask ApiResponse: Status " + responseCode );
if( responseCode !== "200"){
  throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
        responseCode + "\n" + response.getResponseAsString() + "\n" );
}
const responseStr = response.getResponseAsString() + "";
const responseObj = JSON.parse( responseStr );
let   arrChartObjectIds = [];
for( let i = 0; i < responseObj.pageElements.length; i++ ){
  if( responseObj.pageElements[i].sheetsChart != undefined ){
    arrChartObjectIds.push( responseObj.pageElements[i].objectId );
    engine.log( " AutomatedTask Founded Chart: ObjectID " + 
                      responseObj.pageElements[i].objectId );
  }
}
if ( arrChartObjectIds.length === 0 ) {
  throw new Error( "\n AutomatedTask UnexpectedError:" + 
                   " Sheets Chart not found \n" );
}

/// post RefreshSheetsChartRequests
let requestObj2 = {};
    requestObj2.requests = [];
    for( let i = 0; i < arrChartObjectIds.length; i++ ){
      requestObj2.requests[i] = {};
      requestObj2.requests[i].refreshSheetsChart = {};
      requestObj2.requests[i].refreshSheetsChart.objectId = arrChartObjectIds[i];
    }
let apiRequest2 = httpClient.begin(); // HttpRequestWrapper
apiRequest2 = apiRequest2.bearer( token );
apiRequest2 = apiRequest2.body( JSON.stringify( requestObj2 ), "application/json" );
const apiUri2 = "https://slides.googleapis.com/v1/presentations/" +
                presentationId + ":batchUpdate";
engine.log( " AutomatedTask Trying: POST " + apiUri2 );
const response2 = apiRequest2.post( apiUri2 );
const responseCode2 = response2.getStatusCode() + "";
engine.log( " AutomatedTask ApiResponse: Status " + responseCode2 );
if( responseCode2 !== "200"){
  throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
         responseCode2 + "\n" + response2.getResponseAsString() + "\n" );
} // if 200, replies empty ojbect


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

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

Download

Capture

Notes

  1. Presentation ID: https://docs.google.com/presentation/d/1p33hGJFUNYixBmMeaV81nsOVYGUUrZIFyErinFp3CI8/edit#slide=id.g613777c84a_0_0
  2. Slide Object ID: https://docs.google.com/presentation/d/1p33hGJFUNYixBmMeaV81nsOVYGUUrZIFyErinFp3CI8/edit#slide=id.g613777c84a_0_0
  3. Google Slides “Revenue Report Example” (Copy Freely)
  4. Google Sheets “Revenue Quarterly Example” (Copy Freely)

See also

1 thought on “Google Slides: Page; Refresh All Chart”

  1. Pingback: Google Slides: Page; Replace All String – Questetra Support

Leave a Reply

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

%d bloggers like this: