Automate Google Workspace with Apps Script, AppSheet and Gemini
Automate Google Workspace: which tool does what?
To automate Google Workspace, a Swiss SME usually mixes three Google tools. Apps Script moves data on a schedule or after a form submit. AppSheet gives non-developers a small app on top of Sheets. Gemini drafts and searches inside Docs and Gmail. None of the three replaces the others, and only Apps Script runs a process while nobody is at the keyboard.
This is for owners, office and finance leads in Swiss SMEs that already work in Gmail, Drive and Sheets.
If your firm runs on Microsoft 365, the counterpart is automating Microsoft 365 with what you already have. Whether an assistant such as Gemini is the right purchase is a separate question. A Copilot licence or a built workflow is about Copilot, but the fork is the same: an assistant helps a person draft, a workflow moves data when nobody is typing.
What Apps Script can do
Apps Script reacts to an event or a clock and then reads or writes your Sheets, sends mail and works with files in Drive. Google describes it as a platform for business applications that integrate with Google Workspace: you write modern JavaScript in a browser editor, and the scripts are saved to Drive and run on Google’s servers. Four starting points cover most office jobs.
- A clock. A time-driven trigger runs a script at a set time or interval, as often as every minute and as rarely as once a month. The time can be slightly randomised: a trigger set for 9 o’clock runs at some point between 9 and 10.
- A form answer. A form submit trigger runs when someone answers a Google Form.
- A change in a Sheet. An edit trigger runs when a user changes a value in a spreadsheet, a change trigger when its structure changes.
- A web request. A script with a
doGetordoPostfunction can be published as a web app that answers requests from outside, such as a form on your website.
AppSheet and Gemini: what your edition includes
As Google’s pages read at the time of writing, AppSheet Core is included in Business Starter, Standard and Plus, and Gemini reaches Docs and Sheets from Business Standard. AppSheet Core needs a verified domain.
| Business Starter | Business Standard | Business Plus | |
|---|---|---|---|
| AppSheet Core | Included | Included | Included |
| Gemini in Gmail | Included | Included | Included |
| Gemini in Docs, Sheets, Meet, Chat | Not included | Included | Included |
| Gemini app | Basic access | Expanded access | Expanded access |
The rows come from Google’s Business edition comparison, its AppSheet subscription page and its Workspace pricing page. Editions change, so check before you buy.
According to the AppSheet page, Gemini in AppSheet, optical character recognition and advanced data integrations such as BigQuery or Salesforce need AppSheet Enterprise Plus.
On data, Google states in its privacy hub that content is not used for generative AI model training outside your domain without permission, and that Gemini only retrieves content the user can already access. That is Google’s statement, not a review of your setup. With customer data, ask your data protection adviser.
A worked example: our own case-study download gate
On our website, a visitor can download a case study as a PDF after leaving an email address. Apps Script holds that chain together.
A form on the website
The visitor enters an email address and ticks a reCAPTCHA box. The page posts the data to an Apps Script web app.
A check
The script asks Google’s reCAPTCHA service whether the answer is genuine. A cooldown allows one request per email address and case study every ten minutes.
A log in a Sheet
The script writes a row: time, email address, case study, language, whether marketing contact was allowed, and the status.
The PDF from a private Drive folder
The script finds the file by its exact name. Replacing a PDF means uploading a new file with the same name. The script stays untouched.
An email through Microsoft Graph
The script sends the mail with the PDF attached and writes the result, delivered or failed with the error text, back into the same row.
The mail leaves from our Outlook mailbox through Microsoft Graph instead of Gmail, because the company runs on Microsoft 365. Graph accepts the attachment in the same call. That needs an app registration in our Microsoft tenant with the Mail.Send application permission and an administrator’s consent. Apps Script stays the glue and can call anything that has an API.
It is lead gating, not copy protection: once someone has the PDF, they can forward it. The address of the web app is public, so reCAPTCHA and the cooldown reduce abuse but do not end it.
Where the quotas stop you
Google’s quota page sets daily limits per user, so measure your volume against them before you plan a job. The values for a Workspace account, with a personal gmail.com account in brackets:
- Email recipients per day: 1,500 (100)
- Outgoing requests (URL Fetch calls) per day: 100,000 (20,000)
- Total runtime of all triggers per day: 6 hours (90 minutes)
- Runtime of one execution: 6 minutes on both
- Simultaneous executions: 30 per user. Triggers: 20 per user per script
Quotas are per user and reset 24 hours after the first request. Google adds that all quotas can be reduced or changed without notice. Keep business scripts on a Workspace account, not a personal one.
In our download gate, each request costs a handful of outgoing requests: the reCAPTCHA check and the calls to Microsoft. The 100,000 limit is far away, and the mail limit does not apply because we do not send through Gmail.
The limit to plan around is the six minutes. A job that reads a large Sheet row by row can run out of time. Google’s best practices advise reading all data into an array with one command and writing it back with one command, and give an example that drops from about 70 seconds to about 1 second. Batch your reads and writes before you schedule a heavy morning job.
Who owns the scripts
The account that creates a trigger or deploys a web app is the account the script runs as. Google says installable triggers always run under the account of the person who created them. A web app set to “execute as me” runs as its owner, whoever opens it.
That is a risk when one person owns everything. Google’s collaboration guide puts it plainly: “If someone leaves your organization and their account is removed, access to scripts without other owners is lost.” Standalone projects appear in Drive as files, container-bound ones do not, so know which kind you have.
Three habits cover most of it. Share ownership of every script the business relies on. Keep one page listing each script: what it does, what starts it, which account it runs as and where its secrets are kept. And run business scripts from an account the company controls. We run ours from a shared operations account with two-factor authentication, not a personal one, and only the people who manage the leads can edit the Sheet.
A script that talks to another system also holds credentials. Ours sit in the script properties, not in the code. The Microsoft secret expires after 24 months at most, so someone has to renew it.
When something fails
A script that fails quietly is worse than the manual job it replaced. The Apps Script dashboard lists recent executions with their status and shows an error rate, but only someone who looks will see it.
Our gate writes the outcome of every attempt into the same Sheet row: submission status, delivery status and the error text Microsoft returned. Once a week someone filters for failed deliveries, finds the cause and resends by hand. A row without a delivered PDF is a person waiting for a file. The causes we have written down for ourselves are dull: a wrong or expired secret, a missing permission, a misspelt file name.
One more trap. Editing the code does not change the live web app until you deploy a new version.
When Google’s own tools are enough
They are enough when the data already lives in Sheets, Gmail and Drive, the rules fit on one page, someone internal can read the script and two people own it. A morning digest of open rows, a form that fills a register and notifies the owner: that is in-house work.
Get help when the chain has to reach a system without a Google connection, when the logic has more exceptions than rules, when the volume nears the quotas, or when nobody inside can look after the code.
When the Process Check fits
The Process Check fits when you have one recurring task that is still done by hand and want to know whether Apps Script or AppSheet covers it or whether it needs an implementation. Thirty minutes on Microsoft Teams. The Process Check is free. It works with any program that has an API, and what your programs offer is settled in the half hour. You leave knowing where to start, whether it pays off, and with a price range for the implementation, plus a short written summary. The answer may well be “do this yourself in Apps Script”. Then that is in the summary.
If you already know which task it is: book the Process Check.
Written by
Aurum Avis Labs
Builds and ships at Aurum Avis Labs. Writes here about what we learn working with founders and SMEs in the DACH region.
Related Articles
You might also be interested in these articles
Automate Microsoft 365 with what you already have
What an SME can automate with Forms, Lists, Outlook rules, Teams approvals, Bookings and Power Automate, and where the included licence stops.
What the Process Check is and what it is not
A free Process Check: a 30-minute call on one workflow, a concrete case and a package size. Not a workshop.