Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
BQ GAS sample
/*
* dependencies:
* Momement: 'MHMchiX6c1bwSqGM1PZiW_PxhMjh3Sh48'
*/
/**
* @see https://developers.google.com/apps-script/reference/mail/mail-app
*/
function sendMail(recipients, subject, doc) {
var attachment = doc.getAs('application/pdf');
var body = doc.getUrl();
MailApp.sendEmail(recipients, subject, body, {
noReply: true,
attachments: attachment
});
}
/**
* @see https://developers.google.com/bigquery/docs/reference/v2/
* @see https://developers.google.com/apps-script/advanced/bigquery
*/
function runQuery(projectId, query) {
var request = {
query: query + ';'
};
var queryResults = BigQuery.Jobs.query(request, projectId);
var jobId = queryResults.jobReference.jobId;
var sleepTime = 500;
while (!queryResults.jobComplete) {
Utilities.sleep(sleepTime);
sleepTime *= 2;
queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
}
return queryResults;
}
function saveQueryResults(queryResults, sheet) {
// Append the headers.
var headers = queryResults.schema.fields.map(function(field) {
return field.name;
});
sheet.appendRow(headers);
// 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);
}
// Write data to sheet
var data = new Array(rows.length);
for (var i = 0; i < rows.length; i++) {
var cols = rows[i].f;
data[i] = new Array(cols.length);
for (var j = 0; j < cols.length; j++) {
data[i][j] = cols[j].v;
}
}
sheet.getRange(2, 1, rows.length, headers.length).setValues(data);
}
function dailyReport(projectId, query, recipients, subject, workDir, filename) {
var queryResults = runQuery(projectId, query);
if (!queryResults.rows) {
return MailApp.sendEmail(recipients, 'ERROR: Failed to run query', query, { noReply: true });
}
var spreadsheet = SpreadsheetApp.create(filename);
var sheet = spreadsheet.getActiveSheet();
var doc = DocsList.getFileById(spreadsheet.getId());
var parents = doc.getParents();
// Move to working directory
parents.forEach(function (parent) {
doc.removeFromFolder(parent);
})
doc.addToFolder(workDir);
saveQueryResults(queryResults, sheet);
if (recipients && recipients != '') {
sendMail(recipients, subject, doc);
}
}
function createDailyReports() {
var configDocId = 'xxx';
var configDoc = SpreadsheetApp.openById(configDocId);
var configSheet = configDoc.getSheetByName('config');
var IDX_ENABLED = 0;
var IDX_PROJECT_ID = 1;
var IDX_QUERY = 2;
var IDX_SUBJECT = 3;
var IDX_RECIPIENTS = 4;
var IDX_FILENAME = 5;
var workDir = DocsList.getFolder('BQ GAS/firststep/reports');
var moment = Moment.moment();
var data = configSheet.getDataRange().getValues();
var query, projectId, recipients, subject, filename;
for (var i = 1; i < data.length; ++i) {
var row = data[i];
if (row[IDX_ENABLED] === 'T') {
try {
projectId = row[IDX_PROJECT_ID];
query = row[IDX_QUERY];
subject = row[IDX_SUBJECT];
recipients = row[IDX_RECIPIENTS];
filename = row[IDX_FILENAME] + '_' + moment.format('YYYYMMDD');
dailyReport(projectId, query, recipients, subject, workDir, filename);
} catch (e) {
if (recipients) {
MailApp.sendEmail(recipients, 'ERROR: Failed to create report', e.message, { noReply: true });
}
Logger.log(e);
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment