Apps Scripts Code

Copy Code

var add_prompt;
var lastrow;
var nextrow;
function onOpen() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  createSheetIfNotExist(sheet, 'pr1');
  createSheetIfNotExist(sheet, 'pr2');
    createSheetIfNotExist(sheet, 'pr3');
}
function atEdit(e) {
  var range = e.range;
  var sheet = range.getSheet();
  var editedRow = range.getRow();
  var editedColumn = range.getColumn();
  var triggerCellRow = 2; // Row number of the cell
  var triggerCellColumn = 2; // Column number of the cell (1 for Column A, 2 for B, etc.)
  if (editedRow === triggerCellRow && editedColumn === triggerCellColumn) {
    //SpreadsheetApp.getUi().alert("Please wait for new response");
    mainWorkflow();
  }
}




function callOpenAI(show_error=1) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  const pr1Sheet = sheet.getSheetByName('pr1');
  const pr3Sheet = sheet.getSheetByName('pr3');
  const pr2Sheet = sheet.getSheetByName('pr2');
  let prompt = pr1Sheet.getRange('B2').getValue();
   lastRow = pr1Sheet.getLastRow();
   nextrow =lastRow+1;
   Logger.log('nextrow is '+nextrow);
pr1Sheet.getRange(nextrow, 2).setValue(prompt);
pr1Sheet.getRange(nextrow, 1).setValue(getFormattedDateTime());
prompt =add_prompt(sheet,prompt);
Logger.log("prompt: " + prompt);
let worker = pr1Sheet.getRange('A2').getValue();
Logger.log("worker: " + worker);
let end_point = pr3Sheet.getRange('B2').getValue();
let end_point2 = pr3Sheet.getRange('B3').getValue(); // Note: This seems to be the same as end_point
Logger.log("end_point2: " + end_point2);
let model = pr3Sheet.getRange('A2').getValue();
let model1 = pr3Sheet.getRange('A3').getValue();
Logger.log("model1: " + model1);
if(worker ===2){
end_point =end_point2;
}
if(worker ===2){
model =model1;
}
Logger.log("model: " + model);
Logger.log("end_point: " + end_point);
//showBusySign();
let api_key = pr3Sheet.getRange('C2').getValue();
  var payload = {
    "model": model,
    "messages": [
      {
        "role": "system",
        "content": "I am your lovely assitant",
      },
      {
        "role": "user",
        "content": prompt
      }
    ]
  };
if(worker ===2){
payload = {
    "model": model,
    "prompt": prompt,
    "n": 1,
    "size": "1024x1024",
  };
}
  var options = {
    'method' : 'post',
    'contentType': 'application/json',
    'headers': {
      'Authorization': 'Bearer ' + api_key
    },
    'payload' : JSON.stringify(payload),
    'muteHttpExceptions': show_error === 1
  };
        pr1Sheet.getRange(nextrow, 2).setValue(JSON.stringify(payload));
  try {
    var response = UrlFetchApp.fetch(end_point, options);
    Logger.log(response.getContentText());
    return response;
  } catch (e) {
    if (show_error === 1) {
      Logger.log("Error: " + e);
    }
  }
}




function createSheetIfNotExist(sheet, sheetName) {
  let targetSheet = sheet.getSheetByName(sheetName);
  if (!targetSheet) {
    sheet.insertSheet(sheetName);
    if (sheetName === 'pr1') {
      sheet.getSheetByName(sheetName).appendRow(['Type','Prompt', 'Response','Request']);
            sheet.getSheetByName(sheetName).appendRow([1,'Need simple Python Code for addition', 'Response']);
            sheet.getSheetByName(sheetName).appendRow([2,'Dog and cat sitting on sofa']);
    }
    if (sheetName === 'pr2') {
      sheet.getSheetByName(sheetName).appendRow(['Prompt Type',"Default"]);
            sheet.getSheetByName(sheetName).appendRow(['2','text',"2"]);
            sheet.getSheetByName(sheetName).appendRow(['3','content-seo',"Provide SEO friendly content"]);
            sheet.getSheetByName(sheetName).appendRow(['4','code',"No Comments and explanation required , response only in function and full code"]);
           sheet.getSheetByName(sheetName).appendRow(['5','slug','SEO friendly slug for social media with promtion with 3-4 lines message and 5 hashtags in last']);
            sheet.getSheetByName(sheetName).appendRow(['6','image']);
           sheet.getSheetByName(sheetName).appendRow(['7','php',"No Comments and explanation required , response only single function"]);
    }
     if (sheetName === 'pr3') {
      sheet.getSheetByName(sheetName).appendRow(['Models','End Point','Api Key']);      
      sheet.getSheetByName(sheetName).appendRow(['gpt-4-turbo-preview','https://api.openai.com/v1/chat/completions','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']);
     sheet.getSheetByName(sheetName).appendRow(['dall-e-3','https://api.openai.com/v1/images/generations']);
      sheet.getSheetByName(sheetName).appendRow(['gpt-3.5-turbo','https://api.openai.com/v1/chat/completions']);
      sheet.getSheetByName(sheetName).appendRow(['gpt-4','https://api.openai.com/v1/chat/completions']);
      sheet.getSheetByName(sheetName).appendRow(['text-embedding-3-small','https://api.openai.com/v1/chat/completions']);
    }
    var desiredColumnWidth1 = 120; // Width in pixels for the first column
    var desiredColumnWidth2 = 300; // Width in pixels for the second column
    var desiredColumn = 1000; // Width in pixels for the second column
    sheet.setColumnWidth(1, desiredColumnWidth1); // Adjusting width of the first column
    sheet.setColumnWidth(2, desiredColumnWidth2); // Adjusting width of the second column
    sheet.setColumnWidth(3, desiredColumn); // Adjusting width of the second column
    sheet.setRowHeight(2, desiredColumnWidth1);
    var firstRowRange = sheet.getRange("1:1"); // This selects the entire first row
    firstRowRange.setFontWeight("bold");
    var range = sheet.getRange("1:3"); // Selects the entire column from row 1
    range.setWrap(true);
  }
}
function add_prompt(sheet,prompt){
  const pr2Sheet = sheet.getSheetByName('pr2');
  let add_prompt = pr2Sheet.getRange('C2').getValue();
  if(add_prompt ===1){
    return prompt;
  }
let new_prompt = "C"+add_prompt;
Logger.log("Prompt type:"+new_prompt);
new_prompt = pr2Sheet.getRange(new_prompt).getValue();
let final_prompt = prompt +" and "+new_prompt;
return final_prompt;
}




function mainWorkflow() {
    const sheet = SpreadsheetApp.getActiveSpreadsheet();
  const pr1Sheet = sheet.getSheetByName('pr1');
  const pr2Sheet = sheet.getSheetByName('pr2');
  let response = callOpenAI();
  if(!response){
        SpreadsheetApp.getUi().alert("ChatGPT not responding , please check errors");
        return false;
  }
    Logger.log(response);              
    response_data = JSON.parse(response);
    let content='';
    if( response_data['choices'] ){
     content = response_data['choices'][0]['message']['content'];    
    // content =  fetchFunctionsFromResponse(content);
Logger.log("New Content "+content);
    }
    else{
 content= response_data['data'][0]['url'];
    }
    pr1Sheet.getRange(nextrow, 3).setValue(content);
    selectLastRow();  
}


function selectLastRow() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('pr1'); // Replace with your actual sheet name
    var lastColumn = sheet.getLastColumn(); // Get the last column with content
    var range = sheet.getRange(nextrow, 1, 1, lastColumn); // Selects the entire last row
    sheet.setActiveRange(range); // Set the active range to the last row
}




function appendCodeFromSheet() {
  try {
    // Specify the sheet name and cell where your code is located
    let sheetName = "pr1";
    let cellAddress = "B2"; // Change this to the specific cell where your code is
    let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
    let code = sheet.getRange(cellAddress).getValue();
    let includedCode = code;  
    let currentScript = ScriptApp.getProject();
    let currentContent = currentScript.getContent();
    let appendedContent = currentContent + "\n" + includedCode;
    currentScript.setContent(appendedContent);  
    Logger.log("Code appended successfully.");
  } catch (error) {
    Logger.log("Error appending code: " + error.toString());
  }
}
function sendToPrompt(currentFileCode, errorCode) {
  // Example using a hypothetical prompt function
  return prompt("Generate fixed code", "Current code:\n" + currentFileCode + "\n\nError code:\n" + errorCode);
}
function sendEmail(count) {
  var emailAddress = 'ashish@outrightcrm.com';  // Replace with your email address
  var subject = 'Access Revocation Summary';
  var message = 'Access has been revoked for ' + count + ' users.';
  MailApp.sendEmail(emailAddress, subject, message);
}
function getFormattedDateTime() {
  var now = new Date();
  var year = now.getFullYear();
  var month = now.getMonth() + 1; // getMonth() returns 0-11
  var day = now.getDate();
  var hour = now.getHours();
  var minute = now.getMinutes();
  var second = now.getSeconds();
  return year + '-' + month + '-' + day + ' ' + hour + ':' + minute + ':' + second;
}


function showBusySign() {
  var html = HtmlService.createHtmlOutputFromFile('busy')
    .setWidth(300)
    .setHeight(300);
  SpreadsheetApp.getUi().showModalDialog(html, 'Loading...');
}


function fetchFunctionsFromResponse(response) {
  var functions = response.match(/function\s+(\w+)\s*\(/g);


  if (functions) {
    functions = functions.map(function (match) {
      return match.replace(/function\s+(\w+)\s*\(/, '$1');
    });
  }
Logger.log(functions);
return response;
}