bigquery_export_spreadsheets_email.js

function doPost(e){
    runQuery()
}

/**
 * This scripts pulls a table from BigQuery and writes it to Spreadsheets and Email
 */
function runQuery() {
  // Replace this value with the project ID listed in the Google
  // Cloud Platform project.
  var tablepath = CONFIG.gcp.projectId + '.' + CONFIG.gcp.dataset + '.' + CONFIG.gcp.tableid;

  var request = {
    query: 'SELECT offer_id, title, link, destination, reason, total_revenue, ads_revenue FROM ' + tablepath,
    useLegacySql: false
  };
  //Logger.log(request.query)
  var queryResults = BigQuery.Jobs.query(request, CONFIG.gcp.projectId);
  var jobId = queryResults.jobReference.jobId;

  // Check on status of the Query Job.
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
  }

  // Get all the rows of results.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  if (rows) {
    var headers = queryResults.schema.fields.map(function(field) {
      return field.name;
    });
    
    senMail(rows,headers)

    var spreadsheet = SpreadsheetApp.openByUrl(CONFIG.spreadsheet);
    var sheet = spreadsheet.getActiveSheet();
    sheet.clear();
    // Append the headers.
    sheet.appendRow(headers);

    // Append the results.
    var data = new Array(rows.length);
    for (var i = 0; i < rows.length; i++) {
      var cols = rows[i].f;
      data[i] = new Array(cols.length);
      for (var j = 0; j < cols.length; j++) {
        data[i][j] = cols[j].v;
      }
    }
    sheet.getRange(2, 1, rows.length, headers.length).setValues(data);
    Logger.log('Results spreadsheet created: %s',
        spreadsheet.getUrl());
  } else {
    Logger.log('No rows returned.');
  }
}

function senMail(rows,headers){
  var message = CONFIG.email.message_intro + '<br>Es handelt sich dabei um <strong>' + rows.length + ' Produkte.</strong>'
  var tablehead = ''
  headers.forEach(function(colname){
    tablehead = tablehead + '<td>' + colname + '</td>'
  })
  message = message + '<br>Weitere Information finden Sie hier:<br>' + CONFIG.spreadsheet + '<br>' + '<table><tr>' + tablehead + '</tr>'
  // SKUS for Message
  rows.forEach(function(row){
    row = row.f
    message = message + '<tr>';
    for (var i = 0; i < row.length; i++) {
          message = message + '<td>' + row[i].v + '</td>';
    }
     message = message + '</tr>';
   })
  message = message + '</table>'
  CONFIG.emails.forEach(function(email){
    MailApp.sendEmail({
      to: email,
      subject: CONFIG.email.subject,
      htmlBody: message
    });
  })
  
}