Google Shopping Content to BigQuery

Google Merchant Center Daten zu BigQuery übertragen.js

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: [
    'bernhard@webmasterei-prange.de'
  ]
};

// 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;
}