Example of using Apps Script to populate Google Slides with data from BigQuery

code.gs

//// Using BigQuery query results to populate a template slide deck ////

// This object defines the queries associate with each of the params used in the template
// Note that you may want to use functions defined within BigQuery instead of parameterized queries 
var queryObj = {
  "new_cust": "SELECT COUNT(*) FROM `looker-private-demo.retail.customers` as custs " +
              "JOIN `looker-private-demo.retail.transaction_detail` as trans ON custs.id = trans.customer_id " +
              ", UNNEST(trans.line_items) as line_items " +
              "WHERE TIMESTAMP_TRUNC(custs.created_at,WEEK) = @week " + 
              "AND line_items.product_id in (select id from `looker-private-demo.retail.products` where category = @team);",
  "rep_rate": "WITH repeat_orders as " +
              "(SELECT trans.transaction_id as order_id, trans.transaction_timestamp as order_date, MIN(repeat_trans.transaction_timestamp) AS next_order_date " +
              "FROM `looker-private-demo.retail.transaction_detail` as trans, UNNEST(trans.line_items) as line_items " +
              "LEFT JOIN `looker-private-demo.retail.transaction_detail` repeat_trans ON trans.customer_id = repeat_trans.customer_id " +
              "AND trans.transaction_timestamp < repeat_trans.transaction_timestamp WHERE " +
              "TIMESTAMP_TRUNC( trans.transaction_timestamp,WEEK) = @week AND line_items.product_id in " +
              "(select id from `looker-private-demo.retail.products` where category = @team) GROUP BY 1,2)" +
              "SELECT COUNT(DISTINCT CASE WHEN TIMESTAMP_DIFF(order_date, next_order_date, DAY) <= 30  THEN order_id  ELSE NULL END) / NULLIF(COUNT(*),0) " + 
              "AS repeat_purchase_rate FROM repeat_orders;",
  "sales": "SELECT SUM(line_items.sale_price) as total_sales " +
            "FROM `looker-private-demo.retail.transaction_detail` as trans, UNNEST(trans.line_items) as line_items " +
            "WHERE TIMESTAMP_TRUNC( trans.transaction_timestamp,WEEK) = @week AND line_items.product_id in " +
            "(select id from `looker-private-demo.retail.products` where category = @team);",
  "orders": "SELECT COUNT(DISTINCT trans.transaction_id) as total_sales " +
            "FROM `looker-private-demo.retail.transaction_detail` as trans, UNNEST(trans.line_items) as line_items " +
            "WHERE TIMESTAMP_TRUNC(trans.transaction_timestamp,WEEK) = @week AND line_items.product_id in " +
            "(select id from `looker-private-demo.retail.products` where category = @team);"
}

// Get the sunday for the previous complete week to use as the week filter
var curr = new Date();
var week = Utilities.formatDate(new Date(curr.setDate(curr.getDate() - curr.getDay() - 7)), "UTC", "YYYY-MM-dd");

// This function makes a copy of the template slide deck and returns the URL for the new deck
function makeCopy(){
  // The Id of the presentation to copy
  var templateId = "";

  // Copy the file into the specified folder
  var copyFile = {
    title: "All Hands - Week of " + week,
    parents: [{id: ''}]
  };
  copyFile = Drive.Files.copy(copyFile, templateId);
  var copyID = copyFile.id;

  Logger.log("Created Copy - "+ copyID)
  return copyID
}

// This function runs the specified query with given inputs
function runQuery(param, teamName) {
  var projectId = '';

  // If no param is provided, then the user is looking to get all the distinct teams
  if(param == 'teams'){
    query = "SELECT DISTINCT category FROM `looker-private-demo.retail.products`"
  }
  else{
    query = queryObj[param]
  }


  var request = {
    query: query,
    queryParameters: [{name: 'team', parameterValue: {value: teamName}, parameterType: {type: 'STRING'}},
      {name: 'week', parameterValue: {value: week}, parameterType: {type: 'STRING'}},],
    useLegacySql: false
  };

  // If just looking for team names then you don't need the params
  if(param == 'teams'){
    request = {query:query, useLegacySql: false}
  };

  var queryResults = BigQuery.Jobs.query(request, projectId);
  var jobId = queryResults.jobReference.jobId;

  // Check on status of the Query Job
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
  }

  // Get all the rows of results
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  // Unnest results from object
  var results = []
  rows.forEach((val) => {val.f.forEach((n_val) => {results.push(n_val.v)})})

  // Return the results
  return results
}

// This function uses the results of the query to populate the slide deck
function populateSlide(teamName, slideID, presentationID){
  // Create the text merge (replaceAllText) requests for the slide based on the query object defined at the top of the script
  requests = [{
    replaceAllText: {
      pageObjectIds: [slideID],
      containsText: {
        text: '{{team_name}}',
        matchCase: true
      },
      replaceText: teamName
    },
  },{
    replaceAllText: {
      containsText: {
        text: '{{week}}',
        matchCase: true
      },
      replaceText: week
    },
  }];
  for(var param in queryObj){
    var val = runQuery(param, teamName)
    requests.push({
    replaceAllText: {
      pageObjectIds: [slideID],
      containsText: {
        text: '{{' + param + '}}',
        matchCase: true
      },
      replaceText: val[0]
    }
  })
  }

  // Execute the requests for this presentation
  var result = Slides.Presentations.batchUpdate({requests: requests}, presentationID);
}

// This function runs through each team, makes a copy of the slide, and runs each helper function to run the queries and fill in the variables on the slide
function runScript(){
  // First make a copy of the whole deck and get the ID for the new slide deck
  var presentationID = makeCopy();
  var slides = Slides.Presentations.get(presentationID).slides;

  // Loop through each team name and make a copy of the slide 
  var teamNames = runQuery(param="teams", teamName="NA");
  teamNames.forEach((team) => {
    // Make a copy of the second slide
    var requests = [{duplicateObject: {objectId: slides[1].objectId}}]; 
    var resp = Slides.Presentations.batchUpdate({'requests': requests}, presentationID);
    
    // Populate the slide
    populateSlide(team, resp.replies[0].duplicateObject.objectId, presentationID)
  })

  // Remove the second slide
  var requests = [{deleteObject: {objectId: slides[1].objectId}}]; 
  var resp = Slides.Presentations.batchUpdate({'requests': requests}, presentationID);
}

Adding script example