Why?

Sometimes better than a one-off Python script :)

Quick Syntax

Basic

  • = starts a formula

    =9 * 4 → 36

  • ' starts plain text content

    '=100 → =100, as a string

  • use & to concatenate strings

    ="hello" & "world" → helloworld

  • use value() to turn strings to numbers

    =value("10" & "0") * 2 → 200

Concatenating Arrays

It is easier to think that every cell/value is an matrix of shape 1×1.

  • ={1,2}
    1 2

  • ={1;2}
    1
    2

  • ={1,2;3,4}
    1 2
    3 4

    ={1,2;{3,4}} is equivalent

References

=A1 references cell A1.

By default references are relative, when copying, row & column indexes “moves”.

Example of copying =A1 to nearby cells:
=A1 =B1
=A2 =B2

To “lock” or make the reference absolute, prefix the row or column index with $.

Example of copying =A$1+$C3 to nearby cells:
=A$1+$C3 =B$1+$C3
=A$1+$C4 =B$1+$C4

Array References

  • ={B7:C8} references 2×2 cells, equivalent to ={B7,C7;B8,C8}
  • ={A:C} references columns A to C
  • ={A5:A} references A5 to the last A-cell
  • ={A:A,C:C} references columns A and C

NOTE: When a expression evaluates to multiple rows/columns, it will expand to the nearby cells.

Gathering Data

IMPORTHTML

If the data exists on a website, the IMPORTHTML function can import a table or list into the spreadsheet.

The result is cached for an unknown duration. To force reload the data, append some otherwise unused query string like ?v=100.

There is also:

CSV Files

You can upload .csv or .tsv files.

Copy & Paste

Sometimes it is convenient to copy your script’s output from the terminal directly.

When pasted into Sheets, columns are separated by tabs by default.

You can also choose to use ,, ;, etc as column separators.

Google Forms

Google Forms responses can be imported into Google Sheets.

Google Forms questions can be pre-filled. You can observe the pattern of pre-filled links and generate them yourself. The query string looks like entry.123456=answer.

Example usage:

  1. the application generates a pre-filled link

  2. the application shows QR code to the user.

    example command: echo $url | qrencode -t ANSIUTF8

  3. the user scans the QR code with their phone and opens the pre-filled link

  4. the user submits the form

  5. data automatically available in the sheet linked to the form

Bulk Processing & Aggregation

Native Functions

  • ARRAYFORMULA can run scalar functions on multiple rows and/or multiple columns at once. This is very useful if your data grows from time to time.

  • FILTER.

  • VLOOKUP to fetch data from a table.

QUERY

QUERY runs a SQL-like query.

Custom Functions

You can use Google Apps Script (JavaScript-like) to create custom functions for use in your sheets.

The editor can be found in Tools > Script editor.

It is quite slow compared to native functions & QUERY though.

Presenting Data

Charting

Should be straightforward.

Conditional Formatting

Cells, rows, or columns can be formatted to change text or background color if they meet certain conditions. For example, if they contain a certain word or a number.

See https://support.google.com/docs/answer/78413.

Conditional formatting can also be used to set a gradient in a range to highlight the largest/smallest values.

Sharing

There are a lot of options:

  • Share the sheet itself
  • Publish as web pages
  • Link the sheet to Google Docs or Google Slides and share the doc/slide