从Google Sheets脚本重新排列Firebase实时数据库中显示的值

debugcn 发表于 Dev

卡洛斯·克雷格（Carlos Craig）

我想更改我的值在Firebase实时数据库中的显示方式。它们显示为数字，我希望显示该列的标题。

脚本文字：

var secret = 'xxxx'


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 (

    'https://no-excusas.firebaseio.com/' +

    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('Users')


  /*
  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()


  //Use the syncMasterSheet function defined before to push this data to the "masterSheet" key in the firebase database

  syncMasterSheet(data)

}

Firebase数据库的屏幕截图：

电子表格的屏幕截图：

抢劫犯

数据将以您发送的格式推送到Firebase数据库中-的返回值为Range.getValues()a Object[][]（请参见此处）。

解决方法是将数据整理成所需的格式。根据您的描述，我认为数组的顶层是数字（例如，数组索引）是可以的，但是您希望每个项目的内部都被标记为与列相同。

我不会复制所有代码，只复制需要更改的2个函数。首先，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.
  // Get the headers in a separate array.
  var headers = sheet.getRange(1, 1, 1, columns).getValues()[0];  // [0] to unwrap the outer array
  var data = sheet.getRange(2, 1, rows - 1, columns).getValues();  // skipping the header row means we need to reduce rows by 1.

  //Use the syncMasterSheet function defined before to push this data to the "masterSheet" key in the firebase database

  syncMasterSheet(headers, data)
}

其次，syncMasterSheet()应该在执行PUT之前构建您要PUT的对象：

function syncMasterSheet(sheetHeaders, sheetData) {
  /*
  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
  */              
  const outputData = [];
  for(i = 0; i < sheetData.length; i++) {
      var row = sheetData[i];
      var newRow = {};
      for(j = 0; j < row.length; j++) {
        newRow[sheetHeaders[j]] = row[j];
      }
      outputData.push(newRow);
  }

  var options = {
    method: 'put',
    contentType: 'application/json',
    payload: JSON.stringify(outputData)
  }

  var fireBaseUrl = getFirebaseUrl('SpreadsheetTest')

  /*
  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)
}

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。