Google Sheets formula generator with explanations you can follow
Google Sheets has its own tricks: QUERY, ARRAYFORMULA, IMPORTRANGE and REGEXMATCH do things Excel can't in one cell. Tell your todo.is agent what the sheet should show, and it writes a Sheets-specific formula, explains it, and checks it against an export of your data.
The prompt
- Write a Google Sheets formula that [WHAT YOU WANT TO SEE]. My sheet is [SHEET AND COLUMN LAYOUT] (or use the attached CSV export). The formula goes in [WHERE IT GOES]. Prefer one formula in the header row that fills the whole column automatically when new rows come in from [WHERE NEW ROWS COME FROM]. Explain every part in plain words, test it on my data, and tell me any setting I need to change (locale, date format, sharing for IMPORTRANGE).
What to change
- [WHAT YOU WANT TO SEE]: E.g. "a live list of open tickets older than 3 days, newest first" or "each lead's country from their phone code".
- [SHEET AND COLUMN LAYOUT]: Tab names and columns, e.g. "Tab 'Form responses': A timestamp, B email, C status, D assigned to".
- [WHERE IT GOES]: E.g. "cell A1 of a new tab called 'Open'" or "column F header".
- [WHERE NEW ROWS COME FROM]: E.g. "a Google Form", "a Zapier import", "typed by hand". Or "nowhere, the data is fixed".
Example result
- Formula for 'Open'!A1
- Goal: a live list of tickets that are still open and older than 3 days, oldest first.
- Paste in A1 of the Open tab:
- =QUERY('Form responses'!A:E, "select A, B, C, D where C = 'Open' and A < datetime '"&TEXT(NOW()-3, "yyyy-mm-dd HH:mm:ss")&"' order by A asc", 1)
- How it works
- • 'Form responses'!A:E is the whole range, so new form rows are included automatically
- • select A, B, C, D picks timestamp, email, status and assigned to
- • where C = 'Open' keeps only open tickets (QUERY is case-sensitive, so "open" won't match)
- • A < datetime '...' keeps rows older than 3 days; TEXT turns NOW()-3 into the format QUERY expects
- • order by A asc puts the oldest first
- • The final 1 tells QUERY there is one header row
- Bonus: days open, in the column next to it
- In E1: ={"Days open"; ARRAYFORMULA(IF(A2:A="", "", INT(NOW()-A2:A)))}
- One formula fills the whole column, including future rows.
- Test results
- • 312 responses, 41 open, 17 older than 3 days: all 17 appear, oldest from 12 Sept
- • 3 rows had status "open " with a trailing space and were missed. A helper column with =ARRAYFORMULA(TRIM(C:C)) fixes it; point the where clause at that column instead
- Settings to check
- • File > Settings > Locale: if it's a European locale, use ; instead of , between arguments
- • NOW() recalculates on change; set File > Settings > Calculation to "On change and every hour" for a list that updates by itself
How to do it with todo.is
- Copy the prompt and describe the result you want and your tabs and columns.
- Export the tab as CSV (File > Download > CSV) and attach it, or just describe the columns.
- Paste the to-do into todo.is or message your agent; it tests the formula on your rows.
- Paste the formula into your sheet and read the plain-words explanation.
- Ask for changes in words, like "also hide tickets assigned to Sam".
Tips for a better result
- Sheets formulas use commas or semicolons depending on your locale. Mention your country so the separators are right.
- QUERY uses column letters of the range (A, B, C), and Col1, Col2 when the range comes from another formula. Ask for the version that fits.
- Use one ARRAYFORMULA in the header row instead of copying a formula down: rows added by a Google Form will be covered.
- IMPORTRANGE needs you to click "Allow access" once in the target sheet. Your agent will tell you when that applies.
- If a sheet gets slow, ask your agent to replace whole-column ranges and volatile functions like NOW() with a tighter setup.
Google Sheets formula generator: FAQ
- What is the difference between Google Sheets and Excel formulas? Most basics like SUMIFS, XLOOKUP and IF work the same. Sheets adds QUERY, IMPORTRANGE, GOOGLEFINANCE, REGEXMATCH and simpler ARRAYFORMULA, so the best formula is often different.
- Can it edit my Google Sheet directly? This prompt works on a CSV export or your description and gives you the formula to paste. For scripts that change the sheet, use the Google Apps Script generator prompt.
- Why does my QUERY formula return an empty result? Common causes are mixed data types in one column (QUERY treats the minority type as empty), case-sensitive text matches, or dates not written with the date or datetime keyword.
- Does ARRAYFORMULA work with every function? Not all. Functions like SUMIFS and VLOOKUP work inside it, but some, like INDEX and AND/OR, don't spread over ranges the way you'd expect. Your agent picks a pattern that does.
JavaScript is required to use the todo.is app.