Skip to content

Instantly share code, notes, and snippets.

@Webmasterei
Last active December 9, 2021 17:16
Show Gist options
  • Select an option

  • Save Webmasterei/e1c871e5d5a9bcdc29468bc7ad019cd8 to your computer and use it in GitHub Desktop.

Select an option

Save Webmasterei/e1c871e5d5a9bcdc29468bc7ad019cd8 to your computer and use it in GitHub Desktop.
function doPost(e){
runQuery()
return HtmlService.createHtmlOutput("script complete");
}
/**
* This Script collects all Products in Merchant Center that have been created within the lookback window.
*/
function runQuery() {
// Replace this value with the project ID listed in the Google
// Cloud Platform project.
var projectId = 'MYPROJECT';
var dataset = 'gmc_data';
var tableid = 'SHOPPING_CONTENT';
var tablepath = projectId + '.' + dataset + '.' + tableid;
var spreadsheet = 'MYSHEETURL';
var sheetname = 'Neue Produkte';
var labelName = 'Neu ';
var lookback = 60
var request = {
query: 'SELECT offerId as id, "' + labelName + '" as custom_label_2 FROM (SELECT * FROM '+tablepath+' WHERE DATE(_PARTITIONTIME) = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)) WHERE creationDate > DATE_ADD(CURRENT_DATE(), INTERVAL -'+lookback+' DAY) ORDER BY creationDate',
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);
}
if (rows) {
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheet);
var sheet = spreadsheet.getSheetByName(sheetname);
sheet.clear();
// Append the headers.
var headers = queryResults.schema.fields.map(function(field) {
return field.name;
});
sheet.appendRow(headers);
// Append the results.
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);
Logger.log('Results spreadsheet created: %s',
spreadsheet.getUrl());
} else {
Logger.log('No rows returned.');
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment