Google Sheets Cheat Sheet

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

Google Sheets is a great place to store data.

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

ABCD
1idfirst_namelast_namecompany
21TeresaButterlyBrainbox
32ConnTutsellVoonyx
43CaterinaCurtoysZazio
54PapagenaEburneLazzy
65FabienGlendenningShuffletag
76HildegaardJandaSkimia
87MateoJealZoonder
98RodrickAldersleyTalane
109MarionPechTopdrive

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;
      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;

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.


hand pointing emoji hand pointing emoji

FREE RESOURCE

Google Sheets Cheat Sheet

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

Google Sheets Cheat Sheet

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.

Want Better-Looking Google Sheets?

Google Sheets Cheat Sheet

Get my 12-tip cheat sheet that will make your spreadsheets more user-friendly.

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

πŸ—™