Function fetchBigQueryData

automatereporting.js:1–37

()

function fetchBigQueryData() {
  var projectId = 'YOURPROJECTHERE';
  var query = 'SELECT unique_key,CAST(created_date AS DATE) AS created_date, status, status_notes, agency_name, category, complaint_type, source FROM `bigquery-public-data.san_francisco_311.311_service_requests` Where extract(year from created_date) = 2024 LIMIT 100;'
  
  //'SELECT * FROM `bigquery-public-data.san_francisco_311.311_service_requests` where agency_name = "Muni Feedback Received Queue" LIMIT 100';

  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
  for (var i = 0; i < rows.length; i++) {
    var row = rows[i].f.map(cell => cell.v);
    sheet.appendRow(row);
  }
}

function formatReport() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();