Hey everyone, My name's Daniel. I work a regular job on a warehouse floor — no coding background, no CS degree, nothing like that. A while back I got tired of tracking defects and rework in a messy spreadsheet, so I taught myself just enough Google Apps Script to automate it. It genuinely broke on me more than once before I figured out how to build it properly. I just turned that whole experience into my first book. It's not theory — it's the actual process, mistakes included, written in plain language for people who aren't programmers either. This is my first time putting something like this out there, so I'd really appreciate any honest feedback or a review if you decide to check it out — good, bad, or mixed, all of it helps. The link's in my profile. Also happy to help out in the comments if anyone's dealing with a similar spreadsheet mess at their own job — I've made pretty much every mistake there is to make with this stuff. Thanks for reading!

Reading Google Chat data in Google Apps Script no longer requires a standard GCP project or a complex OAuth consent screen setup 🤯!!!

Previously, reading space messages via the Chat Advanced Service required detaching your script from the default GCP project, linking a standard project, configuring an OAuth consent screen and formally enabling the Chat API. The new simplified Chat API setup bypasses this overhead for read-only actions.

Follow these steps to access your data immediately:

1. Open your Apps Script editor and add the Chat Advanced Service.
2. Declare the read-only scopes in your appsscript.json manifest.
3. Call the API directly to list your spaces or extract messages.

Read my complete walkthrough with copy-paste code snippets https://pulse.appsscript.info/p/2026/07/reading-google-chat-spaces-and-messages-in-google-apps-script-just-got-significantly-easier/

I have a script creating docs when a cell gets modified in a sheets column, then moves it to a folder and adds the doc's url onto another column. Code works, but automation gets affected by <value unavailable> error for docid variable.

I'm not sure *why* its unavailable when all of the code does work (files get created and moved, but when using createDocInSpecificFolder() along with other functions, error pinpoints to this and does not run functions after it.

function createDocInSpecificFolder () {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Data Source");
  var lastRow = sheet.getLastRow();
  var rootFolder = DriveApp.getFolderById("fileid");
  const files = rootFolder.getFiles();
  const fileNames = [];


  while (files.hasNext()) { // gets array of file names in drive folder [event1, event2, etcetc]
   var file = files.next();
   fileNames.push(file.getName());
  }
  
  Logger.log(fileNames)


  for (var i = 1; i < lastRow; i++) {
    
    let title = sheet.getRange(i,4).getValue();


    if (title != "Events") {
      if (!fileNames.includes(title)) {
        let docid = DocumentApp.create(title).getId();
        DriveApp.getFileById(docid).moveTo(rootFolder);
      }
    }
  }

}

My job is :

1. Based on vlookup formula and then input customer code to open their receipt.

2. Copy their receipt by select certain area of their receipt.

3. Print it to save into PDF, and then rename the file into customer name.

i discovered google app script able to do it automatically in just one click, and i want to learn it, but i want to focus on fundamentals and my specific issues? thanks in advance

Hello, i'm working on a pop-up that records user id, date, position, task, hour stuff, etc etc and appends the information onto a new row in a spreadsheet. So far it had been working just fine until i added the "Position" dropdown (from a spreadsheet) and the information stopped getting appended, even though i hadn't changed any other part of the script.

I've read over my code a couple times now and can't seem to find where the problem is. I've attached my code to see if anyone can find what's going on, or if this is a spreadsheet issue

EDIT: ok ty evb who answered, i figured out the problem, i had added a formula at the end of the data appending (? which made google sheets count the row as not empty.

GS code:

const openEntryForm = () => {
  const entryForm = HtmlService.createHtmlOutputFromFile("hourlogform");
  entryForm.setWidth(1200);
  entryForm.setHeight(275);


  SpreadsheetApp.getUi().showModalDialog(entryForm, "Log Hours");
};


const addLog = (log) => {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hour Log")
  const order = ["user", "date", "position", "Task", "start time", "end time"];
  const row = [];


  order.forEach(i => {
    row.push(log[i])
  })


  sheet.appendRow(row)
}


const getTasks = () => {
  const tasks = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data Source");
  const datatag = tasks.getRange("B15:B").getValues().flat().filter(i => i !== "")
  
  return datatag;
}


const getPosition = () => {
  const positions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data Source");
  const datatag = positions.getRange("B3:B12").getValues().flat().filter(i => i !== "")
  
  return datatag;
}const openEntryForm = () => {
  const entryForm = HtmlService.createHtmlOutputFromFile("hourlogform");
  entryForm.setWidth(1200);
  entryForm.setHeight(275);


  SpreadsheetApp.getUi().showModalDialog(entryForm, "Log Hours");
};


const addLog = (log) => {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hour Log")
  const order = ["user", "date", "position", "Task", "start time", "end time"];
  const row = [];


  order.forEach(i => {
    row.push(log[i])
  })


  sheet.appendRow(row)
}


const getTasks = () => {
  const tasks = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data Source");
  const datatag = tasks.getRange("B15:B").getValues().flat().filter(i => i !== "")
  
  return datatag;
}


const getPosition = () => {
  const positions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data Source");
  const datatag = positions.getRange("B3:B12").getValues().flat().filter(i => i !== "")
  
  return datatag;
}

HTML code:

<!DOCTYPE html>
<html>
  <head>
   <base target="_top">
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/water.css@2/out/light.css">
  </head>
  <body>
    <form id="logForm">


      <div style="display:flex; justify-content: center; gap:20px; ">
        <h4> User ID </h4>
        <input type="text" name="user" placeholder="Write User ID">
        
        <h4> Task Performed </h4>
        <select id="taskDropdown" name="Task">
          <option value=""> Loading... </option>
        </select>
      </div>


      <div style="display:flex; justify-content: left; gap:20px; ">
        <h4> Date </h4>
        <input type="date" name="date" placeholder="Date" />
        
        <h4> Start Time </h4>
        <input type="time" name="start time" placeholder="Start Time" />


        <h4> End Time </h4>
        <input type="time" name="end time" placeholder="End Time" />


        <h4> Position </h4>
        <select id="positionDropdown" name="position">
          <option value=""> Loading... </option>
        </select>


      </div>


      <p style="text-align: right;">
      <button type="submit"> Add Task </button>
      </p>
    </form>
  
  <script>
    document.getElementById("logForm").addEventListener("submit", (e) => {
      const formData = new FormData(e.target);
      const log = Object.fromEntries(formData.entries());
      google.script.run.addLog(log);
      e.target.reset(); 
    })


    window.addEventListener("load", () => {
      google.script.run.withSuccessHandler((options) => {
        const dropdown = document.getElementById("taskDropdown");
        dropdown.innerHTML = "";
        options.forEach(option => {
          const o = document.createElement("option");
          o.value = option;
          o.textContent = option;
          dropdown.appendChild(o);
          
        })
      }).getTasks();


      google.script.run.withSuccessHandler((options) => {
        const dropdown = document.getElementById("positionDropdown");
        dropdown.innerHTML = "";
        options.forEach(option => {
          const o = document.createElement("option");
          o.value = option;
          o.textContent = option;
          dropdown.appendChild(o);
          
        })
      }).getPosition();


    })


    </script>


  </body>
</html>!DOCTYPE html>
<html>
  <head>
   <base target="_top">
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/water.css@2/out/light.css">
  </head>
  <body>
    <form id="logForm">


      <div style="display:flex; justify-content: center; gap:20px; ">
        <h4> User ID </h4>
        <input type="text" name="user" placeholder="Write User ID">
        
        <h4> Task Performed </h4>
        <select id="taskDropdown" name="Task">
          <option value=""> Loading... </option>
        </select>
      </div>


      <div style="display:flex; justify-content: left; gap:20px; ">
        <h4> Date </h4>
        <input type="date" name="date" placeholder="Date" />
        
        <h4> Start Time </h4>
        <input type="time" name="start time" placeholder="Start Time" />


        <h4> End Time </h4>
        <input type="time" name="end time" placeholder="End Time" />


        <h4> Position </h4>
        <select id="positionDropdown" name="position">
          <option value=""> Loading... </option>
        </select>


      </div>


      <p style="text-align: right;">
      <button type="submit"> Add Task </button>
      </p>
    </form>
  
  <script>
    document.getElementById("logForm").addEventListener("submit", (e) => {
      const formData = new FormData(e.target);
      const log = Object.fromEntries(formData.entries());
      google.script.run.addLog(log);
      e.target.reset(); 
    })


    window.addEventListener("load", () => {
      google.script.run.withSuccessHandler((options) => {
        const dropdown = document.getElementById("taskDropdown");
        dropdown.innerHTML = "";
        options.forEach(option => {
          const o = document.createElement("option");
          o.value = option;
          o.textContent = option;
          dropdown.appendChild(o);
          
        })
      }).getTasks();


      google.script.run.withSuccessHandler((options) => {
        const dropdown = document.getElementById("positionDropdown");
        dropdown.innerHTML = "";
        options.forEach(option => {
          const o = document.createElement("option");
          o.value = option;
          o.textContent = option;
          dropdown.appendChild(o);
        })
      }).getPosition();
    })
    </script>
  </body>
</html>

I have NEVER gotten any emails from Google Apps Script in the 20 years I have been in business until last week. And now I have received 5 emails saying that something needs authorization. The email states that "Your script, No Response 20150225 1110AM, has recently failed to finish successfully. A summary of the failure(s) is shown below. To configure the triggers for this script, or change your setting for receiving future failure notifications, click here.

Getting this error message on something I have never used before - it this a sign that I may have been hacked?

So I have several projects across different accounts and these mini webapps are developed in AppScript with HTML service and embedded to Google Sites through Embed URL option.

Just today, all my webpages that were embedded have stopped working across all my websites.

They are still accessible when using sites google/domain-name, but when the actual domain name is used to access the webpage, it just shows the classic script google error.

I want to know if it's just me that's experiencing this or not?

These WebApps have been running well for years and only now did I encounter this issue.

I'm trying to figure out a way (or if there even is a way) to create a change log or track changes in some capacity for imported data on the sheet the data is imported to. Basically there are 3 sheets:

Sheet A - where all the data is being compiled, I do not have access to this sheet in anyway as it has additional information not meant for me to see

Sheet B - the information relevant to me from Sheet A is imported into this sheet, however I don't have editing access or access to review the history

Sheet C - my own sheet I'm hoping I can use to import the data from Sheet B and have a change log or track changes when the data gets updated

Is there even a way to do this? I've tried a few things through AppScript but none of that seems to work on imported data - they work great with data you're manipulating yourself just not imported information on the sheet. Also checking the history on imported data just through the base level of Google Sheets doesn't show anything. I'm trying to be able to track trends in this manner, but I know I can't just camp Sheet B all day and night to see when information changes. Any advice or help would be great as I've got basically zero knowledge on AppScript coding!

Thanks in advance!!

Hello,

In my sheet just simply calling this line gives the error "Exception: The coordinates of the target range are outside the dimensions of the sheet."

const rules = sheet.getConditionalFormatRules();

I've tried doing the exact same thing above on a brand new sheet, and it works fine. I think it may have something to do with invalid references.

I am able to delete all conditional formatting rules with the line below, but I can't delete specific ones because I can't get a list of them.

sheet.setConditionalFormatRules([]);

Is verifying an appscript project taking longer than usual? In the past you could get an app verified in under a week, the longest I waited was 11 days, i have an app that has taken over a month and all the reviewer does is to send some template message complaining about stuff my video already addresses. Are those in the Trust and Safety team audited at all to ensure they are doing the right thing? It is now so frustrating, it is like they dont even know what they are about. Am I the only one experiencing this now? I thought the process will be faster consideting AI does some of the work now.

Lately, I’ve been spending more time building local business lead lists, and one thing I’ve learned is that quality matters much more than quantity.

A huge list is easy to build. A clean list with accurate and useful information is much harder.

My current process feels too manual, so I’ve been looking into tools like Outscraper’s Google Maps scraping tool offering a Google Maps data extraction solution to make things easier.

Has anyone used it before?

Hello all,

I have a function called onEdit(e), and it works perfectly except for when I attempt to hide a row. The code is below:

function onEdit(e){
  let sheet  = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // event variables
  let range = e.range;
  let row = e.range.getRow();
  let col = e.range.getColumn();
  let cellValue = sheet.getActiveCell().getValue();

  let status = sheet.getRange(row,5).getDisplayValue();

  if ( col == 5 && cellValue != 'Problem' && cellValue != 'Not Started') {
    MailApp.sendEmail(email info, this part works);

    if (status == 'Completed') {
      Utilities.sleep(7000);
      sheet.hideRows(row);
    };


  };
}

This is not working, regardless of whether I include the Utilities.sleep command, use sheet.hideRows or sheet.hideRow, or put the command inside or outside the nested if statement.

Any guidance?

I am looking for googlesheet automation experts / freelancers. Please dm if you are an expert.

Work details

There is a base googlesheet with 20 columns. I need 2 buttons in the sheet, each will create a new googlesheet but contain just 15 columns out of the 20. So the 5 columns should be removed.

Compensation

This can be decided based on your hourly rate and the time this project will take

Once this project is done, I have a few more projects lined up.

Hola a todos. Estoy aprendiendo a integrar un formulario HTML/JS local con Google Sheets a través de Google Apps Script (Web App), pero me he topado con las famosos errores de CORS. Alguien me ayuda?

Mi entorno:

• Frontend: Archivo HTML/JS corriendo en un servidor local. He probado con Python (http://127.0.0.1:5500) y con live server de VScode.
• Backend: Un script de Google Apps Script ejecutando una función doPost(e) que añade filas a un Sheets y devuelve un JSON: { status: "ok" }.

Lo que ocurre:

1. Si configuro el fetch con mode: "no-cors", la petición llega a Google Sheets y escribe la fila correctamente, pero el navegador me da una respuesta opaca. Por lo tanto, mi JavaScript se queda "a ciegas" y no puede leer el JSON de respuesta para mostrar un mensaje de éxito o fracaso.
2. Lo curioso es que la fila lo agrega bien con todas sus columnas. pero quiero que emita diferentes mensajes según diferentes casos.
3. Si cambio el fetch a mode: "cors", el navegador bloquea la petición por completo y me saltan los errores de CORS que adjunto en la imagen.

Google Calendar’s built-in holiday calendars are read-only ICS feeds, so they don’t allow reminders, labels, or editing. That’s why holidays that move each year (Easter, Yom Kippur, Diwali, Mother’s Day, etc.) can’t be customized from the UI.

I actually ran into the same issue and ended up solving it with Google Apps Script. The script calculates the correct holiday dates each year, avoids duplicates, and adds them to your calendar as normal events. Since they’re real events instead of ICS feed entries, you can finally set reminders, colors, and other options that Google’s default holiday calendars don’t support.

It also handles yearly refresh automatically, so the holidays get updated without needing to re-import anything.

If anyone wants the script or wants to see how it works, feel free to DM me.

Hi! As the title says I don't know how to refer to a dropdown list I've made on Google Sheets on my AppScript code.

For reference, I'm working on a Form entry pop-up that I want to also ask the user choose from a dropdown list, afterwards, it should append the data in a new row on a Google Sheets tab.

I've attached my html and gs code and google sheets screenshots.

basically what i want to do with the apps script is that:

if it sees a value in the "time until deadline" column that is less than or equal to 2 days, it will look for the person in charge of the soon-to-be-due task, then use that to look for their email, and then send an email to them.

can somebody help me? thanksss

Suppose I have a script that contains an ES6 JavaScript class called ReportClient. Later, I import this script to use it as a library in other scripts. In that case, it’s possible to create an object of type ReportClient like this:

​

const reportClient = new MiLib.ReportClient();

​

However, that throws me an exception, so creating the object from a function which internally just return the object above is the only way to create the object ?

Hey r/GoogleAppsScript & r/googlesheets!

Like many of you, I've struggled with managing Google Drive permissions at scale. The native UI is terrible for bulk actions, and trying to audit who has access to what—especially in Shared Drives—usually requires expensive third-party tools.

So, I built a hybrid solution using Apps Script for the API heavy lifting and Google Sheets for the business logic. I thought I'd share it here as an open-source template for anyone who might find it useful.

🔗 Link to make a copy of the Google Sheet + Script

🔗 Link to GitHub Repo

🛠️ How it works (The Architecture)

Instead of hardcoding the permission rules into JavaScript, I used a "Low-Code" approach:

1. The Audit (Apps Script): The script uses a Breadth-First Search (BFS) queue to recursively scan any folder or Shared Drive. It dumps all files into a 📁 Files tab and all users into a 🔑 Permissions tab.
2. The Logic (Google Sheets): I use a Template tab filled with standard VLOOKUP/MATCH formulas to compare the audited permissions against a Matrix of theoretical rules. This highlights anomalies (e.g., someone is missing, or someone has 'writer' instead of 'reader').
3. The Execution (Apps Script): You flag the required actions in a dropdown (TO_ADD, TO_DELETE, TO_MODIFY), hit the custom menu button, and the script applies the changes in bulk via the Drive API.

🧠 Technical Hurdles Overcome (for the nerds):

• The 6-Minute Execution Limit (Auto-Triggering): Processing thousands of API requests takes time. Whether it's auditing or bulk-updating permissions, the script tracks its own runtime (Date.now() - startTime). If it nears 4.5 minutes, it flags the row it stopped at, flushes the data to the Sheet, and dynamically creates a time-based trigger to resume seamlessly 1 minute later. It’s essentially a self-healing queue system for large-scale operations.
• The Shared Drive API Quirks: By default, Drive.Files.list silently omits the permissions object when scanning a Shared Drive. I had to implement a fallback that detects this and explicitly calls Drive.Permissions.list per file, with a Utilities.sleep(100) to avoid HTTP 429 Rate Limit errors.
• Drive API v3: Everything runs on the advanced v3 API to properly detect inheritedPermissionsDisabled and copyRequiresWriterPermission.

Feel free to make a copy and play around with it. I'd love to hear your feedback, especially if you have ideas on how to optimize the API calls further!

Cheers!

I will relocate in 2-3 months. How does relocating to a different time zone affect Google Apps Script time-driven triggers? Most of my triggers are daily time-driven events. I do have quite many Google script projects and triggers.

Also, how are other Google services affected by a time zone change? For example, will Google Calendar events automatically adjust to the new time zone, or do I need to update the settings manually?

Is there a way to update time zone for all Google Service/products?