Sync Google Sheets to a Firebase Realtime Database

[Juan_Antonio]

We have a Google Sheets:

We want that when a data is changed in Google sheets, it is modified in real time in a Firebase Realtime Database.

And also in an app with the Firebase component.

We will follow this tutorial:

https://medium.com/firebase-developers/sheets-to-firebase-33132e31935b

[Juan_Antonio]

1.- Codes.

Copy/Paste
https://gist.github.com/edwinlee/85ac9033a133d056a8ded6b74f27f30f#file-code-gs

appsscript.json

Code.gs

I have modified this line: getSheetByName

// MODIFICADO:
 //  var sheets = ss.getSheets();
 var sheets = ss.getSheetByName('hoja1');

[Juan_Antonio]

2.- Rules Firebase.

- Script in Google sheets.

- When Run --> initialize

[Juan_Antonio]

3.- App Inventor.

FirebaseURL:
h t t p s://kio4b-60980.firebaseio.com/1h2jp6a8NkOZqU2sSKeU5wPt3nmRicZ-MXAEEQrQXcU4

ProjectBucket:
hoja1

[Juan_Antonio]

4.- Send the information from a Google Sheets Form to Firebase Realtime.

Through this Youtube video tutorial we can learn to send information from a Form-Sheet to a Data-Sheet in Google Sheets:

- YouTube

• I've added a "To Firebase" button to send the cells content to Firebase Realtime.
• This code sends the content of the cells to Firebase (but does not save the information to the Sheet).
  
  

- Script

// To Firebase
function ToFirebase() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formS = ss.getSheetByName('Form'); // Form Sheet
  var values =[[formS.getRange("B6").getValue(),
                formS.getRange("B8").getValue(),
                formS.getRange("B10").getValue(),  
                formS.getRange("D6").getValue(),
                formS.getRange("D8").getValue(),
                formS.getRange("D10").getValue()]];
  var firebaseUrl= "https://kio4b-60980.firebaseio.com/";
  var base = FirebaseApp.getDatabaseByUrl(firebaseUrl);
  base.setData("ToApp", values);
}

• Pressing the "To Firebase" button will bring the information to Firebase Realtime.
  

  

• It will also come to the app.
  

  

FirebaseURL:
h ttps://kio4b-60980.firebaseio.com/

ProjectBucket:
ToApp

• More information. Codes:
  App inventor. FirebaseDB. Realtime. Formulario.

[Juan_Antonio]

5.- Update Google Sheets with extension. Update Firebase with Script.

• Now we will use the great extension of @TIMAI2
  🟩 [FREE] Google Sheets for AppInventor (replicant) using Google Apps Script

• App sends data to Google Sheets using the extension. The Script Code.gs updates Firebase.
  Return information is sent.

Designer:

FirebaseURL:
h t t p s://kio4b-60980.firebaseio.com/1h2jp6a8NkOZqU2sSKeU5wPt3nmRicZ-MXAEEQrQXcU4

ProjectBucket:
hoja1

ScriptUrl:
h t t p s://script.google.com/macros/s/AKfycbzzfPmHSM-PkiJNXPjWZH7L3u-Yy9_t5v0EPBc2-TdcfkddzWeUG6V1es7aQkTZM60K1w/exec

SheetName:
hoja1

Blocks

Code.gs

• I have added the @TIMAI2 Script code to the Firebase update code.

Summary

// Initialize
function initialize(e) {
 writeDataToFirebase(getEnvironment().spreadsheetID);
}

function getEnvironment() {
 var environment = {
   spreadsheetID: "1h2jp6a8NkOZqU2sSKZZZZZZZZZZicZ-MXAEEQrQXcU4",
   firebaseUrl: "https://kio4b-60980.firebaseio.com/"
 };
 return environment;
}

// Write the data to the Firebase URL
function writeDataToFirebase(sheetID) {
 var ss = SpreadsheetApp.openById(sheetID);
 SpreadsheetApp.setActiveSpreadsheet(ss);
 createSpreadsheetEditTrigger(sheetID);
 var sheets = ss.getSheets();
 for (var i = 0; i < sheets.length; i++) {
   importSheet(sheets[i]);
   SpreadsheetApp.setActiveSheet(sheets[i]);
 }
}

// Creates a Google Sheets on change trigger for the specific sheet
function createSpreadsheetEditTrigger(sheetID) {
 var triggers = ScriptApp.getProjectTriggers();
 var triggerExists = false;
 for (var i = 0; i < triggers.length; i++) {
   if (triggers[i].getTriggerSourceId() == sheetID) {
     triggerExists = true;
     break;
   }
 }

 if (!triggerExists) {
   var spreadsheet = SpreadsheetApp.openById(sheetID);
   ScriptApp.newTrigger("importSheet")
     .forSpreadsheet(spreadsheet)
     .onChange()
     .create();
 }
}

// Delete all the existing triggers for the project
function deleteTriggers() {
 var triggers = ScriptApp.getProjectTriggers();
 for (var i = 0; i < triggers.length; i++) {
   ScriptApp.deleteTrigger(triggers[i]);
 }
}


// A utility function to generate nested object when
// given a keys in array format
function assign(obj, keyPath, value) {
 lastKeyIndex = keyPath.length - 1;
 for (var i = 0; i < lastKeyIndex; ++i) {
   key = keyPath[i];
   if (!(key in obj)) obj[key] = {};
   obj = obj[key];
 }
 obj[keyPath[lastKeyIndex]] = value;
}

// Import each sheet when there is a change
function importSheet() {
 var sheet = SpreadsheetApp.getActiveSheet();
 var name = sheet.getName();
 var data = sheet.getDataRange().getValues();

 var dataToImport = {};

 for (var i = 1; i < data.length; i++) {
   dataToImport[data[i][0]] = {};
   for (var j = 0; j < data[0].length; j++) {
     assign(dataToImport[data[i][0]], data[0][j].split("__"), data[i][j]);
   }
 }

 var token = ScriptApp.getOAuthToken();

 var firebaseUrl = getEnvironment().firebaseUrl + sheet.getParent().getId() + "/" + name;
 var base = FirebaseApp.getDatabaseByUrl(firebaseUrl, token);
 base.setData("", dataToImport);
}

//////////////////// EXTENSION TIMAI2 ///////////////////////////////
// https://ai2.metricrat.co.uk/guides/google-sheets-for-appinventor//
/////////////////////////////////////////////////////////////////////

function doGet(e) {

  var ss = SpreadsheetApp.openById(e.parameter.ID);
  var sh = ss.getSheetByName(e.parameter.SH);
  var fn = e.parameter.FN;
  var rg = sh.getDataRange().getValues();

  // enter row number and column number to return A1 Notation Cell reference
  if ( fn == 'getCellRef' ) {
    var ref = sh.getRange(e.parameter.ROW,e.parameter.COL).getA1Notation();
    return ContentService.createTextOutput(ref);
  } 
  
  // enter start and end row numbers and column numbers to return A1 Notation Range reference
  else if ( fn == 'getRangeRef' ) {
    var ref = sh.getRange(e.parameter.ROW,e.parameter.COL,e.parameter.ROW1,e.parameter.COL1).getA1Notation();
    return ContentService.createTextOutput(ref);
  }
  
  // enter A1 notation reference to return cell value
  else if ( fn == 'readCell' ) {
    var ref = sh.getRange(e.parameter.REF).getValue();
    return ContentService.createTextOutput(ref);
  }
  
  // enter row number to return all values in row as a list
  else if ( fn == 'readRow' ) {
    var ref = sh.getRange(parseInt(e.parameter.ROW),1,1,rg[0].length).getValues();
    return ContentService.createTextOutput(JSON.stringify(ref));
  }
  
  // enter column number (A=1/B=2/etc.) to return all values in column as a list
  else if ( fn == 'readCol' ) {
    var ref = sh.getRange(1,parseInt(e.parameter.COL),rg.length-1,1).getValues();
    return ContentService.createTextOutput(JSON.stringify(ref));
  }
  
  // enter A1 notation reference to return range values as a list
  else if ( fn == 'readRange' ) {
    var ref = sh.getRange(e.parameter.REF).getValues();
    return ContentService.createTextOutput(JSON.stringify(ref));
  }
  
  // returns all values on sheet, including headers
  else if ( fn == 'readSheet' ) {
    return ContentService.createTextOutput(JSON.stringify(rg));     
  }
 
  // outputs results for SQL query of all data  
  else if ( fn == 'querySheet' ) {
    var sql = e.parameter.SQL;
    var hdr = e.parameter.HDR;
    if ( hdr == 0 ) {
    var rgq = sh.getName() + "!" + sh.getDataRange().getA1Notation().replace('A1','A2');
    var qry = '=query(' + rgq + ';\"' + sql + '\";0)';
    } else if ( hdr == 1 ) {
    var rgq = sh.getName() + "!" + sh.getDataRange().getA1Notation();
    var qry = '=query(' + rgq + ';\"' + sql + '\";1)';
    }
    var ts = ss.insertSheet();
    var setQuery = ts.getRange(1,1).setFormula(qry);
    var getResult = ts.getDataRange().getValues();
    ss.deleteSheet(ts); 
    return ContentService.createTextOutput(JSON.stringify(getResult));
  }
  
  // enter row number to delete that row
  else if ( fn == 'deleteRow' ) {
    sh.deleteRow(parseInt(e.parameter.ROW));
    return ContentService.createTextOutput('Row ' + e.parameter.ROW + ' has been deleted');
  }
  
  // enter row number to delete that row
  else if ( fn == 'deleteCol' ) {
    sh.deleteColumn(parseInt(e.parameter.COL));
    return ContentService.createTextOutput('Column ' + e.parameter.COL + ' has been deleted');
  }
  
  // enter A1 notation reference to write cell value
  else if ( fn == 'writeCell' ) {
    sh.getRange(e.parameter.REF).setValue(e.parameter.DATA);
    return ContentService.createTextOutput('Value: ' + e.parameter.DATA + ' written to cell ' + e.parameter.REF);
  }
  
  // enter row number to write row values
  else if ( fn == 'writeRow' ) {
    var data = JSON.parse('[' + e.parameter.DATA + ']');
    sh.getRange(e.parameter.ROW,1,1,data[0].length).setValues(data);
    // NEXT LINE WAS MODIFIED:
    writeDataToFirebase(getEnvironment().spreadsheetID); // Re-initialize
    return ContentService.createTextOutput('Values: ' + e.parameter.DATA + ' written to row ' + e.parameter.ROW);
  }
  
  // enter row number to write row values
  else if ( fn == 'writeCol' ) {
    var data = JSON.parse(e.parameter.DATA);
    sh.getRange(1,e.parameter.COL,data.length,1).setValues(data);
    return ContentService.createTextOutput('Values: ' + e.parameter.DATA + ' written to column ' + e.parameter.COL);
  }
  
  // enter A1 notation reference to write cell values
  else if ( fn == 'writeRange' ) {
    var data = JSON.parse(e.parameter.DATA);
    var ref = sh.getRange(e.parameter.REF).setValues(data);
    return ContentService.createTextOutput('Values: ' + e.parameter.DATA + ' written to cells ' + e.parameter.REF);
  }
  
  // append row to end write row values
  else if ( fn == 'appendRow' ) {
    var data = JSON.parse('[' + e.parameter.DATA + ']');
    var lr = sh.getLastRow();
    sh.insertRowAfter(lr);
    rowNum = lr + 1;
    sh.getRange(rowNum,1,1,data[0].length).setValues(data);
    return ContentService.createTextOutput('Values: ' + e.parameter.DATA + ' appended');
  }
  
  // append column to end and write column values
  else if ( fn == 'appendCol' ) {
    var data = JSON.parse(e.parameter.DATA);
    var lc = sh.getLastColumn();
    sh.insertColumnAfter(lc);
    colNum = lc + 1;
    sh.getRange(1,colNum,data.length,1).setValues(data);
    return ContentService.createTextOutput('Values: ' + e.parameter.DATA + ' appended');
  }
  
  // enter A1 notation reference and formula to set formula to cell
  else if ( fn == 'insertFormula' ) {
    var ref = sh.getRange(e.parameter.REF).setValue(e.parameter.DATA);
    return ContentService.createTextOutput('Formula: ' + e.parameter.DATA + ' inserted to  ' + e.parameter.REF);
  }
}

• I have added this line so that it will restart the Firebase update when a writeRow is modified. You can make other more methodical modifications.
  

  

• It's a good idea to create a new address for the ScriptUrl, using Deploy, every time you make changes.