Jun H.
Sample Weekly report runs itself
Every Monday the managers get their summary and I do nothing. Worth far more than the price.
Google Sheets power user: formulas, QUERY and Apps Script
Use ARRAYFORMULA, XLOOKUP, FILTER and QUERY, connect files with IMPORTRANGE, and automate reports and emails with Google Apps Script
Created by Zeynep Arslan
- Updated September 2026
- English
- Intermediate
What you will learn
- Structure workbooks with named ranges and tables
- Write ARRAYFORMULA, FILTER, SORT and UNIQUE formulas that update themselves
- Look up and combine data with XLOOKUP, LET and named functions
- Summarise and pivot data with the QUERY function
- Connect spreadsheets with IMPORTRANGE and protect shared data
- Write Apps Script functions that read and write sheet data
- Automate reports and emails with time driven triggers
Course content
Sheets beyond the basics
The QUERY function
- Article: QUERY basics 11 min
- Article: Grouping and pivoting with QUERY 10 min
- Article: Dates and cell references in QUERY 9 min
- Article: Connecting files with IMPORTRANGE 9 min
Advanced formulas
- Article: LET, LAMBDA and BYROW 10 min
- Article: Named functions 8 min
- Article: Text and regular expressions 9 min
Collaboration and data quality
- Article: Dropdowns and data validation 8 min
- Article: Protected ranges and permissions 8 min
- Article: Version history and named versions 6 min
- Article: Google Forms to Sheets workflows 8 min
Automating with Apps Script
- Article: Your first script 9 min
- Article: Reading and writing ranges 11 min
- Article: Custom functions 9 min
- Article: Triggers: on edit and on a schedule 10 min
- Article: Sending emails from Sheets 9 min
- Article: Quotas, errors and security 8 min
Final project
- Article: Automated weekly report 12 min
- Article: Maintaining the workbook over time 8 min
- Quiz: Final check 6 min
Requirements
- A Google account
- Comfortable with basic Sheets formulas such as SUM and IF
Description
Google Sheets is easy to start with and surprisingly powerful once you know its strengths: formulas that return whole tables, the QUERY function, live connections between files and a built in scripting language. This course takes you from comfortable user to power user, with a focus on saving time on real, recurring work.
What you will learn
We work with a shared operations workbook for a small events company: bookings arriving from a Google Form, a client list, a pricing sheet and a weekly report for managers.
- Ranges, named ranges and Sheets tables for structured data
- ARRAYFORMULA and dynamic array functions such as FILTER, SORT and UNIQUE
- XLOOKUP, LET, LAMBDA and named functions
- The QUERY function for SQL style filtering, grouping and pivoting
- IMPORTRANGE to connect data across spreadsheets safely
- Dropdowns, data validation, protected ranges and version history
- Google Apps Script: reading and writing data, custom functions, triggers and email
The final project
You will build an automated weekly report: data from a form is cleaned with formulas, summarised with QUERY, and emailed to managers every Monday morning by an Apps Script trigger, with no manual steps.
Who it is for
People who already use Google Sheets for everyday work and want to go further: operations staff, team leads, analysts, teachers and small business owners. You should be comfortable with basic formulas such as SUM and IF. No programming experience is needed for the Apps Script section; we start from the very first line of code.
Who this course is for
- Operations staff and team leads who run work in Google Sheets
- Analysts and teachers who build shared spreadsheets
- Small business owners automating routine reporting
Instructor
Zeynep Arslan
Spreadsheet trainer teaching Excel and Google Sheets for everyday work and reporting
- 4.3 instructor rating
- 15 reviews
- 7,270 students
- 2 courses
- Sample
I have trained office teams in spreadsheets for years, from people who are nervous about formulas to analysts who want to automate their weekly reports. I build tracking sheets, budgets and reporting workbooks, and I spend a lot of time fixing workbooks that grew without a plan.
I teach Excel and Google Sheets with a focus on habits that save time: clean tables, the modern lookup and dynamic array functions, PivotTables, and simple automation with Apps Script. I explain why a formula works, not just what to type.
Every course comes with practice files built from realistic office tasks, so you can use what you learn the next morning.
Student reviews
Elif D.
Sample QUERY is a game changer
I replaced a whole sheet of helper columns with two QUERY formulas. The pivot lesson was excellent.
Marco T.
Sample Apps Script without fear
I had never written code. Now our booking confirmations are sent automatically and marked as sent.
Hiba A.
Sample Great ARRAYFORMULA explanation
The header in the formula trick and multiplying conditions finally made array formulas work for me.
Owen R.
Sample Practical events example
The workbook felt like real work. I adapted the weekly report for our volunteer team within a day.
Natalia S.
Sample Script section moves quickly
Very useful, but I needed to pause often in the Apps Script lessons. The final project ties it together nicely.
Fiona G.
Sample Clear on permissions
The protected ranges and IMPORTRANGE permission notes saved us from sharing salary data by mistake.
Keep exploring
All Office and Productivity Tools courses
More in Office and Productivity Tools
$29.99
Enroll now