Google spreadsheet formula not updating

Have you ever tried to use a formula in a column adjacent to your form responses to do calculations?

You’ve copied it to the bottom of your sheet, maybe even included an IF statement for the blank rows, and now you want it to auto-calculate whenever new responses come in. When a response is collected through the form it adds a new row under your existing data, and any formulas in adjacent columns get bumped down a row rather than being calculated. However, this is a perfect use case for an Array Formula (never heard of these before? In the following example, I’ve set up a simple Google Form which asks a user to submit a single number between 1 and 100.

If you wish to work with a Google Spreadsheet without authenticating, not only must the Spreadsheet in question be visible to the web, but it must also have been explicitly published using "File This is a 2-legged oauth method and designed to be "an account that belongs to your application instead of to an individual end user".

Use this for an app that needs to access a set of documents that you have full access to.

The way to think of it is that in the first row, we effectively have this formula: (or whatever your top row is) and it will auto-fill the whole column.

Whenever a form response is added to the Sheet, a new number appears in column B and that cell is no longer blank.

cell C2): All of these will expand to fill out the entire column, displaying values for any rows that have numbers in column B.

The token will be automatically regenerated when it expires. Many of the calls are accessible from the main Spreadsheet object by passing in a sheet ID (see above), but some functionality is only available on the Worksheet object because it requires various URLs only known after fetching the sheets for making requests. You can treat the row as a normal javascript object.

Object keys will be from the header row of your sheet, however the google API mangles the names a bit to make them simpler.

It's easiest if you just use all lowercase keys to begin with.

See limitations above for notes about Google's row-based API!

Google spreadsheet formula not updating