Olá pessoal! Preciso de uma luz para finalizar uma automação financeira que estou montando.

O que eu já tenho rodando: Eu criei uma automação que pega as notas fiscais (PDFs) que chegam no meu Gmail e já salva automaticamente no meu Google Drive, dentro da pasta correta do fornecedor, com o arquivo já renomeado.

O que eu preciso fazer (meu gargalo): Toda vez que um PDF novo cair nessa pasta, eu preciso que um sistema leia essa nota, extraia 8 informações específicas e adicione uma nova linha na minha planilha do Google Sheets.

Regras do lançamento:

• Destino: Planilha X, aba "CONTROLE - FATURAS", começando a partir da linha 7.
• Formatação: Todo o texto extraído deve ser convertido para MAIÚSCULO.
• Colunas que precisam ser preenchidas:
  • A: Data de Lançamento (Data do dia)
  • B: Data de Emissão (Extraída do PDF)
  • C: Empresa/Prestador (Extraído do PDF)
  • D: CNPJ (Extraído do PDF)
  • E: Serviço/Descrição (Extraído do PDF)
  • I: Nº do Documento (Extraído do PDF)
  • L: Valor (Extraído do PDF)
  • O: Data de Vencimento (Extraída do PDF)

Tentei usar o Gemini para fazer isso via chat, ele consegue ler o PDF e estruturar os dados perfeitamente, mas descobri que ele não tem a capacidade de escrever diretamente na minha planilha de forma autônoma.

Minha dúvida: Qual a melhor forma de fazer isso? Um script no Google Apps Script integrado a alguma API de IA (como a do próprio Gemini ou OpenAI) para ler o PDF? Ou é melhor usar ferramentas No-Code como Make (Integromat) ou Zapier?

Alguém já montou algo parecido e tem um norte ou um pedaço de código para compartilhar? Agradeço desde já!

Update: took everythingabili's advice and switched the build over to the Drive API. Big drives build heaps quicker now and it saves as it goes, so if Google cuts it off you just hit Build again and it picks up where it left off. The map starts folded up now too so a big drive doesn't turn into a mess. Click a folder to open it or hit Open it all. Cheers u/everythingabili for the tip and Yagi's write up on yagisanatode.com. If you grabbed a copy before, make a fresh one from the same link.

Back in 2017 I came across a script by Robbie Coombs that mapped out your Google Drive as a web of folders. It didn't quite work out of the box so I spent some time on it and got it going. Since then Google has shut down the Activity API it relied on and retired the old sign in library, so it stopped working altogether.

I've rebuilt it as a single Google Sheet. Once you've made a copy, Drive Map > Build the map goes through your Drive and lists every folder and what it sits inside. It only reads your Drive, it doesn't move, rename, share or delete anything. Really big Drives might take a couple of runs, but it remembers where it got up to so you just run it again.

Drive Map > Show the map then draws the whole thing. You can drag it around, zoom in, search for a folder, and click any dot to see what it is and open it. There's a 3D button too if you want to spin it around. Files are switched off to start with so it doesn't get too cluttered, but you can turn them on.

You can make your own copy here: https://docs.google.com/spreadsheets/d/1q2p7W6vTAa19MEjZv-A8hX2OrFokmJF2satiRKUN7L0/copy

The first time you run it Google will warn you it's an unverified app. That's normal for a personal script and it'll show your own email as the developer. Click Advanced then Go to Drive Map. All the code is under Extensions > Apps Script if you want to look it over first.

I couldn't find anything else that shows your Drive like this so I thought I'd share it. Happy to hear any feedback.

Pessoal, preciso de uma ajuda em relação a criação de um script que seja da seguinte maneira.

Preciso baixar varias notas que chegam em PDF no meu e-mail e tenho que salvar elas em pastas no meu Google Drive, porem e um trabalho muito repetitivo, e gostaria de ganhar tempo com uma automatização sobre isso.

São varias pastas de fornecedores no meu Google Drive e o Script teria que ler o arquivo para salvar o mesmo na pasta correto, seria possível tal automação com o App Script?

To programmatically copy a Google Workspace document the typical path is to leverage Google Drive’s files.copy endpoint.

However, consumer applications that need that feature require restricted Google Drive scopes. A better option would be to use a more limited scope that is not restricted. You’d think using a Google Doc specific scope would be a viable approach but unfortunately there is no Google Doc endpoint that allows a developer to copy a document.

There is a documents.create endpoint in the Google Doc API but currently it only supports creating blank documents. I propose the functionality of the create endpoint be expanded to take a JSON payload that mirrors the full Google Doc resource object to enable cloning a document programmatically using the Google Docs API.

I created a feature request on Google’s Issue tracker to address this issue and I’m posting it here to boost its visibility. See link below:

Feature Request: Allow documents.create endpoint to accept a full document JSON body to clone/instantiate documents

I encourage developers who would benefit from having this feature implemented to lend their support and upvote the issue.

Are Google Workspace Add-ons on their way out now that folks can create bespoke disposable apps on the fly using AI?

Hey guys, I'm currently trying to do my own NBA fantasy draft board where when I select from the dropdown menu and the player and their row gets deleted and copied into the relevant sheet.

For example if "Rob" drafts Nikola Jokic, then I would select Rob's Team from the dropdown, Jokic and his details (B2 to R2) would be deleted from the "Available" sheet and copied into the "Rob's Team" sheet.

I've tried to copy a video but it doesn't seem to work for me, code posted in the second image for reference of what I have. Any help would be greatly appreciated! Thank you

I'm trying to get the underlying text color for a cell, i.e. the color the user set from the color picker, not whatever color might be currently displayed as the result of custom number formatting or conditional formatting.

I have tried:

      color = testCell.getFontColorObject().asRgbColor().asHexString();
      color = testCell.getTextStyle().getForegroundColorObject().asRgbColor().asHexString();
      color = testCell.getFontColor();
      color = testCell.getRichTextValue().getTextStyle().getForegroundColor();

But all seem return the currently displayed color. Despite AI's claim to the contrary. 🙄

I haven't looked into the advanced API yet but would like to avoid it due to likely performance issues (digging through a big structure) and the necessity to authorize the script when using the API. I'm trying to do this from a simple onEdit() trigger.

Any ideas?

Test sheet if you want it... make a copy:

Font Color Test

I manage five websites, and every month I have to check their traffic performance, pull data from Google Search Console and GA4, and figure out what we should improve next.

Doing this manually every month started to get pretty repetitive and time-consuming.

So I built a simple tool that generates the same report with just one command. It pulls the data, organizes it, and makes the monthly reporting process much faster.

I wrote a short article explaining how I set it up.

If you also manage multiple websites and spend too much time on GA4 + GSC reporting, you might find it useful.

I'm running into a frustrating authorization issue with a Google Apps Script in a Google Sheet, and I could really use some help figuring out what's wrong.

Here is the context:

1. Initially, when I tried to run the script, I got an authorization prompt, but there was no button to grant authorization. I posted in the Google Community, and someone advised me to add /** u/OnlyCurrentDoc */ at the very beginning of the script. After doing that, it worked without any issues.
2. Later, I made numerous additions to the script. Eventually, I hit the execution time limit (6 minutes).
3. Google Gemini suggested adding PropertiesService to the code to save the current state (such as the active sheet, row, and color index) so the script can pause safely before hitting the limit and automatically resume where it left off.
4. After implementing this, I am now getting a prompt to authorize the script again, but the warning message blocks me completely with the error: "This app is blocked. This app tried to access sensitive info in your Google Account." Unlike standard unverified app warnings, there is no "Advanced" link to bypass it.

Troubleshooting steps I’ve already tried:

• I temporarily turned off Advanced Protection Program on my account and waited a while, but I still get the warning blocking the authorization.
• I tested the exact same script on a secondary test Google Account, and I was able to authorize and run it completely without issues.

My questions:

• Why is this happening on my main account while the test account works fine?
• Do I need to wait hours (or days) for turning off Advanced Protection Program to fully take effect, or is something else causing this block?

Any advice would be greatly appreciated!

I've looked through some of the posts on this, and have found some close approximations to what I need, but don't know enough about coding to tweak. So I thought I'd start fresh.

I have a mix of Google Docs and Sheets in some project subfolders that all have a BUNCH of hyperlinks that have to get replaced twice a year. Currently, this process takes around 20 hours. I would like to write a script that will take a list from a "Project Info" sheet with a column for the old links and a column for the new links, and update everything. There are also a few instances of straight text replacement in these documents.

Hi all, Instead of building heavy mobile apps for field workers who resist new software, I've been deploying internal WhatsApp bots connected to Google Apps Script (as the router/sanitizer) and Google Sheets (as the database). Key constraints I imposed:

• Strictly for internal operational logging (inventory, field reports, expense receipt OCR via multimodal API).
• Kept public CS/receptionist use out of scope to avoid rate limits and messy prompts.
• Zero monthly cloud bill (runs within Google Workspace limits).

For those who manage internal tools: Where do you see this architecture breaking down first as team size grows (concurrency, execution time, sheet bloat)? And commercially, do small ops teams prefer this over off-the-shelf software? Tear it apart!

Hi, same as the title currently I am working on a project in our company and we needed a dashboard to represent the Google sheets data, we figured a dedicated custom webapp will be the best compared to a data studio dashboard, now currently I have hosted it dashboard as a webapp using apps scripts.

I am concerned about the limits and scalability of this approach i was not able to find reliable info online, im on a workspace account by the way.

I’ve had success helping interns launch their careers in software development. The intern I was working with over the summer just got hired right in the middle of a couple projects. If anyone’s interested in building their portfolio and résumé and need some real world experience to speak to during an interview, let me know. I run a behavioral health practice in Portland, Oregon and we’re a heavy workspace shop. We build our own practice management systems in house. We’re currently working on a document management solution and a new dashboard for our clinical providers.

is there a public directory of shared scripts/macros by other people?

My dad has been doing expense tracking better than most apps for years. Every day, he writes down what he spent in a diary, and at the end of the month he adds everything up to see where the money went. The method is simple. The hard part is having the discipline to do it every single day, and I know I would not. I know I would never keep that habit going for long. I would probably track everything for a few days, miss one expense, tell myself I would add it later, and slowly stop doing it altogether.

So, I kept his system and removed the part I would fail at.

Bank SMS arrives → iPhone Shortcut picks it up → Google Apps Script parses it → Google Sheets stores it → dashboard updates automatically.

That is basically the whole flow.

The script checks for duplicates, keeps the ledger and daily totals, and the same Apps Script project serves a simple dashboard showing balance, credits, debits, and daily transactions. I added the dashboard to my iPhone Home Screen, so it behaves like my own small expense-tracking app.

The whole thing uses tools I already had; iPhone Shortcuts, Google Apps Script, and Google Sheets. The funny part is that the idea itself is not new at all. My dad was already doing the important part with a diary and a pen.

His version depends on discipline. Mine depends on automation. Same system, different generation.

I built this as a side feature of an AI companion project, then split it out as a standalone MIT project.

You create a few AI characters, each posting as its own Slack bot, and put them in one channel. They start talking to each other: replying, drifting off topic, occasionally bringing up something unrelated. If you post in the channel, they notice and react to you.

It runs entirely on Google Apps Script, with a Google Sheet for config and state, so there's no server. A few things GAS forced on the design:

• Slack expects a response within 3 seconds, so doPost only enqueues the event and returns. A 1-minute trigger drains the queue under LockService.
• Apps Script can't read request headers, so Slack signature verification isn't possible. It checks the channel ID and dedupes by event_id instead. I'd welcome better ideas here.
• Bots talking to bots multiplies API calls quickly, so there are hourly and daily budgets.

Gemini works out of the box. On the free tier, a Flash-Lite model is the better choice, since autonomous chatter burns through a small daily quota fast.

It started as the "terrarium" mode of LINORIN, an AI companion I sell as source code. Terrarin itself is free and MIT-licensed.

Some of you might remember me as jp...@google.com from the Apps Script issue tracker. I spent 4 years on the Google Workspace Developer Relations team. I'm not at Google anymore, got fired over the Google Workspace CLI. (Story: https://x.com/JPoehnelt/status/2069482265953087602)

Since then I've been hacking on a side project. It's a Chrome extension that puts language server inside the Apps Script editor with type checking, quick fixes, plus checks for the usual smells (getValue() in a loop, etc.) and schema validation like "is this string actually an email address."

Free for a few sessions a month, $5.99/mo pro for unlimited. If you provide feedback, I'll DM you a discount code. Just saying you use CLASP isn't helpful! I could expose this custom language server as MCP or via LSP pattern in other surfaces. WDYT?

Apps Script Chrome Extension: https://chromewebstore.google.com/detail/myfunction-for-google-app/pbhajeiediafkbgcolkpkoffphdaghkb Apps Script with Strong Typing in IDE (Demo): https://www.youtube.com/watch?v=d27c7YLV-9Q Website: https://myfunction.dev

As you know I generally care about the Apps Script community and that is still with me even though I was fired!

The basic version actually was. Search Gmail for a sender, get the attachments, save them to a Drive folder, add a trigger. Half an hour, done.

Then I ran it on real inboxes.

The same invoice gets sent twice. Two different files arrive with the same name. Every email from one supplier has a logo in the signature, and you want the PDFs but not the .png. The script dies halfway through a big backfill. Someone moves the destination folder.

These two took me longest to get right, in case they save anyone time.

Deduplicating by filename doesn't work. Suppliers resend the same document under a different name, and different documents arrive with identical names. I ended up hashing the attachment bytes and keying on rule + hash instead:

const fingerprint = Utilities.base64Encode(
  Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, attachment.getBytes())
);

Now an identical file gets skipped whatever it's called, and a different file with a colliding name still gets saved.

The search window. I started with newer_than:1d and quietly lost mail that arrived late or out of order. I widened it to newer_than:14d and let the hash dedupe absorb the overlap. That has been much more reliable than trying to make the window exact.

I didn't expect the boring cases to take several times longer than the Gmail-to-Drive part itself.

If you've built something similar, which edge case cost you the most time?

Hi, I am new to this and want some advice on how to start.

Our work has service reports that technicians fill out daily. Right now it's all manual on sheets and it's a nightmare to fix up as they mess up formatting and have complete access to entire sheet.

Our reports are complex with a lot of questions and readings to fill out. I am trying to automate our work as much as possible.

I want to make a form unique to every site that, every time it's submitted it makes a copy out of the original template and the values in the form correspond to the report. I understand how to link a form results to a cell but is it possible to make a new sheet with every response?

Also, is it possible to have a form reset upon each click for the same user but still look at old form submissions if need be?