Magic of ChatGPT 4 and Dall-E 3 with Free Google Apps Script to automate your Google sheet

var add_prompt;
var lastrow;
var nextrow;

// ------------------------------
// Trigger on spreadsheet open
// ------------------------------
function onOpen() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  createSheetIfNotExist(sheet, 'pr1');
  createSheetIfNotExist(sheet, 'pr2');
  createSheetIfNotExist(sheet, 'pr3');
}

// ------------------------------
// Trigger on edit
// ------------------------------
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 trigger cell
  var triggerCellColumn = 2; // Column number of the trigger cell (B)

  if (editedRow === triggerCellRow && editedColumn === triggerCellColumn) {
    // SpreadsheetApp.getUi().alert("Please wait for new response");
    mainWorkflow();
  }
}

// ------------------------------
// Call OpenAI API
// ------------------------------
function callOpenAI(show_error = 1) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  const pr1Sheet = sheet.getSheetByName('pr1');
  const pr2Sheet = sheet.getSheetByName('pr2');
  const pr3Sheet = sheet.getSheetByName('pr3');

  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();
  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;
    model = model1;
  }

  Logger.log("model: " + model);
  Logger.log("end_point: " + end_point);

  // Build payload
  let api_key = pr3Sheet.getRange('C2').getValue();
  var payload = {
    "model": model,
    "messages": [
      { "role": "system", "content": "I am your lovely assistant" },
      { "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);
    }
  }
}

// ------------------------------
// Create sheets if not exist
// ------------------------------
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 promotion 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']);
    }

    // Set column widths and wrap
    sheet.setColumnWidth(1, 120);
    sheet.setColumnWidth(2, 300);
    sheet.setColumnWidth(3, 1000);
    sheet.setRowHeight(2, 120);
    sheet.getRange("1:1").setFontWeight("bold");
    sheet.getRange("1:3").setWrap(true);
  }
}

// ------------------------------
// Add prompt from pr2 sheet
// ------------------------------
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;
}

// ------------------------------
// Main workflow function
// ------------------------------
function mainWorkflow() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  const pr1Sheet = sheet.getSheetByName('pr1');

  let response = callOpenAI();

  if (!response) {
    SpreadsheetApp.getUi().alert("ChatGPT not responding, please check errors");
    return false;
  }

  Logger.log(response);

  let response_data = JSON.parse(response);
  let content = '';

  if (response_data['choices']) {
    content = response_data['choices'][0]['message']['content'];
    Logger.log("New Content " + content);
  } else {
    content = response_data['data'][0]['url'];
  }

  pr1Sheet.getRange(nextrow, 3).setValue(content);
  selectLastRow();
}

// ------------------------------
// Select the last row
// ------------------------------
function selectLastRow() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('pr1');
  var lastColumn = sheet.getLastColumn();
  var range = sheet.getRange(nextrow, 1, 1, lastColumn);
  sheet.setActiveRange(range);
}

// ------------------------------
// Append code from sheet to script
// ------------------------------
function appendCodeFromSheet() {
  try {
    let sheetName = "pr1";
    let cellAddress = "B2";
    let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
    let code = sheet.getRange(cellAddress).getValue();

    let currentScript = ScriptApp.getProject();
    let currentContent = currentScript.getContent();
    let appendedContent = currentContent + "\n" + code;

    currentScript.setContent(appendedContent);
    Logger.log("Code appended successfully.");
  } catch (error) {
    Logger.log("Error appending code: " + error.toString());
  }
}

// ------------------------------
// Get formatted date and time
// ------------------------------
function getFormattedDateTime() {
  var now = new Date();
  return now.getFullYear() + '-' +
         (now.getMonth() + 1) + '-' +
         now.getDate() + ' ' +
         now.getHours() + ':' +
         now.getMinutes() + ':' +
         now.getSeconds();
}

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

// ------------------------------
// Extract function names from response
// ------------------------------
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;
}

// ------------------------------
// Dummy function for sending to prompt (example)
// ------------------------------
function sendToPrompt(currentFileCode, errorCode) {
  return prompt("Generate fixed code", "Current code:\n" + currentFileCode + "\n\nError code:\n" + errorCode);
}

// ------------------------------
// Send email (already exists in your previous script)
// ------------------------------
function sendEmail(count) {
  var emailAddress = 'ashish@outrightcrm.com';
  var subject = 'Access Revocation Summary';
  var message = 'Access has been revoked for ' + count + ' users.';
  MailApp.sendEmail(emailAddress, subject, message);
}