AI Inside Google Sheets With Apps Script
By Darus S Alfatah · 2025-09-15 · Tutorial · 7 menit
Author note: Apps Script has a time limit; split long jobs into several smaller calls.
Why Apps Script
Apps Script runs inside Google's infrastructure, needs no server, and can call external APIs. For small teams it is the fastest way to add AI to an existing spreadsheet workflow, especially where the data already lives in Sheets.
The constraint is quota and execution time, so design for batched work rather than per-cell calls.
Avoid Recalculation Traps
Custom functions are recalculated whenever the sheet changes or reopens. If the function calls an API, cost multiplies invisibly. Store results in ordinary cells or in CacheService, and provide a menu button for manual refresh.
Treat the API call as an event, not a formula.
Batching
Send many rows in one request, ask for a JSON array back, and write results in one operation. Ten rows per call is a reasonable starting point, balancing latency against request size.
Validate that the array length matches the input length; when it does not, fall back to per-row processing for that batch.
Error Messages for Humans
Spreadsheet users are rarely developers. Return plain messages such as "quota exhausted, try later" instead of raw HTTP codes, and write detailed diagnostics to a hidden log sheet.
Never let a failed call leave cells blank without explanation; blank cells look like missing data and cause wrong conclusions downstream.
Quotas
Apps Script limits daily runtime and external calls. Schedule heavy jobs at night, split work into chunks, and track how much of the quota each automation consumes.
If volume grows beyond the limits, move the logic to a server and keep Sheets as the interface.
Frequently Asked Questions
Can I use it on a free account? Yes, within the standard quotas.
How do I store the key? In Script Properties, never in a cell.
Can it read PDFs? Only via a conversion step; plan for extra processing.
How do I trigger it automatically? Time-driven triggers, not onEdit, for API work.
What about privacy? Avoid sending rows containing personal data unless necessary.
Conclusion
Sheets plus Apps Script is a pragmatic AI automation stack when you batch requests, cache results, and keep quotas in mind.
A Practical Example
A shop collects product reviews into a sheet. A nightly trigger sends fifty reviews at a time, asks the model to classify sentiment and extract the main complaint, and writes three new columns. The owner opens the sheet in the morning and sorts by complaint count.
This pattern works because the work is batched, the output is structured, and failures are logged rather than silently dropping rows. Copy it for surveys, support tickets, or lead qualification lists.
Add a status column so anyone can see which rows have been processed and which are waiting, and keep a small script to reprocess only the failures.
Keeping It Maintainable
Name your functions clearly, keep configuration in one place, and document the sheet layout in a hidden tab. Whoever inherits the file will otherwise spend a day reverse-engineering which column feeds what.
Finally, back up the script weekly; a single accidental deletion can remove weeks of automation work.
A small batch example: Process ten rows per execution to stay under the Apps Script time limit, then continue from the next row.
Frequently Asked Questions
Can beginners follow this tutorial?
Yes. The steps are explained in order with code examples, suitable for beginners and experienced developers alike.
Is BankAI’s endpoint compatible with the library I already use?
Yes, BankAI is 100% OpenAI-compatible, so libraries such as the OpenAI SDK and LangChain work without code changes beyond the Base URL and API key.
Where do I get an API key to follow this tutorial?
You get the API key (and Secret Token) after registering and completing payment in the BankAI dashboard.