I'm having difficulty formatting the data contained in rows and columns as an array of dictionaries. Below is the format I'm trying to achieve:

[[id: "abchdha", name: "Orange", health: "fruit", price: 50], 
[id: "123fsf", name: "Apple", health: "fruit", price: 50]]

Here is my google sheets script:

var secret = "mysecretcode"

function getFirebaseUrl(jsonPath) {
  /*
  We then make a URL builder
  This takes in a path, and
  returns a URL that updates the data in that path
  */
  return (
    'myfirebaselink' +
    jsonPath +
    '.json?auth=' +
    secret
  )
}

function syncMasterSheet(excelData) {
  /*
  We make a PUT (update) request,
  and send a JSON payload
  More info on the REST API here : 
  */
  var options = {
    method: 'put',
    contentType: 'application/json',
    payload: JSON.stringify(excelData)
  }
  var fireBaseUrl = getFirebaseUrl('masterSheet')

  /*
  We use the UrlFetchApp google scripts module
  More info on this here : 
  */
  UrlFetchApp.fetch(fireBaseUrl, options)
}

function startSync() {
  //Get the currently active sheet
  var sheet = SpreadsheetApp.getActiveSheet()
  //Get the number of rows and columns which contain some content
  var [rows, columns] = [sheet.getLastRow(), sheet.getLastColumn()]
  //Get the data contained in those rows and columns as a 2 dimensional array
  var data = sheet.getRange(1, 1, rows, columns).getValues()

  syncMasterSheet(data)
}

I need the function startSync() to set var data equal to my intended format. :)

I'm having difficulty formatting the data contained in rows and columns as an array of dictionaries. Below is the format I'm trying to achieve:

[[id: "abchdha", name: "Orange", health: "fruit", price: 50], 
[id: "123fsf", name: "Apple", health: "fruit", price: 50]]

Here is my google sheets script:

var secret = "mysecretcode"

function getFirebaseUrl(jsonPath) {
  /*
  We then make a URL builder
  This takes in a path, and
  returns a URL that updates the data in that path
  */
  return (
    'myfirebaselink' +
    jsonPath +
    '.json?auth=' +
    secret
  )
}

function syncMasterSheet(excelData) {
  /*
  We make a PUT (update) request,
  and send a JSON payload
  More info on the REST API here : https://firebase.google./docs/database/rest/start
  */
  var options = {
    method: 'put',
    contentType: 'application/json',
    payload: JSON.stringify(excelData)
  }
  var fireBaseUrl = getFirebaseUrl('masterSheet')

  /*
  We use the UrlFetchApp google scripts module
  More info on this here : https://developers.google./apps-script/reference/url-fetch/url-fetch-app
  */
  UrlFetchApp.fetch(fireBaseUrl, options)
}

function startSync() {
  //Get the currently active sheet
  var sheet = SpreadsheetApp.getActiveSheet()
  //Get the number of rows and columns which contain some content
  var [rows, columns] = [sheet.getLastRow(), sheet.getLastColumn()]
  //Get the data contained in those rows and columns as a 2 dimensional array
  var data = sheet.getRange(1, 1, rows, columns).getValues()

  syncMasterSheet(data)
}

I need the function startSync() to set var data equal to my intended format. :)

• javascript
• firebase
• google-apps-script

Share Improve this question Follow edited Aug 1, 2019 at 7:54 Tanaike 202k1212 gold badges120120 silver badges213213 bronze badges asked Jul 30, 2019 at 10:51 ChrisChris 5511 silver badge88 bronze badges 16

• In order to correctly understand about the value of data of var data = sheet.getRange(1, 1, rows, columns).getValues(), can you provide a sample Spreadsheet? Of course, please remove your personal information. – Tanaike Commented Jul 31, 2019 at 0:00
• Sorry! I put a ment earlier but I guess it didn't post. Here it is. Also here is my Firebase database. The top branch under node food is what I want my data to look like, and the bottom is the current output. – Chris Commented Jul 31, 2019 at 1:45
• Thank you for replying and adding the information. But unfortunately, I couldn't understand about the relationship between your image and the value of [[id: "abchdha", name: "Orange", health: "fruit", price: 50], [id: "123fsf", name: "Apple", health: "fruit", price: 50]]. – Tanaike Commented Jul 31, 2019 at 1:48
• @Tanaike I edited the prior ment with a firebase snapshot if that helps? here – Chris Commented Jul 31, 2019 at 1:50
• Thank you for replying. I might have mented while you editing your ment. I apologize for this. In your situation, the Spreadsheet has the column "A" to "D", and for [id: "abchdha", name: "Orange", health: "fruit", price: 50], the column "A", "B", "C" and "D" are name, health, price and undefined, respectively. If my understanding is correct, where is id? By the way, [id: "abchdha", name: "Orange", health: "fruit", price: 50] is not an object. Can you provide the input and output you want by adding the text data to your question? – Tanaike Commented Jul 31, 2019 at 1:58

 |  Show 11 more ments

1 Answer 1

Sorted by: Reset to default 6

• You want to convert the values of Spreadsheet to [{name: "Apple", health: "fruit", price: 50, url: "example."},,,].
• You want to achieve this using Google Apps Script.

If my understanding is correct, how about this sample script?

Sample script:

Please modify the function of startSync() as follows.

function startSync() {
  //Get the currently active sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  //Get the number of rows and columns which contain some content
  var [rows, columns] = [sheet.getLastRow(), sheet.getLastColumn()];
  //Get the data contained in those rows and columns as a 2 dimensional array
  var data = sheet.getRange(1, 1, rows, columns).getValues();

  // I modified below script.
  var header = data[0];
  data.shift();
  var convertedData = data.map(function(row) {
    return header.reduce(function(o, h, i) {
      o[h] = row[i];
      return o;
    }, {});
  });

  syncMasterSheet(convertedData);
}

References:

• map()
• reduce()