时间戳

首页 > 解决方案 > 如何从谷歌表格中获取数据作为字典数组

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

问题描述

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

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

这是我的谷歌表格脚本：

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等于我的预期格式。:)

标签： javascriptfirebasegoogle-apps-script

解决方案

  • 您想将电子表格的值转换为[{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);
}

参考：

推荐阅读