Step 1: Click on the Copy button to copy the code snippet.
Step 2: Paste the copied code into your project’s script editor.
Apps Scripts Blog

Apps Scripts Code

// =====================
// RUN ON SHEET OPEN
//Code.gs
// =====================
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("AI Tools")
    .addItem("Copy & AI Update Spreadsheet", "copyAndAIUpdateSpreadsheet")
    .addToUi();
}

// === MAIN ===
function copyAndAIUpdateSpreadsheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var goalsSheet = ss.getSheetByName("Goals");
  var motive = null;

  if (goalsSheet) {
    motive = goalsSheet.getRange(1, 1).getValue().toString().trim();
    if (motive.split(" ").length >= 15) {
      startAIProcess(motive);
      return;
    }
  }
  // Ask for motive if missing
  showMotiveDialog();
}

// === Ask user for motive ===
function showMotiveDialog() {
  var html = HtmlService.createHtmlOutputFromFile("MotiveForm")
    .setWidth(500)
    .setHeight(320);
  SpreadsheetApp.getUi().showModalDialog(html, "Enter Spreadsheet Goal");
}

// === Save motive and continue ===
function processMotive(motive) {
  motive = motive.trim();
  if (motive.split(" ").length < 15) {
    throw new Error("Goal must have at least 15 words.");
  }
  startAIProcess(motive);
}

// === Start actual AI process ===
function startAIProcess(motive) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var today = new Date();
  var formattedDate = Utilities.formatDate(today, Session.getScriptTimeZone(), "dd MMMM");

  // Make copy
  var newFile = DriveApp.getFileById(ss.getId())
    .makeCopy(ss.getName() + " (AI update on " + formattedDate + ")");
  var newSs = SpreadsheetApp.open(newFile);

  // Ensure Goals sheet
  var goalsSheet = newSs.getSheetByName("Goals");
  if (!goalsSheet) goalsSheet = newSs.insertSheet("Goals");
  goalsSheet.getRange(1, 1).setValue(motive);

  // Show progress sidebar
  var ui = HtmlService.createHtmlOutputFromFile("ProgressUI")
    .setWidth(450)
    .setHeight(300);
  SpreadsheetApp.getUi().showSidebar(ui);

  // Start worker function asynchronously
  PropertiesService.getScriptProperties().setProperty("progress_data", JSON.stringify({
    motive: motive,
    newLink: newSs.getUrl(),
    current: "Starting...",
    percent: 0
  }));
  Utilities.sleep(500); // Give sidebar time to load

  processSheetsWithAI(newSs, motive);
}

// === Process all sheets with AI ===
function processSheetsWithAI(newSs, motive) {
  var sheets = newSs.getSheets();
  var total = sheets.length;
  var done = 0;

  for (var s = 0; s < sheets.length; s++) {
    var sheet = sheets[s];
    if (sheet.getName() === "Goals") continue;

    done++;
    updateProgress(motive, newSs.getUrl(), "Processing tab: " + sheet.getName(), Math.round((done / total) * 100));

    var range = sheet.getDataRange();
    var values = range.getValues();

    for (var r = 1; r < values.length; r++) {
      for (var c = 0; c < values[r].length; c++) {
        var text = values[r][c];
        if (text && text.toString().trim() !== "") {
          try {
            values[r][c] = callChatGPT(motive ,text.toString()
            );
          } catch (err) {
            values[r][c] = "⚠️ AI update failed";
          }
        }
      }
    }

    sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
  }

  updateProgress(motive, newSs.getUrl(), "✅ All tabs processed!", 100);
}

// === Save progress state ===
function updateProgress(motive, newLink, current, percent) {
  PropertiesService.getScriptProperties().setProperty("progress_data", JSON.stringify({
    motive: motive,
    newLink: newLink,
    current: current,
    percent: percent
  }));
}

// === Sidebar polls this ===
function getProgress() {
  var data = PropertiesService.getScriptProperties().getProperty("progress_data");
  return data ? JSON.parse(data) : {};
}

// === MotiveForm.html ===


  Enter Motive Code
  
  
<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <style>
    textarea {
      width: 100%;
      height: 120px;
      font-size: 14px;
      padding: 8px;
    }
    #counter {
      margin-top: 5px;
      font-size: 12px;
      color: gray;
    }
    button {
      margin-top: 10px;
      padding: 8px 16px;
      font-size: 14px;
    }
  </style>
  <script>
    function updateCount() {
      const text = document.getElementById("motive").value.trim();
      const wordCount = text.split(/\s+/).filter(w => w.length > 0).length;
      document.getElementById("counter").innerText =
        wordCount + " words (min 15 required)";
    }
    function submitMotive() {
      const motive = document.getElementById("motive").value;
      google.script.run
        .withFailureHandler(err => alert("Error: " + err.message))
        .processMotive(motive);
      google.script.host.close();
    }
  </script>
</head>
<body>
  <h3>Enter Spreadsheet Motive</h3>
  <textarea id="motive" oninput="updateCount()"></textarea>
  <div id="counter">0 words (min 15 required)</div>
  <button onclick="submitMotive()">Submit</button>
</body>
</html>
  


//chatgpt.gs

// =====================

function callChatGPT( motive, text) {

    if (/https?:\/\/\S+/i.test(text) || /\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}\b/i.test(text)) {

    return text;

  }

  var new_prompt = `

Spreadsheet Goal: ${motive}
Instruction:

- Rewrite the text strictly.

- Correct grammar, spelling, and clarity.

- Keep it concise and professional.

- Do not add intros, explanations, labels, or commentary.

- Output ONLY the improved text.
Text to improve:

"${text}"

`;
  var url = "your-chatGPT-5-end-point";

 var apiKey = "your-api-key-here"; // here your api key

  var payload = {

    "model": "gpt-5",

    "messages": [

      {"role": "system", "content": "You are a helpful AI that improves spreadsheet content."},

      {"role": "user", "content": new_prompt}

    ],

    "max_tokens": 250

  };

  var options = {

    "method": "post",

    "headers": {

      "Authorization": "Bearer " + apiKey,

      "Content-Type": "application/json"

    },

    "payload": JSON.stringify(payload),

    "muteHttpExceptions": true

  };

  var response = UrlFetchApp.fetch(url, options);

  var data = JSON.parse(response.getContentText());

  if (data.error) {

    throw new Error(data.error.message);

  }

  return data.choices[0].message.content.trim();

}

// === ProgressUI.html ===

Progress UI Code
  

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>
      body {
        font-family: Arial, sans-serif;
        padding: 15px;
        text-align: center;
      }
      .box {
        border: 1px solid #ccc;
        padding: 15px;
        border-radius: 8px;
        box-shadow: 0 2px 6px rgba(0,0,0,0.2);
      }
      .progress {
        width: 100%;
        background: #eee;
        border-radius: 6px;
        margin-top: 15px;
      }
      .bar {
        width: 0%;
        height: 20px;
        background: #4caf50;
        border-radius: 6px;
        transition: width 0.3s;
      }
    </style>
  </head>
  <body>
    <div class="box">
      <h3>AI Update Progress</h3>
      <p id="goal"></p>
      <p><b>New Spreadsheet:</b> <a id="newLink" href="#" target="_blank">Opening...</a></p>
      <p id="current"></p>
      <div class="progress"><div class="bar" id="bar"></div></div>
    </div>

    <script>
      function poll() {
        google.script.run.withSuccessHandler(function(data) {
          if (!data) return;
          document.getElementById("goal").innerText = "Goal: " + data.motive;
          document.getElementById("newLink").href = data.newLink;
          document.getElementById("newLink").innerText = data.newLink;
          document.getElementById("current").innerText = data.current;
          document.getElementById("bar").style.width = data.percent + "%";
        }).getProgress();
      }
      setInterval(poll, 1500);
      poll();
    </script>
  </body>
</html>
AI Spreadsheet Enhancer

AI Spreadsheet Enhancer

Apps Scripts Blog

Apps Scripts Code

Enter Motive Code

Progress UI Code
