How To Convert A Google Sheets Database Into An Array Of Objects Using Apps Script

Kieran Dixon

Updated: April 22, 2022

Google Sheets is a great place to store data.

Its rows and columns lend themselves beautifully to a simple database setup:

	A	B	C	D
1	id	first_name	last_name	company
2	1	Teresa	Butterly	Brainbox
3	2	Conn	Tutsell	Voonyx
4	3	Caterina	Curtoys	Zazio
5	4	Papagena	Eburne	Lazzy
6	5	Fabien	Glendenning	Shuffletag
7	6	Hildegaard	Janda	Skimia
8	7	Mateo	Jeal	Zoonder
9	8	Rodrick	Aldersley	Talane
10	9	Marion	Pech	Topdrive

When using Google Apps Script to access this data, the following code get you all of this data in a two-dimensional array pretty quickly:

Dark theme

Copy code

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('database');
const values = sheet.getDataRange().getValues();

Here's what the values constant looks like:

Dark theme

Copy code

const values = [
  ['id', 'first_name', 'last_name', 'company'],
  [1, 'Teresa', 'Butterly', 'Brainbox'],
  [2, 'Conn', 'Tutsell', 'Voonyx'],
  [3, 'Caterina', 'Curtoys', 'Zazio'],
  [4, 'Papagena', 'Eburne', 'Lazzy'],
  [5, 'Fabien', 'Glendenning', 'Shuffletag'],
  [6, 'Hildegaard', 'Janda', 'Skimia'],
  [7, 'Mateo', 'Jeal', 'Zoonder'],
  [8, 'Rodrick', 'Aldersley', 'Talane'],
  [9, 'Marion', 'Pech', 'Topdrive']
]

This data structure isn't the most useful. What would be great is if it was an array of objects with the fields ('id', 'first_name', 'last_name', 'company') as keys.

Here's one way to achieve just that:

Dark theme

Copy code

function databaseAsObjectArray() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('database');
  const [headers, ...data] = sheet.getDataRange().getValues();

  const databaseAsObjectArray = data.map(row => {
    return row.reduce((acc, value, i) => {
      const key = headers[i];
      if (key === '') return acc;
      return { ...acc, [key]: value };
    }, {});
  });

}

The values constant from the previous code block is destructured into two variables (headers and data).

Each row of the data is then mapped with a reduction of the row into a single object (acc) with the relevant header (headers[i]) as the key and the relevant value as the… value.

If for some reason a header is blank, that field is ignored and not added to the entry objects: if (key === '') return acc;

The databaseAsObjectArray constant is:

Dark theme

Copy code

const databaseAsObjectArray = [
  { id: 1, first_name: "Teresa", last_name: "Butterly", company: "Brainbox" },
  { id: 2, first_name: "Conn", last_name: "Tutsell", company: "Voonyx" },
  { id: 3, first_name: "Caterina", last_name: "Curtoys", company: "Zazio" },
  { id: 4, first_name: "Papagena", last_name: "Eburne", company: "Lazzy" },
  { id: 5, first_name: "Fabien", last_name: "Glendenning", company: "Shuffletag" },
  { id: 6, first_name: "Hildegaard", last_name: "Janda", company: "Skimia" },
  { id: 7, first_name: "Mateo", last_name: "Jeal", company: "Zoonder" },
  { id: 8, first_name: "Rodrick", last_name: "Aldersley", company: "Talane" },
  { id: 9, first_name: "Marion", last_name: "Pech", company: "Topdrive" }
];

This is far more useful as when iterating over the data you can simply retrieve a value using the database fields.

Here's another way of doing the exact same thing using for loops:

Dark theme

Copy code

function getDatabaseAsObjectArray2() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('database');
  const values = sheet.getDataRange().getValues();
  const headers = values[0];
  const databaseAsObjectArray = [];

  for (let i = 1; i < values.length; i++) {
    const entry = {};
    for (let j = 0; j < headers.length; j++) {
      const key = headers[j];
      if (key === '') continue;
      entry[key] = values[i][j];
    }
    databaseAsObjectArray.push(entry);
  }
  
}

For each row of data (note that in the outside loop i = 1 to start) an object is created before the columns are looped over to add the key-value pairs (again, ignoring blank headers).

There are many ways to achieve the same result when coding, feel free to write your own or pick the one of these two methods that's more readable to you.

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

You'll get updates from me with an easy-to-find "unsubscribe" link.

Kieran Dixon started using spreadsheets in 2010. He leveled-up his skills working for banks and running his own business. Now he makes Google Sheets and Apps Script more approachable for anyone looking to streamline their business and life.