Skip to content

Instantly share code, notes, and snippets.

@tanaikech
Last active July 26, 2023 16:32
  • Star 2 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
Star You must be signed in to star a gist
Embed
What would you like to do?
Converting Values of Google Spreadsheet to Object using Google Apps Script

Converting Values of Google Spreadsheet to Object using Google Apps Script

This is a sample script for converting the values of Google Spreadsheet to an object using Google Apps Script.

Sample script

function myFunction() {
  const sheetName = "Sheet1";
  const [headers, ...rows] = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getDataRange().getValues();
  const res = rows.map((r) => headers.reduce((o, h, j) => Object.assign(o, { [h]: r[j] }), {}));
  console.log(res);
}
  • When this script is run, the above sample image can be retrieved.

  • In this sample script, the 1st row of the sheet is used as the header row.

  • const res = rows.map((r) => headers.reduce((o, h, j) => Object.assign(o, { [h]: r[j] }), {})); can be also replaced with const res = rows.map((r) => headers.reduce((o, h, j) => (o[h] = r[j], o), {}));.

  • Also, const res = rows.map((r) => headers.reduce((o, h, j) => Object.assign(o, { [h]: r[j] }), {})); can be replaced with const res = rows.map((r) => Object.fromEntries(headers.map((h, j) => [h, r[j]])));.

Added 1

When you want to convert a 2-dimensional array to an object array and vice versa, you can also use the following script.

function myFunction() {
  const sheetName = "Sheet1";
  const [headers, ...rows] = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getDataRange().getValues();
  
  // Convert 2-dimentional array to object array.
  const obj = rows.map((r) => headers.reduce((o, h, j) => (o[h] = r[j], o), {}));
  console.log(obj);
  
  // Convert object aray to 2-dimensional array.
  const ar = [headers, ...obj.map(e => headers.map(h => e[h]))];
  console.log(ar);
}

Added 2

For example, for the above sample Spreadsheet, when the same header titles are included, the sample script is as follows.

function myFunction() {
  const sheetName = "Sheet1";
  const [headers, ...rows] = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getDataRange().getValues();
  const obj = rows.map((r) => headers.reduce((o, h, j) => (o[h] = o[h] ? [...o[h], r[j]] : [r[j]], o), {}));
  console.log(obj);
}

When this script is run, each value is put into an array like [{"a1":["a2"],"b1":["b2a","b2b","b2c"],"c1":["c2"],"d1":["d2"],"e1":["e2"]},,,,].

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment