Google Slides: Page; Refresh All Chart

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/en/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

Discover more from Questetra Support

Subscribe now to keep reading and get access to the full archive.

Continue reading