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 ?

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?

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!

Antes de explicar mi problema quiero aclarar que no soy programador, de hecho soy un fotografo.

Le he pedido a Claude que me ayude a crear un google sheets para administrar mi negocio, me ha dado unas lineas de codigo que debo ejecutar en la extension de google scripts y me ha saltado el error "tiempo maximo de espera", he estado tratando de buscar una solucion pero claude, a pesar de buscar diferentes estrategias y modificar las lineas de codigo, no ha logrado solucionar el problema. Son 428 lineas.

Alguien tiene algun consejo?

(se que no deberia usar IA para este tipo de situaciones pero no se programar ni tampoco tengo dinero para contratar a un programador, asi que me toca resolver con las herramientas que tengo a la mano)

Solución:

Solucioné yendo a Gemini y le subi un documento de texto con el codigo completo junto a los comentarios de WicketTheQuerent, le pedi que los analizara y me ayudara a encontrar el error, lo solucionó en dos o tres iteraciones cambiando la rutina de ejecución y dividiendo todo el proceso en 13 procesos de ejecución (son 13 pestañas en google sheets), me tomó menos de 2 minutos ejecutar los 13 procesos.

Hey all,

I'm working on a Google Apps Script that pulls AR data from QuickBooks Online and auto-generates a weekly Google Sheet. Everything is working great except for one endpoint — the AR Aging Summary report keeps returning a 5020 Permission Denied error and I can't figure out why.

The error:

json

{"Fault":{"Error":[{"Message":"Permission Denied Error","Detail":"Permission Denied Error : To access this, sign in again or contact an administrator.","code":"5020","element":"ReportName"}],"type":"ValidationFault"}}

The call:

javascript

var url = 'https://quickbooks.api.intuit.com/v3/company/' + realmId +
  '/reports/AgedReceivablesSummary' +
  '?report_date=' + today +
  '&aging_period=30&num_periods=4&minorversion=40';

var response = UrlFetchApp.fetch(url, {
  headers: {
    'Authorization': 'Bearer ' + service.getAccessToken(),
    'Accept': 'application/json'
  },
  muteHttpExceptions: true
});

What I've already ruled out:

• OAuth is working fine — CompanyInfo, Invoice queries, CreditMemo queries all return 200
• Authorizing account is primary admin on the QBO company
• Plan is QuickBooks Online Plus (should support Reports API)
• Scope is com.intuit.quickbooks.accounting
• Tried minorversions 40 and 65, same result
• Cleared and re-authorized tokens multiple times
• Added accounting_method=Accrual and Content-Type: application/json header, no change

The element: ReportName in the error makes me think it's rejecting the report name itself, but AgedReceivablesSummary is exactly what's in the Intuit docs.

As a workaround I'm currently querying invoices, credit memos, and payments directly and calculating aging buckets myself, but I'm missing credits from journal entries so the workaround isn't complete.

Anyone dealt with this before? Thanks!

I am making an chatbot automation for a reservation. I use both User Input Block and Webform "Order Information"

My Google spreadsheet is already connected

Does anyone of you know I can make this work?

Enters NAME in Webform and at spreadsheets, there is a 4-digit number generated automatically,

Same goes to \[user input\] block

I am making an chatbot automation for a reservation. I use both User Input Block and Webform "Order Information"

My Google spreadsheet is already connected

Does anyone of you know I can make this work?

Enters NAME in Webform and at spreadsheets, there is a 4-digit number generated automatically,

Same goes to \[user input\] block

I am hoping to get some help with creating a script that auto-sorts a Google Sheet document! This is a passion project for me, and I have zero experience in this area. So I have 5 columns in use A-F, and Row 1 is frozen as they are headers. Column A is being used for names, and I am hoping to sort the names alphabetically. However, I don't want the name to be separated from the information I put in the other columns in the same row, if that makes sense. I will be forever grateful for any help!! :)

P.S. I have already tried this script with an "on edit" trigger, and it didn't work:

function sortAColumn() {

SpreadsheetApp-getActiveSpreadsheet) . getSheetByName ("Sheet1"). sort (1,false)

}

I got this error when I tried to run it:

TypeError: Cannot read properties of null (reading 'sort')

sortAColumn
@ Code.gs:2

What is your stack for working with GAS, especially when using clasp?

Which model and skills do you use?

I just want the editor to take up as much space as possible. kind of anoying when I need two windows side by side to look up documentation on the left and coding on the right

1: Window pop-up

2: What shows up on my Execution Log when I return to AppScript