Vectara GSheet Q&A

Code.gs

var vectaraCorpusKey = 'RFI-RFP'; // your corpus ID
var vectaraApiKey = 'zut_...'; // an API key with access to read and write to this corpus
var unsavedFontColor = '#ff0000';
var unsavedFontStyle = 'italic';

function onEdit(e) {
  var ss = e.source;
  var cell = e.range;
  var formula = cell.getFormula();

  if(! formula) {
    if (cell.isBlank()) {
      cell.setFontColor(null);
      cell.setFontStyle(null);
    } else {
      cell.setFontColor(unsavedFontColor);
      cell.setFontStyle(unsavedFontStyle);
    }
  }
}

function saveUnsavedAnswers() {
  var current_sheet = SpreadsheetApp.getActiveSpreadsheet();

  var metadata = getCurrentMetadata();
  var questionColumn = null;
  var company = current_sheet.getId();
  if (metadata.hasOwnProperty('company')) {
    company = metadata['company'];
  }
  if (metadata.hasOwnProperty('question_column')) {
    questionColumn = metadata['question_column'];
  }

  var range = current_sheet.getRange('A:Z');
  var lastRowNumber = current_sheet.getLastRow();
  var lastColumnNumber = current_sheet.getLastColumn();
  var data = range.getValues();
  for (var i = 0; i < lastRowNumber; i++){
    for (var j = 0; j < data[0].length; j++){
      if (j > lastColumnNumber) {
        break;
      }
      var cell = range.getCell(1+i, 1+j);
      var formula = cell.getFormula();
      if (! formula) {
        if (cell.getFontColorObject().asRgbColor().asHexString().toLowerCase() === unsavedFontColor.toLowerCase()
            && cell.getFontStyle() === unsavedFontStyle) {
          if (questionColumn && cell.getA1Notation() === (questionColumn + String(1+i))) {
            // This is a question, just mark it off
            cell.setFontColor(null);
            cell.setFontStyle(null);
          } else {
            var question = null;
            if (questionColumn !== null) {
              question = current_sheet.getRange(questionColumn + String(1+i)).getValue();
              cell.setFontColor(null);
              cell.setFontStyle(null);
            }
            SaveVectaraAnswer(question, cell.getValue(), cell.getA1Notation(), company)
          }
        }
      } 
    }
  }
}

function onOpen() {
  updateMenus(getCurrentMetadata());
}

function updateMenus(metadata) {
  var current_sheet = SpreadsheetApp.getActiveSpreadsheet();
  try {
    current_sheet.removeMenu('Vectara');
  } catch (error) {}

  var company = 'Set Company';
  if (metadata.hasOwnProperty('company')) {
    company = `Company: ${metadata['company']}`;
  }
  var questionColumn = 'Set Column With the Questions';
  if (metadata.hasOwnProperty('question_column')) {
    questionColumn = `Question Column: ${metadata['question_column']}`;
  }
  var menuEntries = [];
  menuEntries.push({name: company, functionName: 'setCompanyName'});
  menuEntries.push({name: questionColumn, functionName: 'setQuestionColumn'});
  menuEntries.push(null);
  menuEntries.push({name: 'Save Unsaved Answers', functionName: 'saveUnsavedAnswers'});
  current_sheet.addMenu('Vectara', menuEntries);
}

function getCurrentMetadata(){
  var current_sheet = SpreadsheetApp.getActiveSpreadsheet();
  var metadata_doc_id = "metadata%3A" + current_sheet.getId();

  var headers = {
     'x-api-key': vectaraApiKey
  };

  var url = `https://api.vectara.io/v2/corpora/${vectaraCorpusKey}/documents/${metadata_doc_id}`;
  var options = {
    'method' : 'get',
    'contentType': 'application/json',
    'headers': headers
  };
  var metadata = {};
  try {
    var response = JSON.parse(UrlFetchApp.fetch(url, options));
    
    if (response.hasOwnProperty('metadata')) {
      metadata = response['metadata'];
    }
    Logger.log(response);
  } catch (error) {
  }
  
  return metadata;
}

function updateMetadata(metadata_name, answer) {
  var current_metadata = getCurrentMetadata();
  current_metadata[metadata_name] = answer;
  current_metadata['data_type'] = 'metadata';

  var current_sheet = SpreadsheetApp.getActiveSpreadsheet();

  var document_id = 'metadata:' + current_sheet.getId();

  var headers = {
     'x-api-key': vectaraApiKey
  };

  var document = {
    'id': document_id,
    'type': 'structured',
    'metadata': current_metadata,
    'sections': [ {'text': 'Metadata for spreadsheet Q&A'} ]
  };

  try {
    var url = `https://api.vectara.io/v2/corpora/${vectaraCorpusKey}/documents/${document_id}`;
    var options = {
      'method' : 'delete',
      'contentType': 'application/json',
      'headers': headers
    };
    var response = JSON.parse(UrlFetchApp.fetch(url, options));
  } catch (error) {}

  var url = `https://api.vectara.io/v2/corpora/${vectaraCorpusKey}/documents`;
  var options = {
    'method' : 'post',
    'contentType': 'application/json',
    'headers': headers,
    'payload': JSON.stringify(document)
  };
  var response = JSON.parse(UrlFetchApp.fetch(url, options));
  Logger.log(response);
  return current_metadata;
}

function setCompanyName() {
  var result = SpreadsheetApp.getUi().prompt("What company is this for?");
  var textResponse = result.getResponseText();
  if (textResponse) {
    var new_metadata = updateMetadata('company', textResponse);
    updateMenus(new_metadata);
  }
}

function setQuestionColumn() {
  var result = SpreadsheetApp.getUi().prompt("Which column contains the questions?");
  var textResponse = result.getResponseText();
  if (textResponse) {
    var new_metadata = updateMetadata('question_column', textResponse);
    updateMenus(new_metadata);
  }
}

/**
 * Attempts to answer a particular question using Vectara's RAG capabilities
 *
 * @param {string} question The question to answer.
 * @return The answer according to Vectara.
 * @customfunction
 */
function VectaraAnswer(question) {
  var vectaraPrompt = `[
    {"role": "system", "content": "You are an RFI answering assistant acting on behalf of the company Vectara.  You are provided with search results from previously responded-to RFIs that may help answer the given question. You must summarize these results as a coherent answer. Give slight preference to search results that appear earlier in the chat. Only use information provided in this chat."},
    #foreach ($qResult in $vectaraQueryResults)
      #if ($foreach.first)
        {"role": "user", "content": "Search for \\"$esc.java(\\\${vectaraQuery})\\", and give me the first search result."},
        {"role": "assistant", "content": "$esc.java(\\\${qResult.getText()})" },
      #else
        {"role": "user", "content": "Give me the $vectaraIdxWord[$foreach.index] search result."},
        {"role": "assistant", "content": "$esc.java(\\\${qResult.getText()})" },
      #end
    #end
    {"role": "user", "content": "Generate a comprehensive and informative answer (but no more than $vectaraOutChars characters) for the question \\"$esc.java(\\\${vectaraQuery})\\" solely based on the search results in this chat. You must only use information from the provided results. Combine search results together into a coherent answer. Do not repeat text. Only use the most relevant results that answer the question accurately. If a result does not answer the question, do not use it. If the search results are not valid, respond with \\"The returned results did not contain sufficient information to the question.\\"."}
  ]`;

  var headers = {
     'x-api-key': vectaraApiKey
  };

  var url = `https://api.vectara.io/v2/corpora/${vectaraCorpusKey}/query`;

  var query = {
    'query': question,
    'search': {
      'lexical_interpolation': 0.025,
      'limit': 10,
      'context_configuration': {
        'sentences_before': 3,
        'sentences_after': 3
      },
      'metadata_filter': "doc.data_type = 'answer'",
      'reranker': {
        'type': 'userfn',
        'user_function': "get('$.score') - ((to_unix_timestamp(now()) / 86400 - get('$.document_metadata.response_date',0)) / 30)" // decrement the score by 1 for each month old
      }
    },
    'generation': {
        'prompt_template': vectaraPrompt,
        'response_language': 'eng',
        'max_used_search_results': 7
    }
  };

  Logger.log(vectaraPrompt);

  var options = {
    'method' : 'post',
    'contentType': 'application/json',
    'headers': headers,
    'payload': JSON.stringify(query)
  };
  var response = JSON.parse(UrlFetchApp.fetch(url, options));
  var summary = response['summary'];
  return summary;
}

function SaveVectaraAnswer(question, answer, answer_cell_id, company) {
  var d = new Date();
  var answer_epoch_day = Math.round(d.getTime() / 1000 / 86400);
  var current_sheet = SpreadsheetApp.getActiveSpreadsheet();
  var response_doc_id = current_sheet.getId() + ":" +answer_cell_id;

  var headers = {
     'x-api-key': vectaraApiKey
  };

  try {
    var url = `https://api.vectara.io/v2/corpora/${vectaraCorpusKey}/documents/${response_doc_id}`;
    var options = {
      'method' : 'delete',
      'contentType': 'application/json',
      'headers': headers
    };
    var response = JSON.parse(UrlFetchApp.fetch(url, options));
  } catch (error) {}

  var document = {
    'id': response_doc_id,
    'type': 'structured',
    "metadata": {
      'response_date': answer_epoch_day,
      'document_url': current_sheet.getUrl(),
      'document_name': current_sheet.getName(),
      'company': company,
      'data_type': 'answer'
    },
    "sections": [
      {
        "text": `Question: ${question}\n\nAnswer:${answer}`
      }
    ]
  }

  var url = `https://api.vectara.io/v2/corpora/${vectaraCorpusKey}/documents`;
  var options = {
    'method' : 'post',
    'contentType': 'application/json',
    'headers': headers,
    'payload': JSON.stringify(document)
  };
  var response = JSON.parse(UrlFetchApp.fetch(url, options));
}