自动将Google表格自动同步到Firebase，无需按钮 [英] Auto Sync google sheets to firebase without button

问题描述

我使用了一个教程来帮助我使用激活脚本的SYNC按钮将我的工作表同步到firebase.SYNC按钮当前位于电子表格的中间.进行更改后，我想将工作表中的数据自动同步到Firebase.

  function getFirebaseUrl(jsonPath){返回 ('https://no-excusas.firebaseio.com/'+jsonPath +'.json?auth ='+秘密)}函数syncMasterSheet(sheetHeaders，sheetData){/*我们发出一个PUT(更新)请求，并发送JSON有效负载有关REST API的更多信息，请访问: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 = {方法:放入"，contentType:"application/json"，有效负载:JSON.stringify(outputData)}var fireBaseUrl = getFirebaseUrl("UsersSheets")UrlFetchApp.fetch(fireBaseUrl，选项)}函数startSync(){//获取当前活动的工作表var sheet = SpreadsheetApp.getActiveSheet()//获取包含某些内容的行数和列数var [行，列] = [sheet.getLastRow()，sheet.getLastColumn()]//获取这些行和列中包含的数据作为二维数组.//在单独的数组中获取标题.var headers = sheet.getRange(1、1、1列).getValues()[0];//[0]解开外层阵列var data = sheet.getRange(2，1，行-1，列).getValues();//跳过标题row表示我们需要将行数减少1.//使用之前定义的syncMasterSheet函数将数据推送到"masterSheet"键入Firebase数据库syncMasterSheet(标题，数据)} 

解决方案

通常，可以在代码中定义 onEdit 函数，如下所示:

  function onEdit(event){startSync();} 

但是，由于您是通过 UrlFetchApp.fetch()发出外部请求的，因此此

然后，选择添加触发器"并创建编辑时的触发器，如下所示:

您应该考虑是否真的希望在每次编辑上运行此代码，因为请求可能很大(因为它同步了整个工作表)，并且经常运行(在编辑时).

I used a tutorial to help me sync my sheets to firebase with the use of a SYNC button that activates the script. The SYNC button currently sits just in the middle of the spreadsheet. I want to sync the data from sheets automatically to firebase when there are changes made.

function getFirebaseUrl(jsonPath) {

return (
'https://no-excusas.firebaseio.com/' +
jsonPath +
'.json?auth=' +
secret
  )
}



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("UsersSheets")


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

解决方案

Normally, it would be ok to just define an onEdit function in your code, like this:

function onEdit(event) {
  startSync();
}

However, because you are making external requests via UrlFetchApp.fetch(), this will fail with an error about not having the https://www.googleapis.com/auth/script.external_request permission (gobs more detail about trigger authorization here).

Instead, you need to manually create an installable trigger

This is reasonably straightforward. In the edit menu for your code, go to your project's triggers:

Then, select "add a trigger" and create the on edit trigger, like so:

You should think about if you really want this running on every edit as the requests could be quite large (as it syncs the entire sheet) and run frequently (as you edit), however.

这篇关于自动将Google表格自动同步到Firebase，无需按钮的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！