Porto

Blog

Google Spreadsheets as a data source for an Android App using Retrofit

When I develop some prototypes or test apps I need to obtain some shared data from an internet stored location. However I don't want to build a full web app for this. In this article we will create a Google Spreadsheet and we will use Retrofit to get it as models for our application as an standard data-source. We will use the csv capability of Google spreadsheets to extract its data and we will load it to the app. Another solution would be using the Google Query Language as pointed in this blog, but we have used CSV for its simplicity.

You can download all the sources from GitHub Project.

The Google Spreadsheet

We have created the following spreadsheet It contains a list of cooking recipes. The first line contains the name of the attributes, and the following lines the attribute values. We have also added some dummy attributes to the recipe to test all possible types.

We have also made the spreadsheet public with a link.

The Retrofit Converter

First of all we need to create a Retrofit converter so that it can create models from a CSV. It will load the name of the first column of the csv and map it to model attributes. Latter it will create new instances and fill the attributes.

The implementation has some limitations

  1. It only works to produce List results.
  2. It can only be used for server responses
  3. The current implementation only works with attributes of type String, int/Integer, double/Double, long/Long and boolean/Boolean. New improvements may enhance this to enable dates and other types.
  4. We have used some reflection to fill the model attributes. This may be improved with annotation processing, but for now it runs fast enough.

Let's see the Java code.

public class CsvConverter<T> implements Converter<ResponseBody, List<T>> {
    Type type;
    Class<T> itemClass;

    public CsvConverter(Type type) {
        this.type = type;
        itemClass = (Class<T>) ((ParameterizedType)type).getActualTypeArguments()[0];
    }

    @Override
    public List<T> convert(ResponseBody body) throws IOException {
        CSVReader csvreader = new CSVReader(body.charStream());
        String [] nextLine;

        List<T> result = new ArrayList<>();
        String[] titles = csvreader.readNext();
        List<Field> fields = processFirstLine(titles);
        while ((nextLine = csvreader.readNext()) != null) {
            T model = createModel(nextLine, fields);
            result.add(model);
        }
        return result;
    }

    /**
     * Creates a model from the line and the fields
     */
    private T createModel(String[] nextLine, List<Field> fields) throws IOException {
        try {
            T model = itemClass.newInstance();
            for(int i = 0; i<nextLine.length; ++i){
                Field attribute = fields.get(i);
                setValue(model, attribute, nextLine[i]);

            }
            return model;
        } catch (InstantiationException e) {
            throw new IOException(e);
        } catch (IllegalAccessException e) {
            throw new IOException(e);
        }
    }

    /**
     * Sets the value to the field of the model. It will do necessary conversions from String.
     * @param model
     * @param field
     * @param value
     * @throws IllegalAccessException
     */
    private void setValue(T model, Field field, String value) throws IllegalAccessException {
        field.setAccessible(true);
        Class<?> attributeClass = field.getType();
        field.set(model, getValue(value, attributeClass));
    }

    /**
     * Converts a String to a desired class, using default conversions.
     * @param value
     * @param desiredClass
     * @param <K>
     * @return
     */
    private <K> K getValue(String value, Class<K> desiredClass){
        if(desiredClass.isAssignableFrom(String.class)){
            return (K) value;
        } else if(isInt(desiredClass)){
            return (K) Integer.valueOf(value);
        } else  if(isBoolean(desiredClass)) {
            return (K) Boolean.valueOf(value);
        } else  if(isDouble(desiredClass)) {
            return (K) Double.valueOf(value);
        } else  if(isLong(desiredClass)) {
            return (K) Long.valueOf(value);
        } else if(desiredClass.isAssignableFrom(Calendar.class)){
            throw new UnsupportedOperationException();//Todo implement
        } else if(desiredClass.isAssignableFrom(Date.class)){
            throw new UnsupportedOperationException();//Todo implement
        } else {
            throw new UnsupportedOperationException();//Todo implement
        }
    }

    /**
     * Reads the first line of the csv and gets the list of fields
     */
    private List<Field> processFirstLine(String[] titles) throws IOException {
        List<Field> fields = new ArrayList<>();
        for(int i = 0; i< titles.length; ++i){
            try {
                fields.add(itemClass.getDeclaredField(titles[i]));
            } catch (NoSuchFieldException e) {
                throw new IOException(e);
            }
        }
        return fields;
    }

    public static boolean isInt(Class<?> fieldClass){
        return Integer.class.isAssignableFrom(fieldClass) || int.class.isAssignableFrom(fieldClass);
    }

    public static boolean isDouble(Class<?> fieldClass){
        return Double.class.isAssignableFrom(fieldClass) || double.class.isAssignableFrom(fieldClass);
    }

    public static boolean isBoolean(Class<?> fieldClass){
        return Boolean.class.isAssignableFrom(fieldClass) || boolean.class.isAssignableFrom(fieldClass);
    }

    public static boolean isLong(Class<?> fieldClass){
        return Long.class.isAssignableFrom(fieldClass) || long.class.isAssignableFrom(fieldClass);
    }

}

Moreover we have created a simple Converter.Factory to use the previous converter

public class CsvConverterFactory extends Converter.Factory{
    @Override
    public Converter<ResponseBody, ?> fromResponseBody(Type type, Annotation[] annotations) {
        return new CsvConverter(type);
    }
}

Sample test application

We have the common implementation. Let's create an example. We have stored this under the test folder.

We create a Recipe model with our attributes.

public class Recipe {
    String id;
    String name;
    Integer intTest;
    Double doubleTest;
    Long longTest;
    Boolean booleanTest;

    public Recipe() {
    }
    //we ommit the getters and setters
}

Retrofit RecipeDataSource

We define a retrofit data-source:

public interface RecipeDataSource {
    // csv https://docs.google.com/a/google.com/spreadsheets/d/1TLV0JH_DJVb5KRQf56I_Cv6oZY-jHl6bYAIQ9QuBisk/export?gid=0&format=csv
    @GET("1TLV0JH_DJVb5KRQf56I_Cv6oZY-jHl6bYAIQ9QuBisk/export?gid=0&format=csv")
    public Call<List<Recipe>> getRecipes();
}

Lets test everything

We only want to check if the idea is working so we will not create any Activity. We will just use a simple test to see if the communication works. Let's see the test

public class SpreadSheetDataSourceTest extends ApplicationTestCase<Application> {
    public SpreadSheetDataSourceTest() {
        super(Application.class);
    }

    public void testSpreadSheatData() throws Exception {
        Retrofit retrofit = new Retrofit.Builder()
                .baseUrl("https://docs.google.com/a/google.com/spreadsheets/d/")
                .addConverterFactory(new CsvConverterFactory())
                .build();

        RecipeDataSource service = retrofit.create(RecipeDataSource.class);
        List<Recipe> models = service.getRecipes().clone().execute().body();
        assertEquals(models.size(), 3);
        Recipe first = models.get(0);
        assertEquals(first.getId(), "1");
        assertEquals(first.getName(), "Rabbit with chocolate");
        assertEquals(first.getIntTest()+0, 1);
        assertEquals(first.getDoubleTest()+0.0, 1.1);
        assertEquals(first.getLongTest()+0, 1l);
        assertEquals(first.getBooleanTest(), Boolean.TRUE);

        Recipe third = models.get(2);
        assertEquals(third.getBooleanTest(), Boolean.FALSE);
    }
}

If we run it... Hurray! passed!

Overview

We have done a simple demonstration on how to use a Google Spreadsheet as an external data-source. The converter needs to be polished buts its functional right now.

Our next steps will be:

  1. Enhance the Converter to admit more types
  2. Merge this with Android Pillow so that the excel values are stored (and synchronized) in the database.

That's all for today!