javascript - 如何从谷歌表格中获取数据作为字典数组

Question

我很难将行和列中包含的数据格式化为字典数组。以下是我要实现的格式:

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

这是我的 google 表格脚本:

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.com/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.com/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)
}

我需要函数 startSync() 来将 var data 设置为我想要的格式。 :)

Answer 1

• 您想将电子表格的值转换为 [{name: "Apple", health: "fruit", price: 50, url: "example.com"},,,]。
• 您想使用 Google Apps 脚本实现这一目标。

如果我的理解是正确的，这个示例脚本怎么样？

示例脚本:

请修改startSync()函数如下。

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

引用资料:

• map()
• reduce()