Google Sheets

✅ Auth	✅ Writes	❌ Uploads

Use Google Sheets as a data source and storage. Collaborate with others on the same public or private spreadsheet simultaneously, using formulas and functions. And then use the obtained data in your app.

Setting up

Share a spreadsheet and use the provided URL which has format like https://docs.google.com/spreadsheets/d/14bzCuziKutrA3iESarKoj2o56dhraR8pzuFAuwTIo-g/edit?usp=sharing.

Or simply open your spreadsheet in a browser and use the URL from the address bar. In this case, the URL has format like https://docs.google.com/spreadsheets/d/1IMFDv0aWWZ8F4GIdk_gmOwl60DD4-eCnLEX1CV9WBho/edit#gid=0.

Basics

By default, Madata returns data from the spreadsheet as an array of arrays, where each element corresponds to a row of data. If you specify the headerRow or keys constructor option (see below), Madata will return an array of objects instead.

Keep in mind that you must be logged in to write data back to the spreadsheet, and the spreadsheet permissions should allow this operation.

To read data from and write it back to a private spreadsheet, you must be logged in. Madata will not let you work with others' private spreadsheets, only yours.

Constructor options

Option	Type	Default value	Description
`sheet`	`String`	–	The title of a sheet to work with. If not provided, Madata will try to use the sheet specified via the `sheetIndex` option or in the URL. If it fails, Madata will try to use the first visible sheet.
`sheetIndex`	`Number`	–	An index (starting from `0`) of the sheet to work with.
`range`	`String`	–	A range with data in A1 notation. If not provided, Madata will try to use all the data on the sheet.
`headerRow`	`Boolean`	`false`	Whether the first row of data is a row with column headings. If so, Madata will return an array of objects where each object corresponds to one row of data in the spreadsheet. Column headings will become keys, and the values of the cells will be values. In this case, the header row won't be a part of the returned data.
`transpose`	`Boolean`	`false`	Whether to transpose data before returning it. This option might be helpful, e.g., when your data has headings not in the first row, but in the first column. Simply transpose the data so that the first column becomes the first row, the second column becomes the second row, and so on.
`keys`	`Array<String>` \| `Function`	–	If specified, Madata will return an array of objects with the provided (or generated by a mapping function) keys where each object corresponds to one row of data in the spreadsheet. It is helpful when the data has no headings or cannot be used as object keys. If your data has more columns than the keys you specified (or generate with the function), Madata will use a corresponding (zero-based) index of the column as the default object key. A mapping function takes a column heading, its index in the array of headings, and the array of headings and returns a string - object key. You can use `GoogleSheets.keys()` to return a unique object key if there are duplicates among headings.
`allowAddingSheets`	`Boolean`	`false`	Whether to add a new sheet on save if there is no sheet with the specified title or index in the spreadsheet.
`serializeDates`	`Boolean`	`false`	Whether dates, times, and durations should be represented as strings in their given number format (which depends on the spreadsheet locale). For example, instead of the default `44963`, Madata might return `2/6/2023 12:15:00`.
`smartValues`	`Boolean`	`false`	Whether the strings will be parsed (as formulas, booleans, or numbers) as if the user typed them into a cell via the Google Sheets UI. For example, the `Mar 1, 2016` string becomes a date, and `=1+2` becomes a formula. Formats can also be inferred, so `$100.15` becomes a number with currency formatting.

A1 notation for specifying cell ranges

This is a string like A1:B2 that refers to a group of cells in the sheet and is typically used in formulas. For example, valid ranges are:

A1:B2 refers to the first two cells in the top two rows of the sheet.
A:C refers to all the cells in the first three columns of the sheet.
1:2 refers to all the cells in the first two rows of the sheet.
A5:A refers to all the cells of the first column of the sheet, from row 5 onward.
C2:2 refers to all the cells of the second row of the sheet, from column C onward.

Named ranges are also supported.

Google Sheets version history

With the backend, you can take advantage of the Google Sheets version history system. Before storing the data back, simply replace unchanged data with null, and the Google Sheets backend will leave them untouched in the sheet.

If you want to remove data from the sheet (i.e., clear the corresponding cell), replace every piece of data which needs to be deleted with an empty string before you store the data.

Example

let backend = Backend.from(
	"https://docs.google.com/spreadsheets/d/1IMFDv0aWWZ8F4GIdk_gmOwl60DD4-eCnLEX1CV9WBho/edit?usp=sharing",
	{ sheetIndex: 0, headerRow: true, keys: ["item", "cost", "stocked", "ship_date"] }
);

let data = backend.load();
console.log(data);