BigQuery Reports nach Google Spreadsheets exportieren und per E-Mail verschicken

Google BigQuery ist ein geniales Tool, um Daten zusammen zu führen und zu verarbeiten.  Allerdings können die meisten Menschen dort nicht direkt arbeiten, weil hierzu SQL Kenntnisse notwendig sind. Looker wäre jetzt eine Alternative, aber auch nicht immer praktisch. Manchmal soll es der gute, alte Spreadsheet und eine E-Mail dazu sein. Dazu biete ich in diesem Artikel eine Lösung.

Das Script wurde ursprünglich für Benachrichtigungen über das Google Merchant Center geschrieben. Es lässt sich aber, mit wenigen Modifikationen, auch sehr gut nutzen, um andere Reportings aus Google BigQuery zu gewinnen.

Was tut das Script?

1. Daten aus einem Table oder View in BigQuery holen.
2. Daten nach Google Spreadsheets exportieren
3. E-Mail generieren und Daten an konfigurierte E-Mails verschicken.

Installation des Scripts

Das Apps Script besteht aus 2 Teilen.

1. Code
2. Config

Bitte die Teile also jeweils in 2 „Dateien“ in Apps Scripts ablegen. So trennt ihr Konfiguration und Funktion. Das ist insb. bei Updates und Änderungen vorteilhaft.

Anschließend Config mit Leben füllen und in Code die doPost oder runQuery ausführen.

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

bigquery_export_spreadsheets_email.js hosted with ❤ by GitHub

In der CONFIG Variable müssen die Daten für BigQuery, ein Spreadsheet, Emails und Email Texte definiert werden:

CONFIG = {
  gcp: {
    projectId: 'MYPROJECT',
    dataset: 'MYDATASET',
    tableid: 'fresh_out'
  },
  spreadsheet: 'MYSPREADSHEET',
  emails: [
    '[email protected]',
    //'[email protected]'
    ],
  email: {
    message_intro: 'Hallo Zusammen,<br>einige Artikel sind im Merchant Center Offline gegangen.<br><strong>Die Tabelle zeigt die Umsätze der letzten 30 Tage.</strong><br>Bitte kontrollieren Sie die Gründe dieser Fehler.',
    subject: 'WARNUNG: Einige Produkte sind im Merchant Center offline.'

  }
}

CONFIG = {

gcp: {

projectId: ‚MYPROJECT‘,

dataset: ‚MYDATASET‘,

tableid: ‚fresh_out‘

},

spreadsheet: ‚MYSPREADSHEET‘,

emails: [

‚[email protected]‘,

//’[email protected]‘

],

email: {

message_intro: ‚Hallo Zusammen,<br>einige Artikel sind im Merchant Center Offline gegangen.<br><strong>Die Tabelle zeigt die Umsätze der letzten 30 Tage.</strong><br>Bitte kontrollieren Sie die Gründe dieser Fehler.‘,

subject: ‚WARNUNG: Einige Produkte sind im Merchant Center offline.‘

}

}

view rawbigquery_export_spreadsheets_email_CONFIG.js hosted with ❤ by GitHub

Fazit

Mit Hilfe des Scripts lassen sich sehr einfach Reportings aus BigQuery realisieren.