// This script by Danny Richman is free to use on a Creative Commons License @DannyRichman

function onOpen(){

// This function add a menu option to covert formulas to plain text. 

var ui = SpreadsheetApp.getUi();
ui.createMenu('Save Values')
.addItem('Save', 'saveAsFixedValue')
.addToUi();
}

function saveAsFixedValue(){

// This function prevents the API being called each time the sheet is refreshed or updated to save your API credits. 

var ss = SpreadsheetApp.getActiveSheet()
var data = ss.getDataRange().getValues()
ss.getRange(1,1,data.length,data[0].length).setValues(data)

}

function GETFORMULA(val) {
// main function to generate formula and write value to sheet
var ss = SpreadsheetApp.getActiveSheet()

// Exit function if no description provided
if (val == "" ) {
    result = ""
    return result;
}


// get API key from settings sheet
var setsh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Settings");
var apiKey = setsh.getRange(2,1).getValue()

// configure the API request to OpenAI
var data = {

   "prompt": "Create a Google Sheet formula to count the number of rows in cells A2:B20 with a value greater than 20\n=COUNTIF(A2:B20,\">20\")\n\nCreate a Google Sheet formula to split the text in cell A2 where the \"/\" symbol appears into two columns\n=SPLIT(A2,\"/\")\n\nCreate a Google Sheet formula which takes the date range from cells A2 to A18 and the values from cells B2 to B18 and forecasts those values five years ahead\n=FORECAST.LINEAR(A2:A18,B2:B18,5)\n\n" + val + "\n",


    "temperature": 0.70,
    "max_tokens": 64,
    "top_p": 1,
    "best_of": 4,
    "frequency_penalty": 0,
    "presence_penalty": 0,
    "stop": ["\n\n"]
  };
   var options = {
    'method' : 'post',
    'contentType': 'application/json',
    'payload' : JSON.stringify(data),
    'headers': {
      Authorization: 'Bearer ' + apiKey,
    },
  };
  
  var response = UrlFetchApp.fetch(
 
 'https://api.openai.com/v1/engines/text-davinci-001/completions',
    options,
  );

 // Send the API request 
 var result = JSON.parse(response.getContentText())['choices'][0]['text']
 Logger.log(result)

  return result
}