In BigQuery, automatic data transfer is available for Google Merchant Center data. Unfortunately, this service has the problem that this connector has not been adapted for the Conent API for Shopping v2.1. Thus, the dataset generated there is incomplete and usable only to a limited extent.
This article shows how to replace the import function with a Google Apps script, which then transfers the data to BigQuery on a daily basis.
The script
The script is installed and configured as a Google Ads script, so anyone with a Google Ads account can use this solution. The script is based on this export Google Ads Reports into BigQuery – Single Account .
Installation in a nutshell
- Open Google Ads account
- Navigate to ” Tools> Scripts> + “ to create a new script.
- Name the script e.g. “Shopping API Transfer to BigQuery”
- Copy the script
- Set the variable “TRUNCATE_EXISTING_TABLES = true” so that the table structure is created on the first run.
- Open “Advanced APIs” in the top right corner and activate “BigQuery” and “Shopping Content”.
- Create dataset in BigQuery (important to define the location and autom. expiration time of the data)
- In the script, fill the fields variable MERCHANT_CENTER_ID, BIGQUERY_PROJECT_ID, BIGQUERY_DATASET_ID, BIGQUERY_TABLE_NAME with your own values.
- Press “Preview” at the bottom right
- Use the yellow button to authorize. (The logged in Google account needs access to BigQuery as well as the Merchant Center)
- Run “Preview” again so that the script runs for the first time.
- Correct the dataset in BigQuery.
- After the first run, TRUNCATE_EXISTING_TABLES should be set to false, otherwise past data will be overwritten.
- In the script overview, define the time of the script call and set it to “daily”.
var CONFIG = {
MERCHANT_CENTER_ID: 'GMCID',
BIGQUERY_PROJECT_ID: 'BQPID',
BIGQUERY_DATASET_ID: 'BQDSID',
LOCATION: 'eu',
CHUNK_SIZE: 250, // 250 is max size for Content API
// Truncate existing data, otherwise will append.
TRUNCATE_EXISTING_DATASET: false,
TRUNCATE_EXISTING_TABLES: false,
// Lists of reports and fields to retrieve from Google Ads.
REPORTS: {PRODUCTS:{
NAME: 'PRODUCTS',
CONDITIONS: '',
FIELDS: [
{"name":"id", "type":"STRING", "mode":"NULLABLE"},
{"name":"offerId", "type":"STRING", "mode":"NULLABLE"},
{"name":"source", "type":"STRING", "mode":"NULLABLE"},
{"name":"title", "type":"STRING", "mode":"NULLABLE"},
{"name":"description", "type":"STRING", "mode":"NULLABLE"},
{"name":"link", "type":"STRING", "mode":"NULLABLE"},
{"name":"imageLink", "type":"STRING", "mode":"NULLABLE"},
{"name":"contentLanguage", "type":"STRING", "mode":"NULLABLE"},
{"name":"targetCountry", "type":"STRING", "mode":"NULLABLE"},
{"name":"feedLabel", "type":"STRING", "mode":"NULLABLE"},
{"name":"channel", "type":"STRING", "mode":"NULLABLE"},
{"name":"brand", "type":"STRING", "mode":"NULLABLE"},
{"name":"gtin", "type":"FLOAT", "mode":"NULLABLE"},
{"name":"customLabel0", "type":"STRING", "mode":"NULLABLE"},
{"name":"customLabel1", "type":"STRING", "mode":"NULLABLE"},
{"name":"customLabel2", "type":"STRING", "mode":"NULLABLE"},
{"name":"customLabel3", "type":"STRING", "mode":"NULLABLE"},
{"name":"customLabel4", "type":"STRING", "mode":"NULLABLE"},
{"name":"price", "type":"RECORD", "mode":"NULLABLE", "fields":[
{"name":"value", "type":"FLOAT", "mode":"NULLABLE"},
{"name":"currency", "type":"STRING", "mode":"NULLABLE"}
]
},
{"name":"salePrice", "type":"RECORD", "mode":"NULLABLE", "fields":[
{"name":"value", "type":"FLOAT", "mode":"NULLABLE"},
{"name":"currency", "type":"STRING", "mode":"NULLABLE"}
]
},
{"name":"additionalImageLinks", "type":"STRING", "mode":"REPEATED"},
{"name":"expirationDate", "type":"STRING", "mode":"NULLABLE"},
{"name":"adult", "type":"BOOLEAN", "mode":"NULLABLE"},
{"name":"kind", "type":"STRING", "mode":"NULLABLE"},
{"name":"color", "type":"STRING", "mode":"NULLABLE"},
{"name":"googleProductCategory", "type":"STRING", "mode":"NULLABLE"},
{"name":"itemGroupId", "type":"STRING", "mode":"NULLABLE"},
{"name":"mpn", "type":"STRING", "mode":"NULLABLE"},
{"name":"pattern", "type":"STRING", "mode":"NULLABLE"},
{"name":"material", "type":"STRING", "mode":"NULLABLE"},
{"name":"salePriceEffectiveDate", "type":"STRING", "mode":"NULLABLE"},
{"name":"shipping", "type":"RECORD", "mode":"REPEATED", "fields":[
{"name":"price", "type":"RECORD", "mode":"NULLABLE", "fields":
[
{"name":"value", "type":"FLOAT", "mode":"NULLABLE"},
{"name":"currency", "type":"STRING", "mode":"NULLABLE"}
]
},
{"name":"country", "type":"STRING", "mode":"NULLABLE"},
{"name":"region", "type":"STRING", "mode":"NULLABLE"},
{"name":"service", "type":"STRING", "mode":"NULLABLE"},
{"name":"locationId", "type":"STRING", "mode":"NULLABLE"},
{"name":"locationGroupName", "type":"STRING", "mode":"NULLABLE"},
{"name":"postalCode", "type":"STRING", "mode":"NULLABLE"},
{"name":"minHandlingTime", "type":"STRING", "mode":"NULLABLE"},
{"name":"maxHandlingTime", "type":"STRING", "mode":"NULLABLE"},
{"name":"minTransitTime", "type":"STRING", "mode":"NULLABLE"},
{"name":"maxTransitTime", "type":"STRING", "mode":"NULLABLE"}
]
},
{"name":"shippingWeight", "type":"RECORD", "mode":"NULLABLE", "fields":[
{"name":"value", "type":"FLOAT", "mode":"NULLABLE"},
{"name":"unit", "type":"STRING", "mode":"NULLABLE"}
]
},
{"name":"sizes", "type":"STRING", "mode":"REPEATED"},
{"name":"customAttributes", "type":"RECORD", "mode":"REPEATED", "fields":[
{"name":"name", "type":"STRING", "mode":"NULLABLE"},
{"name":"value", "type":"STRING", "mode":"NULLABLE"},
{"name":"groupValues", "type":"RECORD", "mode":"REPEATED","fields":[
{"name":"name", "type":"STRING", "mode":"NULLABLE"},
{"name":"value", "type":"STRING", "mode":"NULLABLE"},
{"name":"groupValues", "type":"STRING", "mode":"NULLABLE"}
]}
]},
{"name":"identifierExists", "type":"BOOLEAN", "mode":"NULLABLE"},
{"name":"multipack", "type":"STRING", "mode":"NULLABLE"},
{"name":"isBundle", "type":"BOOLEAN", "mode":"NULLABLE"},
{"name":"mobileLink", "type":"STRING", "mode":"NULLABLE"},
{"name":"availabilityDate", "type":"STRING", "mode":"NULLABLE"},
{"name":"shippingLabel", "type":"STRING", "mode":"NULLABLE"},
{"name":"unitPricingMeasure", "type":"RECORD", "mode":"NULLABLE", "fields":[
{"name":"value", "type":"FLOAT", "mode":"NULLABLE"},
{"name":"unit", "type":"STRING", "mode":"NULLABLE"}
]
},
{"name":"unitPricingBaseMeasure", "type":"RECORD", "mode":"NULLABLE", "fields":[
{"name":"value", "type":"FLOAT", "mode":"NULLABLE"},
{"name":"unit", "type":"STRING", "mode":"NULLABLE"}
]
},
{"name":"shippingLength", "type":"RECORD", "mode":"NULLABLE", "fields":[
{"name":"value", "type":"FLOAT", "mode":"NULLABLE"},
{"name":"unit", "type":"STRING", "mode":"NULLABLE"}
]
},
{"name":"shippingWidth", "type":"RECORD", "mode":"NULLABLE", "fields":[
{"name":"value", "type":"FLOAT", "mode":"NULLABLE"},
{"name":"unit", "type":"STRING", "mode":"NULLABLE"}
]
},
{"name":"shippingHeight", "type":"RECORD", "mode":"NULLABLE", "fields":[
{"name":"value", "type":"FLOAT", "mode":"NULLABLE"},
{"name":"unit", "type":"STRING", "mode":"NULLABLE"}
]
},
{"name":"displayAdsId", "type":"STRING", "mode":"NULLABLE"},
{"name":"displayAdsSimilarIds", "type":"STRING", "mode":"REPEATED"},
{"name":"displayAdsTitle", "type":"STRING", "mode":"NULLABLE"},
{"name":"displayAdsLink", "type":"STRING", "mode":"NULLABLE"},
{"name":"sellOnGoogleQuantity", "type":"STRING", "mode":"NULLABLE"},
{"name":"promotionIds", "type":"STRING", "mode":"REPEATED"},
{"name":"maxHandlingTime", "type":"STRING", "mode":"NULLABLE"},
{"name":"minHandlingTime", "type":"STRING", "mode":"NULLABLE"},
{"name":"costOfGoodsSold", "type":"RECORD", "mode":"NULLABLE", "fields":[
{"name":"value", "type":"FLOAT", "mode":"NULLABLE"},
{"name":"currency", "type":"STRING", "mode":"NULLABLE"}
]
},
{"name":"adsGrouping", "type":"STRING", "mode":"NULLABLE"},
{"name":"adsLabels", "type":"STRING", "mode":"NULLABLE"},
{"name":"adsRedirect", "type":"STRING", "mode":"NULLABLE"},
{"name":"productTypes", "type":"STRING", "mode":"REPEATED"},
{"name":"ageGroup", "type":"STRING", "mode":"NULLABLE"},
{"name":"availability", "type":"STRING", "mode":"NULLABLE"},
{"name":"condition", "type":"STRING", "mode":"NULLABLE"},
{"name":"gender", "type":"STRING", "mode":"NULLABLE"},
{"name":"sizeSystem", "type":"STRING", "mode":"NULLABLE"},
{"name":"sizeType", "type":"STRING", "mode":"NULLABLE"},
{"name":"additionalSizeType", "type":"STRING", "mode":"NULLABLE"},
{"name":"destinations", "type":"RECORD", "mode":"REPEATED", "fields":[
{"name":"destinationName", "type":"STRING", "mode":"NULLABLE"},
{"name":"intention", "type":"STRING", "mode":"NULLABLE"}
]
},
{"name":"destinationStatuses", "type":"RECORD", "mode":"REPEATED", "fields":[
{"name":"approvedCountries", "type":"STRING", "mode":"REPEATED"},
{"name":"destination", "type":"STRING", "mode":"NULLABLE"},
{"name":"status", "type":"STRING", "mode":"NULLABLE"},
{"name":"disapprovedCountries", "type":"STRING", "mode":"REPEATED"},
{"name":"pendingCountries", "type":"STRING", "mode":"REPEATED"}
]
},
{"name":"itemLevelIssues", "type":"RECORD", "mode":"REPEATED", "fields":[
{"name":"code", "type":"STRING", "mode":"NULLABLE"},
{"name":"servability", "type":"STRING", "mode":"NULLABLE"},
{"name":"resolution", "type":"STRING", "mode":"NULLABLE"},
{"name":"attributeName", "type":"STRING", "mode":"NULLABLE"},
{"name":"destination", "type":"STRING", "mode":"NULLABLE"},
{"name":"description", "type":"STRING", "mode":"NULLABLE"},
{"name":"detail", "type":"STRING", "mode":"NULLABLE"},
{"name":"documentation", "type":"STRING", "mode":"NULLABLE"},
{"name":"applicableCountries", "type":"STRING", "mode":"REPEATED"}
]
},
{"name":"energyEfficiencyClass", "type":"STRING", "mode":"NULLABLE"},
{"name":"minEnergyEfficiencyClass", "type":"STRING", "mode":"NULLABLE"},
{"name":"maxEnergyEfficiencyClass", "type":"STRING", "mode":"NULLABLE"},
{"name":"taxCategory", "type":"STRING", "mode":"NULLABLE"},
{"name":"transitTimeLabel", "type":"STRING", "mode":"NULLABLE"},
{"name":"shoppingAdsExcludedCountries", "type":"STRING", "mode":"NULLABLE"},
{"name":"pickupMethod", "type":"STRING", "mode":"NULLABLE"},
{"name":"pickupSla", "type":"STRING", "mode":"NULLABLE"},
{"name":"linkTemplate", "type":"STRING", "mode":"NULLABLE"},
{"name":"mobileLinkTemplate", "type":"STRING", "mode":"NULLABLE"},
{"name":"productDetails", "type":"RECORD", "mode":"REPEATED", "fields":[
{"name":"sectionName", "type":"string", "mode":"NULLABLE"},
{"name":"attributeName", "type":"string", "mode":"NULLABLE"},
{"name":"attributeValue", "type":"string", "mode":"NULLABLE"}
]
},
{"name":"productHighlights", "type":"STRING", "mode":"REPEATED"},
{"name":"subscriptionCost", "type":"RECORD", "mode":"REPEATED", "fields":[
{"name":"price", "type":"RECORD", "mode":"NULLABLE", "fields":
[
{"name":"value", "type":"FLOAT", "mode":"NULLABLE"},
{"name":"currency", "type":"STRING", "mode":"NULLABLE"}
]
},
{"name":"period", "type":"STRING", "mode":"NULLABLE"}
]
},
{"name":"canonicalLink", "type":"STRING", "mode":"NULLABLE"},
{"name":"creationDate", "type":"DATE", "mode":"NULLABLE"},
{"name":"lastUpdateDate", "type":"DATE", "mode":"NULLABLE"},
{"name":"googleExpirationDate", "type":"DATE", "mode":"NULLABLE"},
{"name":"includedDestinations", "type":"STRING", "mode":"REPEATED"},
{"name":"excludedDestinations", "type":"STRING", "mode":"REPEATED"},
]
},
PRODUCTS_STATUS:
{
NAME: 'PRODUCTS_STATUS',
CONDITIONS: '',
FIELDS: [
{"name":"productId", "type":"STRING", "mode":"NULLABLE"},
{"name":"title", "type":"STRING", "mode":"NULLABLE"},
{"name":"link", "type":"STRING", "mode":"NULLABLE"},
{"name":"destinationStatuses", "type":"RECORD", "mode":"REPEATED", "fields":[
{"name":"approvedCountries", "type":"STRING", "mode":"REPEATED"},
{"name":"destination", "type":"STRING", "mode":"NULLABLE"},
{"name":"status", "type":"STRING", "mode":"NULLABLE"},
{"name":"disapprovedCountries", "type":"STRING", "mode":"REPEATED"},
{"name":"pendingCountries", "type":"STRING", "mode":"REPEATED"}
]
},
{"name":"kind", "type":"STRING", "mode":"NULLABLE"},
{"name":"creationDate", "type":"STRING", "mode":"NULLABLE"},
{"name":"lastUpdateDate", "type":"STRING", "mode":"NULLABLE"},
{"name":"googleExpirationDate", "type":"STRING", "mode":"NULLABLE"},
{"name":"itemLevelIssues", "type":"RECORD", "mode":"REPEATED", "fields":[
{"name":"code", "type":"STRING", "mode":"NULLABLE"},
{"name":"servability", "type":"STRING", "mode":"NULLABLE"},
{"name":"resolution", "type":"STRING", "mode":"NULLABLE"},
{"name":"attributeName", "type":"STRING", "mode":"NULLABLE"},
{"name":"destination", "type":"STRING", "mode":"NULLABLE"},
{"name":"description", "type":"STRING", "mode":"NULLABLE"},
{"name":"detail", "type":"STRING", "mode":"NULLABLE"},
{"name":"documentation", "type":"STRING", "mode":"NULLABLE"},
{"name":"applicableCountries", "type":"STRING", "mode":"REPEATED"}
]
}
]
}
}
,
RECIPIENT_EMAILS: [
'[email protected]'
]
};
// Impose a limit on the size of BQ inserts: 10MB - 512Kb for overheads.
var MAX_INSERT_SIZE = 10 * 1024 * 1024 - 512 * 1024;
/**
* Main method
*/
function main() {
// CREATE FUNCTIONS
createDataset();
createTable(CONFIG.REPORTS.PRODUCTS);
// Get Data and Ship it to BQ
productList(CONFIG.REPORTS.PRODUCTS)
product_status(CONFIG.REPORTS.PRODUCTS_STATUS)
}
/**
* Lists the products for a given merchant.
*/
function productList(reportConfig) {
var merchantId = CONFIG.MERCHANT_CENTER_ID; // Replace this with your Merchant Center ID.
var jobIds = []
var pageToken;
var pageNum = 1;
var maxResults = CONFIG.CHUNK_SIZE;
do {
var items = [];
var products = ShoppingContent.Products.list(merchantId, {
pageToken: pageToken,
maxResults: maxResults
});
for(var i = 0; i< products.resources.length ;i++){
items.push(products.resources[i])
}
var ndJson = items.map(JSON.stringify).join('\n')
var blobData = Utilities.newBlob(ndJson, 'application/json');
var jobId = loadDataToBigquery(blobData,reportConfig);
jobIds.push(jobId);
pageToken = products.nextPageToken;
Logger.log("Page "+pageNum +" Ready")
pageNum++;
} while (pageToken);
waitTillJobsComplete(jobIds);
}
// Get Product Status from a given Merchant Account.prototype
function product_status(reportConfig){
var merchantId = CONFIG.MERCHANT_CENTER_ID; // Replace this with your Merchant Center ID.
var jobIds = []
var pageToken;
var pageNum = 1;
var maxResults = CONFIG.CHUNK_SIZE;
do {
var items = [];
var products = ShoppingContent.Productstatuses.list(merchantId, {
pageToken: pageToken,
maxResults: maxResults,
includeInvalidInsertedItems:true,
includeAttributes:true
});
for(var i = 0; i< products.resources.length ;i++){
items.push(products.resources[i])
}
var ndJson = items.map(JSON.stringify).join('\n')
var blobData = Utilities.newBlob(ndJson, 'application/json');
var jobId = loadDataToBigquery(blobData,reportConfig);
jobIds.push(jobId);
pageToken = products.nextPageToken;
Logger.log("Page "+pageNum +" Ready")
pageNum++;
} while (pageToken);
return items;
}
function createDataset() {
if (datasetExists()) {
if (CONFIG.TRUNCATE_EXISTING_DATASET) {
BigQuery.Datasets.remove(CONFIG.BIGQUERY_PROJECT_ID,
CONFIG.BIGQUERY_DATASET_ID, {'deleteContents' : true});
Logger.log('Truncated dataset.');
} else {
Logger.log('Dataset %s already exists. Will not recreate.',
CONFIG.BIGQUERY_DATASET_ID);
return;
}
}
// Create new dataset.
var dataSet = BigQuery.newDataset();
dataSet.friendlyName = CONFIG.BIGQUERY_DATASET_ID;
dataSet.datasetReference = BigQuery.newDatasetReference();
dataSet.datasetReference.projectId = CONFIG.BIGQUERY_PROJECT_ID;
dataSet.datasetReference.datasetId = CONFIG.BIGQUERY_DATASET_ID;
dataSet = BigQuery.Datasets.insert(dataSet, CONFIG.BIGQUERY_PROJECT_ID);
Logger.log('Created dataset with id %s.', dataSet.id);
}
/**
* Checks if dataset already exists in project.
*
* @return {boolean} Returns true if dataset already exists.
*/
function datasetExists() {
// Get a list of all datasets in project.
var datasets = BigQuery.Datasets.list(CONFIG.BIGQUERY_PROJECT_ID);
var datasetExists = false;
// Iterate through each dataset and check for an id match.
if (datasets.datasets != null) {
for (var i = 0; i < datasets.datasets.length; i++) {
var dataset = datasets.datasets[i];
if (dataset.datasetReference.datasetId == CONFIG.BIGQUERY_DATASET_ID) {
datasetExists = true;
break;
}
}
}
return datasetExists;
}
/**
* Creates a new table.
*
* If a table with the same id already exists and the truncate flag
* is set, will truncate the old table. If the truncate flag is not
* set, then will not create a new table.
*
* @param {Object} reportConfig Report configuration including report name,
* conditions, and fields.
*/
function createTable(reportConfig) {
if (tableExists(reportConfig.NAME)) {
if (CONFIG.TRUNCATE_EXISTING_TABLES) {
BigQuery.Tables.remove(CONFIG.BIGQUERY_PROJECT_ID,
CONFIG.BIGQUERY_DATASET_ID, reportConfig.NAME);
Logger.log('Truncated table %s.', reportConfig.NAME);
} else {
Logger.log('Table %s already exists. Will not recreate.',
reportConfig.NAME);
return;
}
}
// Create new table.
var table = BigQuery.newTable();
var schema = BigQuery.newTableSchema();
var bigQueryFields = [];
// Add each field to table schema.
var fieldNames = reportConfig.FIELDS;
for (var i = 0; i < fieldNames.length; i++) {
var fieldName = fieldNames[i].name;
// Logger.log(fieldNames[i])
var bigQueryFieldSchema = BigQuery.newTableFieldSchema();
bigQueryFieldSchema.description = fieldName;
bigQueryFieldSchema.name = fieldName;
bigQueryFieldSchema.type = reportConfig.FIELDS[i].type;
bigQueryFieldSchema.mode = reportConfig.FIELDS[i].mode;
if(reportConfig.FIELDS[i].fields){
bigQueryFieldSchema.fields = reportConfig.FIELDS[i].fields
}
bigQueryFields.push(bigQueryFieldSchema);
}
schema.fields = bigQueryFields;
table.schema = schema;
table.friendlyName = reportConfig.NAME;
table.time_partitioning = {"type":"DAY", "expirationMs":31536000000};
table.tableReference = BigQuery.newTableReference();
table.tableReference.datasetId = CONFIG.BIGQUERY_DATASET_ID;
table.tableReference.projectId = CONFIG.BIGQUERY_PROJECT_ID;
table.tableReference.tableId = reportConfig.NAME;
table = BigQuery.Tables.insert(table, CONFIG.BIGQUERY_PROJECT_ID,
CONFIG.BIGQUERY_DATASET_ID);
Logger.log('Created table with id %s.', table.id);
}
/**
* Checks if table already exists in dataset.
*
* @param {string} tableId The table id to check existence.
*
* @return {boolean} Returns true if table already exists.
*/
function tableExists(tableId) {
// Get a list of all tables in the dataset.
var tables = BigQuery.Tables.list(CONFIG.BIGQUERY_PROJECT_ID,
CONFIG.BIGQUERY_DATASET_ID);
var tableExists = false;
// Iterate through each table and check for an id match.
if (tables.tables != null) {
for (var i = 0; i < tables.tables.length; i++) {
var table = tables.tables[i];
if (table.tableReference.tableId == tableId) {
tableExists = true;
break;
}
}
}
return tableExists;
}
/**
* Creates a BigQuery insertJob to load csv data.
*
* @param {Object} reportConfig Report configuration including report name,
* conditions, and fields.
* @param {Blob} data Csv report data as an 'application/octet-stream' blob.
* @param {number=} skipLeadingRows Optional number of rows to skip.
*
* @return {string} jobId The job id for upload.
*/
function loadDataToBigquery(data,reportConfig) {
// Create the data upload job.
var job = {
configuration: {
load: {
destinationTable: {
projectId: CONFIG.BIGQUERY_PROJECT_ID,
datasetId: CONFIG.BIGQUERY_DATASET_ID,
tableId: reportConfig.NAME
},
//autodetect:true,
source_format:'NEWLINE_DELIMITED_JSON',
time_partitioning:{"type":"DAY"},
maxBadRecords:2,
schemaUpdateOptions:["ALLOW_FIELD_ADDITION","ALLOW_FIELD_RELAXATION"],
schema: {fields:reportConfig.FIELDS}
}
}
};
var insertJob = BigQuery.Jobs.insert(job, CONFIG.BIGQUERY_PROJECT_ID, data);
Logger.log('Load job started for %s. Check on the status of it here: ' +
'https://bigquery.cloud.google.com/jobs/%s', reportConfig.NAME,
CONFIG.BIGQUERY_PROJECT_ID + ' JobId: ' + insertJob.jobReference.jobId);
return insertJob.jobReference.jobId;
}
/**
* Polls until all jobs are 'DONE'.
*
* @param {Array.<string>} jobIds The list of all job ids.
*/
function waitTillJobsComplete(jobIds) {
var complete = false;
var remainingJobs = jobIds;
while (!complete) {
remainingJobs = getIncompleteJobs(remainingJobs);
if (remainingJobs.length == 0) {
complete = true;
}
if (!complete) {
Logger.log(remainingJobs.length + ' jobs still being processed.');
// Wait 5 seconds before checking status again.
Utilities.sleep(5000);
}
}
Logger.log('All jobs processed.');
}
/**
* Iterates through jobs and returns the ids for those jobs
* that are not 'DONE'.
*
* @param {Array.<string>} jobIds The list of job ids.
*
* @return {Array.<string>} remainingJobIds The list of remaining job ids.
*/
function getIncompleteJobs(jobIds) {
var remainingJobIds = [];
for (var i = 0; i < jobIds.length; i++) {
var jobId = jobIds[i];
try{
var getJob = BigQuery.Jobs.get(CONFIG.BIGQUERY_PROJECT_ID, jobId,{'location':CONFIG.LOCATION});
if (getJob.status.state != 'DONE') {
remainingJobIds.push(jobId);
}
}
catch(error){
Logger.log(error)
}
}
return remainingJobIds;
}
Transfer Google Merchant Center data to BigQuery.js hosted with ❤ by GitHub
Conclusion
With the script it is easy to transfer data from GMC to BigQuery. In addition, the “Fields” variable can be used to easily react to changes in the retrieval semantics.
