In BigQuery steht ein automatischer Datentransfer für Google Merchant Center Daten zur Verfügung. Leider hat dieser Service das Problem, dass dieser Connector nicht für die Conent API for Shopping v2.1 angepasst wurde. Somit ist der dort generierte Datenbestand lückenhaft und nur bedingt verwendbar.
Dieser Artikel zeigt wie die Importfunktion durch ein Google Apps Script ersetzt werden kann, welches dann täglich die Daten zu BigQuery transferiert.
Das Script
Das Script wird als Google Ads Script installiert und konfiguriert, sodass jeder mit Google Ads Account diese Lösung nutzen kann. Das Skript basiert auf diesem Export Google Ads Reports into BigQuery – Single Account.
Insallation in Kürze
- Google Ads Account aufrufen
- Navigieren zu „Werkzeug > Skripts > +“ um ein neues Script zu erstellen.
- Skript bennen z.B. „Shopping API Transfer nach BigQuery“
- Das Script kopieren
- Die Variable „TRUNCATE_EXISTING_TABLES = true“ setzen, sodass die Tabellenstruktur beim ersten Lauf angelegt wird.
- Oben rechts „Erweiterte APIs“ öffnen und „BigQuery“ und „Shopping Content“ aktivieren.
- Dataset in BigQuery erstellen (wichtig ist die Standortvergabe und die autom. Ablaufzeit der Daten zu definieren)
- Im Script die Felder variablen MERCHANT_CENTER_ID, BIGQUERY_PROJECT_ID, BIGQUERY_DATASET_ID, BIGQUERY_TABLE_NAME mit eigenen Werten füllen.
- Unten rechts „Vorschau“ drücken
- Mit dem gelben Button die Authorisierung vornehmen. (Der eingeloggte Google Account benötigt Zugriff auf BigQuery sowie das Merchant Center)
- Erneut „Vorschau“ ausführen, sodass das Script das erste mal läuft.
- Den Datenbestand in BigQuery korrigieren.
- Nach dem ersten Lauf sollte TRUNCATE_EXISTING_TABLES = false gesetzt werden, weil sonst die Daten der Vergangenheit überschrieben werden.
- In der Script-Übersicht die Zeit des Skript-Aufrufs definieren und auf „täglich“ setzen.
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;
}
Google Merchant Center Daten zu BigQuery übertragen.js hosted with ❤ by GitHub
Fazit
Mit dem Skript ist es einfach möglich Daten vom GMC nach BigQuery zu übertragen. Zudem kann in der „Fields“ Variable einfach auf Änderungen in der Abrufsemantik reagiert werden.
