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
__________________________________
|