Function formatReport

Data to Narrative with Input.js:93–215

()

}

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

  var rawData = rawSheet.getDataRange().getValues(); // Unpivoted Data - Not Using below
  var aggData = aggSheet.getDataRange().getValues(); // Pivot Data

  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-flash:generateContent`  
  data = {
    contents: {
      role: "USER",
      parts: { "text": `${input.var5}` + ' Only reffer to the data provided do not make anything up. Full Data:' + aggData}
    },  
    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('#083763') // Dark Blue 3 background