-
-
Save Webmasterei/e1c871e5d5a9bcdc29468bc7ad019cd8 to your computer and use it in GitHub Desktop.
This file contains hidden or 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
| 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