|     Inicio    |   |         |  |   FOROS      |  |      |      
   Elastix - VoIP B4A (Basic4Android) App inventor 2 PHP - MySQL
  Estación meteorológica B4J (Basic4Java) ADB Shell - Android Arduino
  Raspberry Pi Visual Basic Script (VBS) FireBase (BD autoactualizable) NodeMCU como Arduino
  AutoIt (Programación) Visual Basic Cosas de Windows Webs interesantes
Translate:
Búsqueda en este sitio:


.

App inventor 2 en español
Cómo programar los teléfonos móviles con Android
mediante App inventor 2 - Juan Antonio Villalpando

-- Tutorial de iniciación de App Inventor 2 en español --

Volver al índice del tutorial

____________________________

8M.- FirebaseDB. Realtime. Sincronización con Google Sheets.

p8M_firebaseDB_GoogleSheets.aia

- Tenemos una base de datos en Google Sheets.

- Queremos que cuando modifiquemos o creemos un dato, se modifique también en Firebase Realtime Database.

- Y además también cambie automáticamente en la aplicación construida con App Inventor.

- Vamos a seguir este tutorial:

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

- En la base de datos vamos a: Extensiones / App Script.

- En App Script creamos o modificacamos el archivo Code.gs con este contenido:

Code.gs

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

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

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

// Write the data to the Firebase URL
function writeDataToFirebase(sheetID) {
 var ss = SpreadsheetApp.openById(sheetID);
 SpreadsheetApp.setActiveSpreadsheet(ss);
 createSpreadsheetEditTrigger(sheetID);
 // MODIFICADO:
 //  var sheets = ss.getSheets();
 var sheets = ss.getSheetByName('hoja1');
 for (var i = 0; i < sheets.length; i++) {
   importSheet(sheets[i]);
   SpreadsheetApp.setActiveSheet(sheets[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);
}

- Luego creamos appsscript.json con este contenido:

appsscript.json

{
"timeZone": "America/Los_Angeles",
  "dependencies": {
    "libraries": [{
      "userSymbol": "FirebaseApp",
      "libraryId": "1hguuh4Zx72XVC1Zldm_vTtcUUKUA6iBUOoGnJUWLfqDWx5WlOJHqYkrt",
      "version": "29",
      "developmentMode": true
    }]
  },
  "exceptionLogging": "STACKDRIVER",
  "oauthScopes": ["https://www.googleapis.com/auth/firebase.database", "https://www.googleapis.com/auth/userinfo.email", 

"https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/script.scriptapp",
"https://www.googleapis.com/auth/script.external_request"], "executionApi": { "access": "DOMAIN" } }

- Reglas de permisos en Firebase:

- Cuando vamos al Script y lo ejecutamos obtenemos:

- App Inventor

_______________
- Diseño.

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

ProjectBucket:
hoja1

_______________
- Bloques.

_____________________________________

2.- Enviamos los datos desde la aplicación con una extensión.

_______________
- Diseño.

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

_______________
- Bloques.

_______________
- Google Sheets.

_______________
- App Script.

- Añado al código original Code.gs, la parte correspondiente a la extensión de TIMAI2 que puedes obtener en:

https://community.appinventor.mit.edu/t/free-google-sheets-for-appinventor-replicant-using-google-apps-script/54411

Code.gs

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

- He modificado la línea indicada para que cada vez que inserte una fila reinicie la sincronización.

- Cada vez que modifiques este archivos es conviniente pulsar en "Implementar" y volver a poner la dirección obtenida en el ScriptUrl de la extensión.

_______________
- Siguiente tutorial. Formulario.

- En el siguiente tutoria enviaremos la información de un Formulario 8N_firebaseDB_Googlesheets_2.htm

__________________________________

 

- Mi correo:
juana1991@yahoo.com
- KIO4.COM - Política de cookies. Textos e imágenes propiedad del autor:
© Juan A. Villalpando
No se permite la copia de información ni imágenes.
Usamos cookies propias y de terceros que entre otras cosas recogen datos sobre sus hábitos de navegación y realizan análisis de uso de nuestro sitio.
Si continúa navegando consideramos que acepta su uso. Acepto    Más información