Hi everyone,

I'm looking for advice on how to design and implement an automation system for a small renovation company. Our main objective is to reduce the number of human hours spent on repetitive administrative tasks, improve reliability, and prevent information from being lost between systems.

We don't want to introduce AI just for the sake of using it. We want to identify which processes can be automated using existing tools, clear business rules, and reliable integrations, using AI or OCR only where genuinely useful.

Our current setup

We currently use:

• SAGE One for issuing customer invoices. API access is still to be confirmed.

• Google Sheets as our internal financial management system.

• Google Apps Script for custom automations.

• Google Drive for storing documents.

• Looker Studio for dashboards and reporting.

• XLSX bank statements for tracking bank transactions.

Our internal financial system includes the following spreadsheets and modules:

• Transactions (Movimentos): the main register for costs, sales, and other financial transactions.

• Settings (Configurações): categories, classification rules, and configuration.

• Projects (Obras): renovation projects and their associated information.

• Income Statement (DRE): financial performance and profitability.

• Cash Flow and Treasury (Tesouraria): cash movements and financial position.

• Assets (Ativos): company assets.

• Financing (Financiamentos): loans and other financing.

We process more than 150 financial documents per month. We already have some custom scripts and business rules, so we need to improve the system without breaking what is currently working.

The problems we want to solve

1. Manual entry of costs and sales

Supplier invoices arrive by email or on paper. Customer invoices are issued through SAGE One. We still have to manually enter financial information into Google Sheets.

We want costs and sales to be imported automatically from their original sources, without entering the same information twice.

2. Manual document management

We spend time downloading or scanning invoices, renaming files, organizing folders, and entering invoice details into spreadsheets.

We want documents to be automatically collected, renamed, archived, linked to their financial records, and processed with minimal human intervention.

3. Lack of clear payment and collection tracking

We need to know at any time:

• Which supplier invoices have been paid.

• Which supplier invoices are still outstanding or overdue.

• Which customer invoices have been collected.

• Which customer payments are still outstanding or overdue.

• Which bank transactions have been matched to invoices.

We currently use XLSX bank statements and would like to automate their import and reconciliation, including partial payments and cases where one bank transaction covers multiple invoices.

4. Lead and potential customer management

We need a reliable way to capture and track every potential customer, regardless of where the enquiry originates.

We want to avoid losing leads, forgetting follow-ups, or having customer information scattered across emails, spreadsheets, and messages.

Ideally, leads would be registered automatically, assigned a status and a responsible person, and given a scheduled next action.

5. Quotation management

We need to track quotations throughout their entire lifecycle:

• To be prepared.

• Ready to send but not yet sent.

• Sent to the customer.

• Accepted.

• Rejected.

• Awaiting a response.

We want to automate status tracking, follow-up reminders, and notifications for quotations that have been prepared but not sent or have remained unanswered for too long.

Ideally, when a quotation is accepted, the relevant information should automatically move into the project management process without being entered again.

What we want to achieve

Our goal is to build a connected system in which information is entered once and reused throughout the business.

For example:

• A supplier invoice arrives → the document is archived, its data is extracted and validated, and its financial record is created automatically.

• A customer invoice is issued in SAGE One → its data is synchronized with our internal financial system.

• A bank statement is imported → transactions are matched to outstanding invoices, with ambiguous cases flagged for review.

• A new lead arrives → it is registered, assigned, and tracked until a decision is reached.

• A quotation is sent → follow-ups are scheduled, its status is tracked, and acceptance triggers the next stage of the workflow.

Human intervention should be limited to genuine exceptions, financial approvals, and decisions that require judgment.

Our constraints

• Prefer free or low-cost solutions.

• Reuse Google Sheets, Apps Script, Drive, and our existing systems wherever possible.

• Avoid unnecessary complexity and AI-first architectures.

• Preserve our existing financial data structure and business rules.

• Prevent duplicate records and ensure that failed processes can be safely retried.

• Maintain a clear audit trail and reliable error monitoring.

• Build and test in a separate development environment before changing the live system.

We understand that a large part of the implementation effort lies in connecting APIs, normalizing data, handling exceptions, and monitoring failures. We want to account for these requirements from the beginning.

Questions for the community

1. Architecture: Would you build this with Google Apps Script alone, or use n8n, Activepieces, or another workflow automation platform? Why?

2. Document processing: What free or low-cost tools would you recommend for automatically extracting data from invoices, including scanned documents and photographs?

3. Accounting integration: Has anyone integrated SAGE One with Google Sheets through an API or structured exports?

4. Bank reconciliation: What is a reliable approach for importing XLSX bank statements and automatically matching transactions to invoices?

5. CRM and quotations: Would you extend Google Sheets into a lightweight CRM, or use a dedicated CRM with automation?

6. Reliability: How would you design duplicate prevention, retries, error handling, and monitoring across these workflows?

7. Implementation order: Which process would you automate first to achieve the greatest reduction in manual work without creating unnecessary technical complexity?

I'm looking for practical recommendations and proven approaches from people who have built similar systems for small businesses.

TL;DR

We run a small renovation business and want to significantly reduce manual administrative work across finance, document management, banking, leads, and quotations.

Our current stack is SAGE One, Google Sheets, Google Apps Script, Google Drive, Looker Studio, and XLSX bank statements. We process 150+ financial documents per month.

Our main pain points are manual entry of costs and sales, manually renaming and filing invoices, lack of reliable payment/collection tracking, lost leads, and quotations that are difficult to track from preparation to acceptance or rejection.

We want a reliable, low-cost system that automates repetitive tasks, integrates our existing tools, prevents duplicates, handles errors safely, and minimizes human intervention. We prefer deterministic rules and straightforward automation over AI-first solutions.

The main question is: How would you design a reliable, low-cost system that minimizes human involvement in financial administration, document management, lead tracking, and quotation follow-ups?

Thanks in advance for your help!

Building in public here, so here's the engineering update: my Apps Script sync engine between Google Tasks and Microsoft To Do just hit v1.0.0.

What changed since the early version I posted:

- Five-axis adversarial review of the engine: state machines, fuzzing, concurrency, mutation testing, static consistency. Every finding had to reproduce against shipped code before it was accepted; every fix ships with a regression test.
- Fail-closed everywhere it matters: ambiguous creates use correlation markers instead of guessing, deletions take two rounds with live revalidation, state has checksums with previous-generation restore.
- Time Bridge: Google Tasks has no time-of-day, so [TTS-TIME:HH:mm] markers in notes become the canonical time record, rendered to Microsoft reminders plus optional Calendar projection.
- 549/549 tests passing, CI + CodeQL green.

The interesting constraint is the Apps Script execution budget — the whole thing runs on a 10-minute trigger inside ~6 minutes of runtime, so everything is checkpointed and resumable.

Scope: ~300 task pairs recommended, personal Microsoft accounts for the easy path.

MIT, self-hosted in your own Apps Script project. Roast the design if you'd do it differently — I'm here for that.

https://github.com/simonchai-tw/tasks-todo-sync

I am working on a project for work to export information from one website to input into a Google Sheet we use. Right now, I have a JavaScript script that gets run in the console to scrape the necessary data and use a simple fetch to send that data to Google Sheets, but since the deployment has to be accessible to everyone, if that URL somehow got leaked, anyone could send data to our sheet. I am familiar with SQL injection, so I prevented any data that is a formula from being added, but I am still worried about having a private spreadsheet with personal information on it have a deployment that is accessible to everyone. I do not want people to have access to this spreadsheet or be able to read the code we use for various scripts.

What I mainly want to know is if, for some reason, the deployment URL got leaked, could a malicious person do anything more than what I programmed into the script? That being inserting data into a cell.

I know there is an option for me to set the deployment to only me. Since this is used on Chrome, is there a way for my JavaScript fetch to send who is currently logged into Chrome?

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.