This section covers Google Sheets at two levels. Formulas are the sheet’s own language — code that lives in a cell and runs without anything installed. The Sheets API is the level above: a spreadsheet that lives in Google Drive and is edited from PHP over an API, where the unit of work is a request against a spreadsheet ID rather than a file handle. Most real work starts in a cell and moves up only when it has to.
Formulas: code that lives in a cell
No project, no credentials, nothing installed — these run in the spreadsheet itself. They are the fastest way to get an answer out of a sheet, and knowing where they stop is what tells you when to move up to the API.
- Write SQL in a cell with QUERY —
select,where,group byandorder by, in one cell - Filter, sort and de-duplicate in one formula
- Replace VLOOKUP with XLOOKUP, and when INDEX/MATCH still wins
- Cover a whole column with ARRAYFORMULA instead of filling down
- Name and reuse a calculation with LAMBDA and LET
- Pull live external data in with the IMPORT functions
The ceiling is real. A formula recalculates on the sheet’s schedule, not yours; it cannot authenticate, retry, or write anywhere else; and the external-fetch IMPORT functions need a human to approve them once in a browser. When any of that starts to matter, the rest of this page is the level to move to.
Browse every Google Sheets formula article
Set up access first
Nothing else works until Google trusts your code. This is the part that trips people up, and it is worth doing in order:
- Create a Google Cloud project
- Enable the Google Sheets API
- Install the Google client library
- Authenticate with a service account
For anything running unattended — a cron job, a queue worker, an export endpoint — the service account is the one to use. It belongs to your code rather than to a person, so there is no consent screen and no token to refresh by hand; you share the spreadsheet with its email address like you would with a colleague. The older OAuth walkthrough is still here for the interactive case, but start with the service account.
Reading and writing values
The everyday work, and the closest thing this API has to a core loop:
- Create a blank spreadsheet or a “Hello World” one
- Read cells from a range
- Write into a range and update existing cells
- Append rows to the end of a sheet
Changing how the sheet looks
Formatting goes through a different, batched part of the API than values do — a list of requests applied in one call rather than a write to a range. Borders, merged cells, the spreadsheet title and duplicating a sheet all follow that same shape, so the first one you learn carries over.
Working with Excel files instead?
If the spreadsheet is a file on disk or an upload rather than a document in Drive, you want PhpSpreadsheet — reading and writing .xlsx from PHP is on the Excel side. Moving between the two is a normal thing to want, and the conversion articles cover it.