Function fetchBigQueryData

Data to Narrative with Input.js:52–91

()

function fetchBigQueryData() {
  var input = pullVariables()
  Logger.log(input);
  var projectId = project;

  var query = `SELECT extract(year from date) as year, category_name, item_description, vendor_name, COUNT(*) as num_trans, ROUND(SUM(sale_dollars),2) as sales_in_usd, ROUND(SUM(state_bottle_cost * bottles_sold),2) as cost FROM \`bigquery-public-data.iowa_liquor_sales.sales\` Where EXTRACT(year FROM date) IN (${input.var1}, ${input.var2}) AND category_name = '${input.var3}' AND city = '${input.var4}' Group by ALL Order BY 5 desc;`
 
  var request = {
    query: query,
    useLegacySql: false
  };

  var queryResults = BigQuery.Jobs.query(request, projectId);
  var jobId = queryResults.jobReference.jobId;

  var results = BigQuery.Jobs.getQueryResults(projectId, jobId);
  var rows = results.rows;

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('RawData');
  if (!sheet) {
    sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('RawData');
  } else {
    sheet.clear();
  }

  // Set headers
  var headers = results.schema.fields.map(field => field.name);
  sheet.appendRow(headers);
  var headerRange = sheet.getRange(1, 1, 1, headers.length);
  headerRange.setBackground('#000000').setFontColor('#FFFFFF').setFontWeight('bold');

  // Set Rows
  var values = rows.map(row => row.f.map(cell => cell.v));
  // Get the next empty row in the sheet
  var lastRow = sheet.getLastRow();
  // Append all rows at once
  if (values.length > 0) {
    sheet.getRange(lastRow + 1, 1, values.length, values[0].length).setValues(values);
 }
}

function formatReport() {
  auth();