AppsScript_Main_Code

// Function to call OpenAI API from Google Sheets
function callOpenAI(parameterName, userInputText) {
  var scriptProperties = PropertiesService.getScriptProperties();
  var apiKey = scriptProperties.getProperty('OPENAI_API_KEY');

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SystemPrompts');
  var range = sheet.getDataRange();
  var values = range.getValues();

  var model = '';
  var systemPrompt = '';

  for (var i = 1; i < values.length; i++) {
    var sheetParameterName = values[i][0] ? values[i][0].trim() : '';
    if (sheetParameterName === String(parameterName).trim()) {
      model = values[i][1] ? values[i][1].trim() : '';
      systemPrompt = values[i][2] ? values[i][2].trim() : '';
      break;
    }
  }

  if (!model || !systemPrompt) {
    throw new Error('Parameter name not found or incomplete in the SystemPrompts sheet. Searched for: ' + parameterName.trim());
  }

  var apiUrl = 'https://api.openai.com/v1/chat/completions';

  var payload = {
    model: model,
    messages: [
      { role: "system", content: systemPrompt },
      { role: "user", content: userInputText }
    ],
    max_tokens: 1000,
    temperature: 1
  };

  var options = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      Authorization: 'Bearer ' + apiKey
    },
    payload: JSON.stringify(payload)
  };

  var response = UrlFetchApp.fetch(apiUrl, options);
  var json = JSON.parse(response.getContentText());

  if (!json.choices || json.choices.length === 0 || !json.choices[0].message || !json.choices[0].message.content) {
    throw new Error('OpenAI response does not contain the expected message content.');
  }

  var content = json.choices[0].message.content.trim();
  return content;
}

// Function to create custom menu in Google Sheets
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Revise API Response')
    .addItem('Re-send request to OpenAI', 'reviseResponse')
    .addToUi();
}

// Function to handle revising the response for multiple cells
function reviseResponse() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var ranges = sheet.getActiveRangeList().getRanges();

  ranges.forEach(function(range) {
    var cells = range.getValues();
    var formulas = range.getFormulas();
    var startRow = range.getRow();
    var startColumn = range.getColumn();

    for (var i = 0; i < cells.length; i++) {
      for (var j = 0; j < cells[i].length; j++) {
        var cell = sheet.getRange(startRow + i, startColumn + j);
        var formula = formulas[i][j];
        if (formula) {
          var parameterName = extractParameterNameFromFormula(formula);
          var userInputText = cells[i][j];

          var originalPrompt = getSystemPrompt(parameterName);
          var originalResponse = userInputText;

          var revisedPrompt = `
            Here is the original user's system prompt: ${originalPrompt}
            Based on this input: ${userInputText}
            Here is the response that they got: ${originalResponse}
            The user is not satisfied with the response, please revise to make sure it complies with the requirements of the request, including the requested schema.
            Please provide only the revised structured response in the specified format without any additional explanation or text.
          `;

          var newValue = callOpenAI(parameterName, revisedPrompt);

          cell.setValue(newValue);
        }
      }
    }
  });
}

// Helper function to extract the parameter name from the formula
function extractParameterNameFromFormula(formula) {
  var matches = formula.match(/callOpenAI\("([^"]+)",/i);
  if (matches) {
    return matches[1];
  }
  throw new Error('Parameter name not found in the formula: ' + formula);
}

// Helper function to get the system prompt based on the parameter name
function getSystemPrompt(parameterName) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SystemPrompts');
  var range = sheet.getDataRange();
  var values = range.getValues();

  for (var i = 1; i < values.length; i++) {
    var sheetParameterName = values[i][0] ? values[i][0].trim() : '';
    if (sheetParameterName === String(parameterName).trim()) {
      return values[i][2] ? values[i][2].trim() : '';
    }
  }
  throw new Error('System prompt not found for parameter name: ' + parameterName.trim());
}