{"id":1974,"date":"2021-10-14T15:31:47","date_gmt":"2021-10-14T13:31:47","guid":{"rendered":"https:\/\/webmasterei-prange.de\/google-merchant-center-data-transfer-to-bigquery-bigquery-data-transfer-service\/"},"modified":"2025-10-06T20:51:50","modified_gmt":"2025-10-06T18:51:50","slug":"google-merchant-center-data-transfer-to-bigquery-bigquery-data-transfer-service","status":"publish","type":"post","link":"https:\/\/webmasterei-prange.de\/en\/google-merchant-center-data-transfer-to-bigquery-bigquery-data-transfer-service\/","title":{"rendered":"Google Merchant Center Data Transfer to BigQuery (BigQuery Data Transfer Service)"},"content":{"rendered":"<style>.kb-image1183_8cf785-00 .kb-image-has-overlay:after{opacity:0.3;}<\/style>\n<figure class=\"wp-block-kadence-image kb-image1183_8cf785-00 size-full\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/webmasterei-prange.de\/wp-content\/uploads\/2025\/09\/google-merchant-center-daten-nach-bigquer.png.webp\" alt=\"Google Merchant Center data to Bigquer\" width=\"1910\" height=\"1000\" class=\"kb-img wp-image-1961 \"><\/figure>\n\n<p>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.<\/p>\n\n<p>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.<\/p>\n\n<h2 class=\"wp-block-heading\" id=\"toc_Das_Script\">The script<\/h2>\n\n<p>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 <a href=\"https:\/\/developers.google.com\/google-ads\/scripts\/docs\/solutions\/bigquery-exporter?hl=fi\" target=\"_blank\" rel=\"noreferrer noopener\">export Google Ads Reports into BigQuery \u2013 Single Account<\/a> .<\/p>\n\n<p><strong>Installation in a nutshell<\/strong><\/p>\n\n<ol class=\"wp-block-list\">\n<li>Open Google Ads account<\/li>\n\n\n\n<li>Navigate to &#8221; <strong>Tools<\/strong>&gt; <strong>Scripts<\/strong>&gt; <strong>+<\/strong> \u201c to create a new script.<\/li>\n\n\n\n<li>Name the script e.g. \u201cShopping API Transfer to BigQuery\u201d<\/li>\n\n\n\n<li>Copy the script<\/li>\n\n\n\n<li>Set the variable \u201cTRUNCATE_EXISTING_TABLES = true\u201d so that the table structure is created on the first run.<\/li>\n\n\n\n<li>Open \u201cAdvanced APIs\u201d in the top right corner and activate \u201cBigQuery\u201d and \u201cShopping Content\u201d.<\/li>\n\n\n\n<li>Create dataset in BigQuery (important to define the location and autom. expiration time of the data)<\/li>\n\n\n\n<li>In the script, fill the fields variable MERCHANT_CENTER_ID, BIGQUERY_PROJECT_ID, BIGQUERY_DATASET_ID, BIGQUERY_TABLE_NAME with your own values.<\/li>\n\n\n\n<li>Press \u201cPreview\u201d at the bottom right<\/li>\n\n\n\n<li>Use the yellow button to authorize. (The logged in Google account needs access to BigQuery as well as the Merchant Center)<\/li>\n\n\n\n<li>Run \u201cPreview\u201d again so that the script runs for the first time.<\/li>\n\n\n\n<li>Correct the dataset in BigQuery.<\/li>\n\n\n\n<li>After the first run, TRUNCATE_EXISTING_TABLES should be set to false, otherwise past data will be overwritten.<\/li>\n\n\n\n<li>In the script overview, define the time of the script call and set it to \u201cdaily\u201d.<\/li>\n<\/ol>\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"js\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">var CONFIG = {\n  MERCHANT_CENTER_ID: 'GMCID',\n  BIGQUERY_PROJECT_ID: 'BQPID',\n  BIGQUERY_DATASET_ID: 'BQDSID',\n  LOCATION: 'eu',\n  CHUNK_SIZE: 250, \/\/ 250 is max size for Content API\n  \/\/ Truncate existing data, otherwise will append.\n  TRUNCATE_EXISTING_DATASET: false,\n  TRUNCATE_EXISTING_TABLES: false,\n  \/\/ Lists of reports and fields to retrieve from Google Ads.\n  REPORTS: {PRODUCTS:{\n  NAME: 'PRODUCTS',\n  CONDITIONS: '',\n  FIELDS: [\n  {\"name\":\"id\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"offerId\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"source\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"title\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"description\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"link\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"imageLink\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"contentLanguage\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"targetCountry\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"feedLabel\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"channel\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"brand\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"gtin\", \"type\":\"FLOAT\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"customLabel0\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"customLabel1\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"customLabel2\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"customLabel3\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"customLabel4\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"price\", \"type\":\"RECORD\", \"mode\":\"NULLABLE\", \"fields\":[\n  {\"name\":\"value\", \"type\":\"FLOAT\", \"mode\":\"NULLABLE\"},\n          {\"name\":\"currency\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"}\n  ]\n  },\n  {\"name\":\"salePrice\", \"type\":\"RECORD\", \"mode\":\"NULLABLE\", \"fields\":[\n  {\"name\":\"value\", \"type\":\"FLOAT\", \"mode\":\"NULLABLE\"},\n          {\"name\":\"currency\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"}\n  ]\n  },\n  {\"name\":\"additionalImageLinks\", \"type\":\"STRING\", \"mode\":\"REPEATED\"},\n  {\"name\":\"expirationDate\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"adult\", \"type\":\"BOOLEAN\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"kind\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"color\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"googleProductCategory\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"itemGroupId\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"mpn\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"pattern\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"material\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"salePriceEffectiveDate\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"shipping\", \"type\":\"RECORD\", \"mode\":\"REPEATED\", \"fields\":[\n  {\"name\":\"price\", \"type\":\"RECORD\", \"mode\":\"NULLABLE\", \"fields\":\n  [\n  {\"name\":\"value\", \"type\":\"FLOAT\", \"mode\":\"NULLABLE\"},\n             {\"name\":\"currency\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"}\n  ]\n  },\n  {\"name\":\"country\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"region\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"service\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"locationId\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"locationGroupName\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"postalCode\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"minHandlingTime\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"maxHandlingTime\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"minTransitTime\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n          {\"name\":\"maxTransitTime\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"}\n  ]\n  },\n  {\"name\":\"shippingWeight\", \"type\":\"RECORD\", \"mode\":\"NULLABLE\", \"fields\":[\n  {\"name\":\"value\", \"type\":\"FLOAT\", \"mode\":\"NULLABLE\"},\n          {\"name\":\"unit\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"}\n  ]\n  },\n  {\"name\":\"sizes\", \"type\":\"STRING\", \"mode\":\"REPEATED\"},\n  {\"name\":\"customAttributes\", \"type\":\"RECORD\", \"mode\":\"REPEATED\", \"fields\":[\n  {\"name\":\"name\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"value\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"groupValues\", \"type\":\"RECORD\", \"mode\":\"REPEATED\",\"fields\":[\n  {\"name\":\"name\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"value\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n             {\"name\":\"groupValues\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"}\n  ]}\n  ]},\n  {\"name\":\"identifierExists\", \"type\":\"BOOLEAN\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"multipack\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"isBundle\", \"type\":\"BOOLEAN\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"mobileLink\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"availabilityDate\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"shippingLabel\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"unitPricingMeasure\", \"type\":\"RECORD\", \"mode\":\"NULLABLE\", \"fields\":[\n  {\"name\":\"value\", \"type\":\"FLOAT\", \"mode\":\"NULLABLE\"},\n          {\"name\":\"unit\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"}\n  ]\n  },\n  {\"name\":\"unitPricingBaseMeasure\", \"type\":\"RECORD\", \"mode\":\"NULLABLE\", \"fields\":[\n  {\"name\":\"value\", \"type\":\"FLOAT\", \"mode\":\"NULLABLE\"},\n          {\"name\":\"unit\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"}\n  ]\n  },\n  {\"name\":\"shippingLength\", \"type\":\"RECORD\", \"mode\":\"NULLABLE\", \"fields\":[\n  {\"name\":\"value\", \"type\":\"FLOAT\", \"mode\":\"NULLABLE\"},\n          {\"name\":\"unit\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"}\n  ]\n  },\n  {\"name\":\"shippingWidth\", \"type\":\"RECORD\", \"mode\":\"NULLABLE\", \"fields\":[\n  {\"name\":\"value\", \"type\":\"FLOAT\", \"mode\":\"NULLABLE\"},\n          {\"name\":\"unit\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"}\n  ]\n  },\n  {\"name\":\"shippingHeight\", \"type\":\"RECORD\", \"mode\":\"NULLABLE\", \"fields\":[\n  {\"name\":\"value\", \"type\":\"FLOAT\", \"mode\":\"NULLABLE\"},\n          {\"name\":\"unit\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"}\n  ]\n  },\n  {\"name\":\"displayAdsId\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"displayAdsSimilarIds\", \"type\":\"STRING\", \"mode\":\"REPEATED\"},\n  {\"name\":\"displayAdsTitle\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"displayAdsLink\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"sellOnGoogleQuantity\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"promotionIds\", \"type\":\"STRING\", \"mode\":\"REPEATED\"},\n  {\"name\":\"maxHandlingTime\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"minHandlingTime\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"costOfGoodsSold\", \"type\":\"RECORD\", \"mode\":\"NULLABLE\", \"fields\":[\n  {\"name\":\"value\", \"type\":\"FLOAT\", \"mode\":\"NULLABLE\"},\n          {\"name\":\"currency\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"}\n  ]\n  },\n  {\"name\":\"adsGrouping\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"adsLabels\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"adsRedirect\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"productTypes\", \"type\":\"STRING\", \"mode\":\"REPEATED\"},\n  {\"name\":\"ageGroup\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"availability\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"condition\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"gender\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"sizeSystem\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"sizeType\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"additionalSizeType\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"destinations\", \"type\":\"RECORD\", \"mode\":\"REPEATED\", \"fields\":[\n  {\"name\":\"destinationName\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n          {\"name\":\"intention\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"}\n  ]\n  },\n  {\"name\":\"destinationStatuses\", \"type\":\"RECORD\", \"mode\":\"REPEATED\", \"fields\":[\n  {\"name\":\"approvedCountries\", \"type\":\"STRING\", \"mode\":\"REPEATED\"},\n  {\"name\":\"destination\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"status\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"disapprovedCountries\", \"type\":\"STRING\", \"mode\":\"REPEATED\"},\n          {\"name\":\"pendingCountries\", \"type\":\"STRING\", \"mode\":\"REPEATED\"}\n  ]\n  },\n  {\"name\":\"itemLevelIssues\", \"type\":\"RECORD\", \"mode\":\"REPEATED\", \"fields\":[\n  {\"name\":\"code\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"servability\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"resolution\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"attributeName\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"destination\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"description\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"detail\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"documentation\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n          {\"name\":\"applicableCountries\", \"type\":\"STRING\", \"mode\":\"REPEATED\"}\n  ]\n  },\n  {\"name\":\"energyEfficiencyClass\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"minEnergyEfficiencyClass\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"maxEnergyEfficiencyClass\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"taxCategory\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"transitTimeLabel\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"shoppingAdsExcludedCountries\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"pickupMethod\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"pickupSla\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"linkTemplate\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"mobileLinkTemplate\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"productDetails\", \"type\":\"RECORD\", \"mode\":\"REPEATED\", \"fields\":[\n  {\"name\":\"sectionName\", \"type\":\"string\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"attributeName\", \"type\":\"string\", \"mode\":\"NULLABLE\"},\n          {\"name\":\"attributeValue\", \"type\":\"string\", \"mode\":\"NULLABLE\"}\n  ]\n  },  \n  {\"name\":\"productHighlights\", \"type\":\"STRING\", \"mode\":\"REPEATED\"},\n  {\"name\":\"subscriptionCost\", \"type\":\"RECORD\", \"mode\":\"REPEATED\", \"fields\":[\n  {\"name\":\"price\", \"type\":\"RECORD\", \"mode\":\"NULLABLE\", \"fields\":\n  [\n  {\"name\":\"value\", \"type\":\"FLOAT\", \"mode\":\"NULLABLE\"},\n             {\"name\":\"currency\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"}\n  ]\n  },\n          {\"name\":\"period\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"}\n  ]\n  },\n  {\"name\":\"canonicalLink\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"creationDate\", \"type\":\"DATE\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"lastUpdateDate\", \"type\":\"DATE\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"googleExpirationDate\", \"type\":\"DATE\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"includedDestinations\", \"type\":\"STRING\", \"mode\":\"REPEATED\"},\n  {\"name\":\"excludedDestinations\", \"type\":\"STRING\", \"mode\":\"REPEATED\"},\n  ]\n  },\n  PRODUCTS_STATUS:  \n  {\n  NAME: 'PRODUCTS_STATUS',\n  CONDITIONS: '',\n  FIELDS: [\n  {\"name\":\"productId\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"title\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"link\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"destinationStatuses\", \"type\":\"RECORD\", \"mode\":\"REPEATED\", \"fields\":[\n  {\"name\":\"approvedCountries\", \"type\":\"STRING\", \"mode\":\"REPEATED\"},\n  {\"name\":\"destination\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"status\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"disapprovedCountries\", \"type\":\"STRING\", \"mode\":\"REPEATED\"},\n          {\"name\":\"pendingCountries\", \"type\":\"STRING\", \"mode\":\"REPEATED\"}\n  ]\n  },\n  {\"name\":\"kind\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"creationDate\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"lastUpdateDate\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"googleExpirationDate\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n\n  {\"name\":\"itemLevelIssues\", \"type\":\"RECORD\", \"mode\":\"REPEATED\", \"fields\":[\n  {\"name\":\"code\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"servability\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"resolution\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"attributeName\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"destination\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"description\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"detail\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n  {\"name\":\"documentation\", \"type\":\"STRING\", \"mode\":\"NULLABLE\"},\n          {\"name\":\"applicableCountries\", \"type\":\"STRING\", \"mode\":\"REPEATED\"}\n  ]\n  }\n  ]\n  }\n  }\n  ,\n\n  RECIPIENT_EMAILS: [\n  'bernhard@webmasterei-prange.de'\n  ]\n};\n\n\/\/ Impose a limit on the size of BQ inserts: 10MB - 512Kb for overheads.\nvar MAX_INSERT_SIZE = 10 * 1024 * 1024 - 512 * 1024;\n\n\/**\n  * Main method\n  *\/\nfunction main() {\n  \/\/ CREATE FUNCTIONS\n  createDataset();\n  createTable(CONFIG.REPORTS.PRODUCTS);\n  \/\/ Get Data and Ship it to BQ\n  productList(CONFIG.REPORTS.PRODUCTS)\n  product_status(CONFIG.REPORTS.PRODUCTS_STATUS)\n  }\n\n\n\/**\n  * Lists the products for a given merchant.\n  *\/\nfunction productList(reportConfig) {\n  var merchantId = CONFIG.MERCHANT_CENTER_ID; \/\/ Replace this with your Merchant Center ID.\n  var jobIds = []\n  var pageToken;\n  var pageNum = 1;\n  var maxResults = CONFIG.CHUNK_SIZE;\n  do {\n  var items = [];\n  var products = ShoppingContent.Products.list(merchantId, {\n  pageToken: pageToken,\n  maxResults: maxResults\n  });\n  for(var i = 0; i&lt;  products.resources.length ;i++){\n  items.push(products.resources[i])\n  }\n    \n  var ndJson = items.map(JSON.stringify).join('\\n')\n  var blobData = Utilities.newBlob(ndJson, 'application\/json');\n  var jobId = loadDataToBigquery(blobData,reportConfig);\n  jobIds.push(jobId);\n  pageToken = products.nextPageToken;\n  Logger.log(\"Page \"+pageNum +\" Ready\")\n  pageNum++;\n  } while (pageToken);\n  waitTillJobsComplete(jobIds);\n}\n\n\n\/\/ Get Product Status from a given Merchant Account.prototype\nfunction product_status(reportConfig){\n  var merchantId = CONFIG.MERCHANT_CENTER_ID; \/\/ Replace this with your Merchant Center ID.\n  var jobIds = []\n  var pageToken;\n  var pageNum = 1;\n  var maxResults = CONFIG.CHUNK_SIZE;\n  do {\n  var items = [];\n  var products = ShoppingContent.Productstatuses.list(merchantId, {\n  pageToken: pageToken,\n  maxResults: maxResults,\n  includeInvalidInsertedItems:true,\n  includeAttributes:true\n  });\n  for(var i = 0; i&lt;  products.resources.length ;i++){\n  items.push(products.resources[i])\n  }\n  var ndJson = items.map(JSON.stringify).join('\\n')\n  var blobData = Utilities.newBlob(ndJson, 'application\/json');\n  var jobId = loadDataToBigquery(blobData,reportConfig);\n  jobIds.push(jobId);\n  pageToken = products.nextPageToken;\n  Logger.log(\"Page \"+pageNum +\" Ready\")\n  pageNum++;\n  } while (pageToken);\n  return items;\n}\n\n\nfunction createDataset() {\n  if (datasetExists()) {\n  if (CONFIG.TRUNCATE_EXISTING_DATASET) {\n  BigQuery.Datasets.remove(CONFIG.BIGQUERY_PROJECT_ID,\n  CONFIG.BIGQUERY_DATASET_ID, {'deleteContents' : true});\n  Logger.log('Truncated dataset.');\n  } else {\n  Logger.log('Dataset %s already exists. Will not recreate.',\n  CONFIG.BIGQUERY_DATASET_ID);\n  return;\n  }\n  }\n\n  \/\/ Create new dataset.\n  var dataSet = BigQuery.newDataset();\n  dataSet.friendlyName = CONFIG.BIGQUERY_DATASET_ID;\n  dataSet.datasetReference = BigQuery.newDatasetReference();\n  dataSet.datasetReference.projectId = CONFIG.BIGQUERY_PROJECT_ID;\n  dataSet.datasetReference.datasetId = CONFIG.BIGQUERY_DATASET_ID;\n\n  dataSet = BigQuery.Datasets.insert(dataSet, CONFIG.BIGQUERY_PROJECT_ID);\n  Logger.log('Created dataset with id %s.', dataSet.id);\n}\n\n\/**\n  * Checks if dataset already exists in project.\n  *\n  * @return  {boolean}  Returns true if dataset already exists.\n  *\/\nfunction datasetExists() {\n  \/\/ Get a list of all datasets in project.\n  var datasets = BigQuery.Datasets.list(CONFIG.BIGQUERY_PROJECT_ID);\n  var datasetExists = false;\n  \/\/ Iterate through each dataset and check for an id match.\n  if (datasets.datasets != null) {\n  for (var i = 0; i  &lt;  datasets.datasets.length; i++) {\n  var dataset = datasets.datasets[i];\n  if (dataset.datasetReference.datasetId == CONFIG.BIGQUERY_DATASET_ID) {\n  datasetExists = true;\n  break;\n  }\n  }\n  }\n  return datasetExists;\n}\n\n\/**\n  * Creates a new table.\n  *\n  * If a table with the same id already exists and the truncate flag\n  * is set, will truncate the old table. If the truncate flag is not \n  * set, then will not create a new table.\n  *\n  * @param  {Object}  reportConfig Report configuration including report name,\n  * conditions, and fields.\n  *\/\nfunction createTable(reportConfig) {\n  if (tableExists(reportConfig.NAME)) {\n  if (CONFIG.TRUNCATE_EXISTING_TABLES) {\n  BigQuery.Tables.remove(CONFIG.BIGQUERY_PROJECT_ID,\n  CONFIG.BIGQUERY_DATASET_ID, reportConfig.NAME);\n  Logger.log('Truncated table %s.', reportConfig.NAME);\n  } else {\n  Logger.log('Table %s already exists. Will not recreate.',\n  reportConfig.NAME);\n  return;\n  }\n  }\n\n  \/\/ Create new table.\n  var table = BigQuery.newTable();\n  var schema = BigQuery.newTableSchema();\n  var bigQueryFields = [];\n\n  \/\/ Add each field to table schema.\n  var fieldNames = reportConfig.FIELDS;\n  for (var i = 0; i  &lt;  fieldNames.length; i++) {\n  var fieldName = fieldNames[i].name;\n\/\/ Logger.log(fieldNames[i])\n  var bigQueryFieldSchema = BigQuery.newTableFieldSchema();\n  bigQueryFieldSchema.description = fieldName;\n  bigQueryFieldSchema.name = fieldName;\n  bigQueryFieldSchema.type = reportConfig.FIELDS[i].type;\n  bigQueryFieldSchema.mode = reportConfig.FIELDS[i].mode;\n  if(reportConfig.FIELDS[i].fields){\n  bigQueryFieldSchema.fields = reportConfig.FIELDS[i].fields\n  }\n\n  bigQueryFields.push(bigQueryFieldSchema);\n  }\n\n  schema.fields = bigQueryFields;\n  table.schema = schema;\n  table.friendlyName = reportConfig.NAME;\n  table.time_partitioning = {\"type\":\"DAY\", \"expirationMs\":31536000000};\n  table.tableReference = BigQuery.newTableReference();\n  table.tableReference.datasetId = CONFIG.BIGQUERY_DATASET_ID;\n  table.tableReference.projectId = CONFIG.BIGQUERY_PROJECT_ID;\n  table.tableReference.tableId = reportConfig.NAME;\n\n  table = BigQuery.Tables.insert(table, CONFIG.BIGQUERY_PROJECT_ID,\n  CONFIG.BIGQUERY_DATASET_ID);\n\n  Logger.log('Created table with id %s.', table.id);\n}\n\n\/**\n  * Checks if table already exists in dataset.\n  *\n  * @param  {string}  tableId The table id to check existence.\n  *\n  * @return  {boolean}  Returns true if table already exists.\n  *\/\nfunction tableExists(tableId) {\n  \/\/ Get a list of all tables in the dataset.\n  var tables = BigQuery.Tables.list(CONFIG.BIGQUERY_PROJECT_ID,\n  CONFIG.BIGQUERY_DATASET_ID);\n  var tableExists = false;\n  \/\/ Iterate through each table and check for an id match.\n  if (tables.tables != null) {\n  for (var i = 0; i  &lt;  tables.tables.length; i++) {\n  var table = tables.tables[i];\n  if (table.tableReference.tableId == tableId) {\n  tableExists = true;\n  break;\n  }\n  }\n  }\n  return tableExists;\n}\n\n\/**\n  * Creates a BigQuery insertJob to load csv data.\n  *\n  * @param  {Object}  reportConfig Report configuration including report name,\n  * conditions, and fields.\n  * @param  {Blob}  data Csv report data as an 'application\/octet-stream' blob.\n  * @param {number=} skipLeadingRows Optional number of rows to skip.\n  *\n  * @return  {string}  jobId The job id for upload.\n  *\/\nfunction loadDataToBigquery(data,reportConfig) {\n  \/\/ Create the data upload job.\n  var job = {\n  configuration: {\n  load: {\n  destinationTable: {\n  projectId: CONFIG.BIGQUERY_PROJECT_ID,\n  datasetId: CONFIG.BIGQUERY_DATASET_ID,\n  tableId: reportConfig.NAME\n  },\n \n\/\/autodetect:true,\n  source_format:'NEWLINE_DELIMITED_JSON',\n  time_partitioning:{\"type\":\"DAY\"},\n  maxBadRecords:2,\n  schemaUpdateOptions:[\"ALLOW_FIELD_ADDITION\",\"ALLOW_FIELD_RELAXATION\"],\n  schema: {fields:reportConfig.FIELDS}\n  }\n  }\n  };\n\n  var insertJob = BigQuery.Jobs.insert(job, CONFIG.BIGQUERY_PROJECT_ID, data);\n  Logger.log('Load job started for %s. Check on the status of it here: ' + \n  'https:\/\/bigquery.cloud.google.com\/jobs\/%s', reportConfig.NAME,\n  CONFIG.BIGQUERY_PROJECT_ID + ' JobId: ' + insertJob.jobReference.jobId);\n  return insertJob.jobReference.jobId;\n}\n\n\/**\n  * Polls until all jobs are 'DONE'.\n  *\n  * @param {Array.&lt;string&gt;} jobIds The list of all job ids.\n  *\/\nfunction waitTillJobsComplete(jobIds) {\n  var complete = false;\n  var remainingJobs = jobIds;\n  while (!complete) {\n  remainingJobs = getIncompleteJobs(remainingJobs);\n  if (remainingJobs.length == 0) {\n  complete = true;\n  }\n  if (!complete) {\n  Logger.log(remainingJobs.length + ' jobs still being processed.');\n  \/\/ Wait 5 seconds before checking status again.\n  Utilities.sleep(5000);\n  }\n  }\n  Logger.log('All jobs processed.');\n}\n\n\/**\n  * Iterates through jobs and returns the ids for those jobs\n  * that are not 'DONE'.\n  *\n  * @param {Array.&lt;string&gt;} jobIds The list of job ids.\n  *\n  * @return {Array.&lt;string&gt;} remainingJobIds The list of remaining job ids.\n  *\/\nfunction getIncompleteJobs(jobIds) {\n  var remainingJobIds = [];\n  for (var i = 0; i  &lt;  jobIds.length; i++) {\n  var jobId = jobIds[i];\n  try{\n  var getJob = BigQuery.Jobs.get(CONFIG.BIGQUERY_PROJECT_ID, jobId,{'location':CONFIG.LOCATION});\n  if (getJob.status.state != 'DONE') {\n  remainingJobIds.push(jobId);\n  }\n  }\n  catch(error){\n  Logger.log(error)\n  }\n  }\n  return remainingJobIds;\n}<\/pre>\n\n<p><a href=\"https:\/\/gist.github.com\/Webmasterei\/0e86db50705514cfafe35ad6eafda5c3#file-google-merchant-center-daten-zu-bigquery-ubertragen-js\">Transfer Google Merchant Center data to BigQuery.js<\/a> hosted with \u2764 by <a href=\"https:\/\/github.com\/\">GitHub<\/a><\/p>\n\n<h2 class=\"wp-block-heading\" id=\"toc_Fazit\">Conclusion<\/h2>\n\n<p>With the script it is easy to transfer data from GMC to BigQuery. In addition, the \u201cFields\u201d variable can be used to easily react to changes in the retrieval semantics.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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&#8230;<\/p>\n","protected":false},"author":1,"featured_media":1961,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_kad_blocks_custom_css":"","_kad_blocks_head_custom_js":"","_kad_blocks_body_custom_js":"","_kad_blocks_footer_custom_js":"","_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"_kad_post_classname":"","slim_seo":{"title":"Transfer Google Merchant Center data via BigQuery Data Transfer Service","description":"How to use the BigQuery Data Transfer Service to automatically import Merchant Center data into BigQuery and create reports efficiently."},"footnotes":""},"categories":[28,32,31],"tags":[],"class_list":["post-1974","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-big-query","category-google-ads-scripts","category-google-shopping"],"taxonomy_info":{"category":[{"value":28,"label":"Big Query"},{"value":32,"label":"Google Ads Scripts"},{"value":31,"label":"Google Shopping"}]},"featured_image_src_large":["https:\/\/webmasterei-prange.de\/wp-content\/uploads\/2025\/09\/google-merchant-center-daten-nach-bigquer.png-1024x536.webp",1024,536,true],"author_info":{"display_name":"admin","author_link":"https:\/\/webmasterei-prange.de\/en\/author\/admin\/"},"comment_info":0,"category_info":[{"term_id":28,"name":"Big Query","slug":"big-query","term_group":0,"term_taxonomy_id":28,"taxonomy":"category","description":"","parent":0,"count":12,"filter":"raw","cat_ID":28,"category_count":12,"category_description":"","cat_name":"Big Query","category_nicename":"big-query","category_parent":0},{"term_id":32,"name":"Google Ads Scripts","slug":"google-ads-scripts","term_group":0,"term_taxonomy_id":32,"taxonomy":"category","description":"","parent":0,"count":10,"filter":"raw","cat_ID":32,"category_count":10,"category_description":"","cat_name":"Google Ads Scripts","category_nicename":"google-ads-scripts","category_parent":0},{"term_id":31,"name":"Google Shopping","slug":"google-shopping","term_group":0,"term_taxonomy_id":31,"taxonomy":"category","description":"","parent":0,"count":7,"filter":"raw","cat_ID":31,"category_count":7,"category_description":"","cat_name":"Google Shopping","category_nicename":"google-shopping","category_parent":0}],"tag_info":false,"_links":{"self":[{"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/posts\/1974","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/comments?post=1974"}],"version-history":[{"count":1,"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/posts\/1974\/revisions"}],"predecessor-version":[{"id":1975,"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/posts\/1974\/revisions\/1975"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/media\/1961"}],"wp:attachment":[{"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/media?parent=1974"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/categories?post=1974"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/tags?post=1974"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}