Utilizar Google Sheet como base de datos

En este artículo voy a tratar de explicar de forma detallada cómo utilizar Google Sheet, que es la hoja de cálculo online de Google, como base de datos para nuestra aplicación. Por ahora, los datos se introducirán directamente en Google Sheet y la app los descargará en formato JSON.

En el ejemplo que voy a realizar, voy a añadir a mi app QR Commands una nueva funcionalidad que consiste en consultar una FAQ que he obtenido de la comunidad de GoPro Labs. La hoja de cálculo se denomina tips y consta de varias campos (columnas): date, title, description, link, keyword,type y version.

Google Apps Script

Para que esta hoja de cálculo sea accesible desde internet y que nos devuelva el resultado en formato JSON, he de utilizar Google Apps Script (GAS), que es una plataforma para el desarrollo de aplicaciones web con acceso a casi todas las librerías de G Suite. Algunas de estas incluyen Gmail, Drive, Google Sheets, Google Docs, entre otras.

Lo único que tienes que hacer es escribir javascript. No hay nada que instalar. GAS te provee de un editor de código online que se ejecuta en los servidores de Google.

Tipos de script

En GAS existen tres tipos de script: standalone, container-bound y web apps. Los standalone scripts son recomendados para tareas de utilidad, por ejemplo, obtener el número de eventos registrados en Google Calendar para la siguiente semana y enviarte un email con los resultados. Este tipo de scripts pueden ser ejecutados de manera manual o automáticamente cada cierto tiempo.

Los container-bound scripts son accesibles desde Google Sheets, Docs, Slides y Forms. Son utilizados para extender la funcionalidad de estas aplicaciones. El comportamiento de este tipo de script es muy similar al de las macros.

Los scripts de tipo web apps permiten exponer en la red la funcionalidad que hayas implementado. Para utilizarla, basta con hacer peticiones http a una url que nos entrega GAS. Este tipo de script debe contener una función doGet(e)o doPost(e)y retornar un objeto de tipo HtmlOutputo TextOutput.

Creación del script

Vas a crear un script de tipo web app para que el contenido de la primera hoja de Google Sheet sea entregado al usuario que hace la petición en formato JSON. Para ello, visita la página principal de GAS y haz clic en Nuevo Proyecto en el menú lateral. Esto te llevará a la consola de proyectos, donde crearemos nuestro script.

El código que has de introducir es el siguiente:

function getData(id) {

  var spreadsheet = SpreadsheetApp.openById(id);

  var sheet = spreadsheet.getSheets()[0];

  var values = sheet.getDataRange().getValues();

  var headers = values.shift();

  var data = { data: [] };

  

  for (var i = 0; i < values.length; i++) {

    var row = values[i];

    var register = {};

    for (var j = 0; j < row.length; j++) {

      register[headers[j]] = row[j]

    }

    data.data.push(register);

  }

  

  var json = JSON.stringify(data);

  

return ContentService.createTextOutput(json).setMimeType(ContentService.MimeType.JSON);

}

function doGet(request) {

  if (request.parameter.id !== undefined){

    return getData(request.parameter.id);    

  }else{

    return ContentService.createTextOutput(JSON.stringify({error:"ERROR"}));

  }

}

Aunque no es motivo de este artículo, voy a tratar de resumir el funcionamiento de este código. Consta de dos funciones, doGet() y getData()

Cuando llamamos al script lo hacemos utilizando doGet() y le enviamos como parámetro el id de la hoja de cálculo. El id es el código que aparece en la url después de https://docs.google.com/spreadsheets/d/ hasta /edit#gid=0 cuando abrimos una hoja de cálculo en Google Sheet. Si este código está presente en la petición https devolverá el resultado de ejecutar la función getData().

getData() se encarga de abrir la hoja de cálculo y devolver un objeto que contine un array en formato JSON con el contenido de la primera hoja (0).

A continuación debemos publicar el script en formato web app. Para ello, en el menú superior seleccionamos Publicar - Implementar como aplicación web.

En el campo Who has access to the app debemos seleccionar entre la diferentes opciones. Si deseas que cualquiera pueda ejecutar el script has de seleccionar Anyone, even anonymous.

Durante la fase de publicación debemos autorizar la ejecución del script:

Una vez haz hecho click en Desploy, nos aparecerá la url que debemos llamar desde nuestra web añadiendo el id de la hoja de cálculo. Por ejemplo, en mi caso sería (he ocultado parte de la url]:

https://script.google.com/macros/s/AKfycbz********Jnw4pUUIDUU-VjrViXH7ahgqN1********EX/exec?id=[id de la hoja de cálculo]

El resultado obtenido sería:

{"data": [{"date": "2021-01-07T00:00:00.000Z","title": "Sleep and WiFi","description": "You can't use the WiFi during a Labs induced sleep. The camera is completely off to extend battery, WiFi is a significant battery drain.","link": "https://community.gopro.com/t5/GoPro-Labs/QR-codes-and-camera-disappearing-camera-not-found/m-p/922370","keyword": "WiFi","type": "Information","version": "1.70.75"},{"date": "2020-12-21T00:00:00.000Z","title": "Delayed Capture","description": "Is it possible to start filming for example 4 times a day for 10 minuttes? At 09:00, 12:00, 15:00 and 18:00? And the camera shut down in between? Yes. This command would do that, and repeat each day.!09:00S!600E!12:00S!600E!15:00S!600E!18:00S!600E!R","link": "https://community.gopro.com/t5/GoPro-Labs/Delayed-capture/m-p/892133","keyword": "Others","type": "Information","version": "1.70.75"}...}

]}

Código en Android

En android, debemos crear una estructura de datos como la de la hoja de cálculo. Para ello, creamos una nueva clase denominada TipsObjectModel:

public class TipsObjectModel {
    private String date;
    private String title;
    private String description;
    private String link;
    private String keyword;
    private String type;
    private String version;

    public TipsObjectModel(String date, String title, String description, 
    String link, String keyword, String type, String version) {
        this.date = date;
        this.title = title;
        this.description = description;
        this.link = link;
        this.keyword = keyword;
        this.type = type;
        this.version = version;
    }

    public String getDate() {
        return date;
    }

    public void setDate(String date) {
        this.date = date;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public String getLink() {
        return link;
    }

    public void setLink(String link) {
        this.link = link;
    }

    public String getKeyword() {
        return keyword;
    }

    public void setKeyword(String keyword) {
        this.keyword = keyword;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getVersion() {
        return version;
    }

    public void setVersion(String version) {
        this.version = version;
    }
}

A continuación, creamos la clase que contendrá el array de datos y que denominaremos TipsDataModel:

import java.util.ArrayList;

public class TipsDataModel {

    ArrayList< TipsObjectModel > data = new ArrayList < TipsObjectModel > ();

    public TipsDataModel(ArrayList<TipsObjectModel> data) {
        this.data = data;
    }

    public ArrayList<TipsObjectModel> getData() {
        return data;
    }

    public void setData(ArrayList<TipsObjectModel> data) {
        this.data = data;
    }
}

Para deserializar el formato JSON en un objeto Java utilizaremos la biblioteca de código abierto gson. Para ello importamos dicha biblioteca:

implementation 'com.google.code.gson:gson:2.8.6'

La aplicación debe de tener acceso a internet por lo que debemos dar los permisos adecuados:

<uses-permission android:name="android.permission.INTERNET" />

A continuación puedes descargar la base de datos en formato JSON. Para ello voy a utilizar el siguiente código utilizando HttpURLConnection que es es el cliente HTTP estándar para Android, utilizado para enviar y recibir datos a través de la web. Es una implementación concreta de URLConnection para HTTP (RFC 2616).

Declaro el objeto tips:

private TipsDataModel tips;

Llamo a la función loadTips()

private void loadTips() {
    String url = "https://script.google.com/macros/s/.../exec?id=...";
    new DownloadJSONTips().execute(url);
}

private class DownloadJSONTips extends AsyncTask<String, Void, String> {
    Exception error;

    @Override
    protected String doInBackground(String... urls) {
        try {
            return downloadUrl(urls[0],"UTF-8");
        } catch (IOException e) {
            error = e;
            return "ERROR";
        }
    }

    @Override
    protected void onPostExecute(String result) {
        if (result.equals("ERROR")) {
            Toast.makeText(TipsActivity.this,
            "ERROR downloading Tips",Toast.LENGTH_SHORT).show();
        } else {
            Gson gson = new GsonBuilder().create();
            try {
                tips = gson.fromJson(result, TipsDataModel.class);
                showTips();
            } catch (IllegalStateException | JsonSyntaxException exception)
            {
                Toast.makeText(TipsActivity.this,
                "ERROR downloading Tips",Toast.LENGTH_SHORT).show();
            }
        }
    }
}

//Conecta y descarga
public String downloadUrl(String myurl, String charset) throws IOException {
    InputStream is = null;
    try {
        URL url = new URL(myurl);
        HttpURLConnection conn = (HttpURLConnection) url.openConnection();
        conn.setReadTimeout(20000 /* milisegundos */);
        conn.setConnectTimeout(45000 /* milisegundos */);
        conn.setRequestMethod("GET");
        conn.setDoInput(true);
        // Inicia la consulta
        conn.connect();
        int response = conn.getResponseCode();
        if (response != 200) {
            return "ERROR";
        } else {
            is = conn.getInputStream();
            //Para descargar la página web completa
            BufferedReader reader;
            if (charset.equals("ISO-8859-1")) reader = new 
            BufferedReader(new InputStreamReader
            (is, StandardCharsets.ISO_8859_1));
            else reader = new BufferedReader
            (new InputStreamReader(is, StandardCharsets.UTF_8));
            StringBuilder webPage = new StringBuilder();
            String data;
            while ((data = reader.readLine()) != null) {
                webPage.append(data).append("\n");
            }
            return webPage.toString();
        }        
    } finally {
        if (is != null) {
            is.close();
        }
    }
}

Por último, en showTips() mostramos el resultado de la forma que deseemos como ListViews o RecyclerViews.

private void showTips() {
    Log.d("TIPS_LOG", String.valueOf(tips.getData().size()));
    .....
}