Created
August 18, 2021 02:28
-
-
Save leighajarett/376d9abd27cde7f004173ed5d34d2808 to your computer and use it in GitHub Desktop.
sample_bq_slide_appscript
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
//// 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 | |
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 = "1sKCi5Ss_cXJIXo8waWu23rg8cziv_CYjy8mcnjT_QUM"; | |
// Copy the file into the specified folder | |
var copyFile = { | |
title: "All Hands - Week of " + week, | |
parents: [{id: '14N9s7ASFiA5DSPJyJJJjGTWGiwg_Ofi2'}] | |
}; | |
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 = 'leigha-bq-dev'; | |
// 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); | |
} | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment