Advanced Google Apps Scripting

We are not going to write code by hand. You will describe what you want in plain English, and Gemini will generate the Apps Script for you. Your job is to prompt clearly, paste the code into the right place, deploy, and describe any errors back to Gemini so it can fix them.

We will move through these steps together, as a group:

1. Prompt Gemini to build a web app from the test spreadsheet
2. Refine the look into a dashboard with dropdowns
3. Add a feature: turn a due date into a Google Task
4. Deploy, hit an error, and resolve it by feeding Gemini the details
5. Explore on your own, then rebuild with your own spreadsheet

Everything you build today actually does something: it edits a live sheet and creates tasks or events. That power is the point, and it is also the risk. A script that writes to the wrong place, or to someone else's account, can do real damage quietly. Before you point this at anything that matters, keep a few habits.

• Test on copies, never production. Build and experiment against a copy of a spreadsheet, not the live one your team depends on. Only point the script at real data once it behaves.
• Confirm who the actions affect. A web app set to "Execute as: Me" runs with your permissions and writes to your account. Tasks and calendar events land on your Tasks and your calendar, not other people's, unless you explicitly add them. Verify that before sharing anything.
• Check the access setting on deploy. "Anyone" means anyone with the link can trigger it. For real data, prefer "Anyone within your Workspace" or specific people.
• Read what a new permission is asking for. When Google prompts for authorization, it is telling you what the script can touch. If a request seems broader than the task, stop and ask why.
• Grow the blast radius slowly. Start with actions inside your own account. Only reach outside it (shared drives, other people's calendars, external services) once you understand and trust what the script does.

Open a new project at script.google.com. Then give Gemini your goal and paste in the link to your copy of the test spreadsheet. Ask for the app itself, not a tutorial.

I want to build a simple web app in Apps Script that allows others to navigate and edit this spreadsheet. Give me the exact code and tell me which file each part goes in.

[paste the link to your copy of the spreadsheet]

Gemini will hand you two pieces: server code for Code.gs and page code for a new index.html file. Follow its instructions on where each goes, then deploy as a web app when it tells you to.

The first version looks like a raw grid. Ask Gemini to redesign it. You do not need design vocabulary, describe the experience you want in ordinary words.

Could this look less like a spreadsheet and more like a dashboard? I'd like dropdowns to pick a category and a record, then a clean form to edit that item.

Replace both files with the new versions Gemini provides, then redeploy a new version of the existing deployment so your live URL updates.

Now connect the app to something outside the sheet. Point at a date column and ask Gemini to create a task or calendar event from it.

When someone enters a date in the "Next service due" column, can we automatically create a task for me in Google Tasks with the item name and due date?

Instead of a task, create a Google Calendar event on that date titled with the item name, so it shows up on my calendar.

You will almost certainly hit an error somewhere in here. That is expected, and it is the most valuable skill of the day. Errors get resolved by giving Gemini the exact evidence, not by guessing.

When something goes wrong, do one of these:

• Copy the exact error text and paste it back to Gemini.
• Take a screenshot of what you see (a permission wall, raw code showing instead of your app, a red error banner) and share it.
• Tell Gemini where it happened: in the editor, on deploy, or on the live web app.

I'm getting this error [paste the exact message or attach a screenshot]. It happened when I [deployed / opened the web app / clicked a date]. What's causing it and how do I fix it?

Two errors are common enough to name:

• Raw code shows instead of your app: the server code was pasted into index.html by mistake. Ask Gemini to confirm what belongs in each file, fix it, and redeploy.
• A permission or authorization error after adding Tasks or Calendar: the new service was added but Google never prompted you to approve it. Ask Gemini how to force the authorization prompt, then re-approve.

First, add one feature of your own to the maintenance app. Then start fresh with a copy of a spreadsheet you actually use and run the same process: build, refine, connect, deploy.

Share what you built, what broke, and how you fixed it. The debugging stories are often the most useful part.

Homework: pick one spreadsheet-driven task in your real workflow and turn it into a small web app this week. Keep the prompts and errors you used, we will compare notes next session.

Give Feedback

We move from Apps Script to Google Workspace Studio and build a hands-off expense tracker. It watches a folder for receipt scans, extracts the data from each receipt, and logs it into a spreadsheet automatically.

For anyone who stays the full two hours, we go one step further: a phone button that launches the scanner, so you can scan receipts straight into the designated folder for simple on-the-go logging.