Function formatReport

Big Query and Gemini Narrative Reporting.js:50–174

()

}

function formatReport() {
  auth();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var rawSheet = ss.getSheetByName('RawData');
  var reportSheet = ss.getSheetByName('Report') || ss.insertSheet('Report');
  reportSheet.clear();

  var rawData = rawSheet.getDataRange().getValues();

  cache = CacheService.getUserCache();
  token = cache.get("token");
  if (token == "") return "ERROR";
  Logger.log(`Token = ${token}`);
  url = `https://us-central1-aiplatform.googleapis.com/v1/projects/${project}/locations/us-central1/publishers/google/models/gemini-1.5-pro:generateContent`  
  data = {
    contents: {
      role: "USER",
      parts: { "text": "You are a public policy expert. I providing you with three years of 311 service request data from San Francisco. Write a report commenting on improvements and negative movements from the period in question, include the absolute and percentage movement of categories in the discussion, do not include a table. Finish with a five point plan of where we need to put resources. Focus only on the data provided and double check calculations" + rawData}
    },  
    generation_config: {
      temperature: 0.3,
      topP: 1,
      maxOutputTokens: 1000        
    }
  }
  const options = {
    method: "post",
    contentType: 'application/json',   
    headers: {
     Authorization: `Bearer ${token}`,
    },
    payload: JSON.stringify(data)
  };

  const response = UrlFetchApp.fetch(url, options);
  if (response.getResponseCode() == 200) {
    json = JSON.parse(response.getContentText());
    answer = json.candidates[0].content.parts[0].text;
    Logger.log(answer);

// Format the Markdown in Sheets     
var lines = answer.split('\n');
  var rowIndex = 1;

  lines.forEach(function(line) {
    var cell = reportSheet.getRange(rowIndex, 1);
    var text = line.trim();
    
    if (text.startsWith('### ')) {
      // Handle H3 titles
      text = text.replace('### ', '');
      cell.setValue(text)
          .setFontWeight('bold')
          .setFontSize(14)
          .setBackground('#efefef');
    } else if (text.startsWith('## ')) {
      // Handle H2 titles
      text = text.replace('## ', '');