For the implementation of this solution, you need:<\/p>\n\n
To supplement data in Google Merchant Center, there’s the option of “supplemental feeds.” These trigger product updates based on an item number and other attributes. You can create such a feed using Google Spreadsheets, BigQuery, and an AppScript that retrieves the data from BigQuery and writes it to a spreadsheet. <\/p>\n\n
First, you need to determine the latest products in BigQuery and create a table with an output in the format offer_id, customLabel0.<\/p>\n\n
We achieve this in BigQuery with the following query:<\/p>\n\n
SELECT\n offerId AS id,\n 'New' AS custom_label_0\nFROM (\n SELECT\n *\n FROM\n `mnyproject.gmc_data.SHOPPING_CONTENT`\n WHERE\n DATE(_PARTITIONTIME) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))\nWHERE\n creationDate > DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)\nORDER BY\n creationDate<\/pre>\n\nThis query could now be executed directly in the apps script. However, to make the code more maintainable, it’s better to save the query directly in BigQuery as a view. <\/p>\n\n
To specify the date range when products are to be classified as new, you must change the 30 in line 12 to any value.<\/p>\n\n
Output new products in Google Spreadsheet<\/h2>\n\n
To make the products available in Google Spreadsheets we use a Google Apps Script<\/a> .<\/p>\n\n
The script executes a query based on the query just defined, formats the data for spreadsheets, and writes it to the spreadsheet. The script is a modified version of this Google Script<\/a> . <\/p>\n\n
Before executing, you should correctly fill in the variables in lines 12-17. In the created spreadsheet, the sheet name must be as shown in line 17. <\/p>\n\n
function doPost(e){\n runQuery()\n return HtmlService.createHtmlOutput(\"script complete\");\n}\n\n\/**\n * This Script collects all Products in Merchant Center that have been created within the lookback window.\n *\/\nfunction runQuery() {\n \/\/ Replace this value with the project ID listed in the Google\n \/\/ Cloud Platform project.\n var projectId = 'MYPROJECT';\n var dataset = 'gmc_data';\n var tableid = 'SHOPPING_CONTENT';\n var tablepath = projectId + '.' + dataset + '.' + tableid;\n var spreadsheet = 'MYSHEETURL';\n var sheetname = 'New Products';\n var labelName = 'New ';\n var lookback = 60\n\n var request = {\n 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',\n useLegacySql: false\n };\n var queryResults = BigQuery.Jobs.query(request, projectId);\n var jobId = queryResults.jobReference.jobId;\n\n \/\/ Check on status of the query job.\n var sleepTimeMs = 500;\n while (!queryResults.jobComplete) {\n Utilities.sleep(sleepTimeMs);\n sleepTimeMs *= 2;\n queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);\n }\n\n \/\/ Get all the rows of results.\n var rows = queryResults.rows;\n while (queryResults.pageToken) {\n queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {\n pageToken: queryResults.pageToken\n });\n rows = rows.concat(queryResults.rows);\n }\n\n if (rows) {\n var spreadsheet = SpreadsheetApp.openByUrl(spreadsheet);\n var sheet = spreadsheet.getSheetByName(sheetname);\n sheet.clear();\n \/\/ Append the headers.\n var headers = queryResults.schema.fields.map(function(field) {\n return field.name;\n });\n sheet.appendRow(headers);\n\n \/\/ Append the results.\n var data = new Array(rows.length);\n for (var i = 0; i < rows.length; i++) {\n var cols = rows[i].f;\n data[i] = new Array(cols.length);\n for (var j = 0; j < cols.length; j++) {\n data[i][j] = cols[j].v;\n }\n }\n sheet.getRange(2, 1, rows.length, headers.length).setValues(data);\n\n Logger.log('Results spreadsheet created: %s',\n spreadsheet.getUrl());\n } else {\n Logger.log('No rows returned.');\n }\n}<\/pre>\n\nNew Labels in Spreadsheet.js<\/a> hosted with \u2764 by GitHub<\/a><\/p>\n\n
Add spreadsheet as an additional feed in the Merchant Center<\/h2>\n\n
\n
- Open the customer’s Merchant Center that should receive the supplemental feed.<\/li>\n\n\n\n
- Navigate to Products > Feeds<\/li>\n\n\n\n
- Select the \u201cAdd subfeed\u201d button at the bottom of the screen.<\/li>\n\n\n\n
- Follow the subsequent steps and select the corresponding spreadsheet.<\/li>\n\n\n\n
- It’s important to set which main feed these changes should affect.<\/li>\n<\/ol>\n\n
Problem: Triggering the sequence time-efficiently<\/h2>\n\n
One problem with this solution is the different times at which data is created:<\/p>\n\n