Skip to main content

Using onEdit trigger on Google Spreadsheets to send Firebase Cloud Messaging notification to Android apps

Normally apps use database and a backend to operate. Here I'd like to share another way of using Google Sheets as a database to replace SQL, Mongo and the likes. It is not very customizable or extendable but it is a very good way to prototype or for small scale services or internal tools. Whenever a change happens on the sheets, the app gets a notification so it can re-poll the sheets to get new data.

Using Google Sheets as a Database

Overview

What is needed for this to work.
  1. Google account, Google Sheets file.
  2. Created Firebase project.
  3. Created android app and registered to use FCM.
  4. The app has access rights to the Google Sheets to read its contents. Reading a Google Sheets file has been covered many times before so I will just refer you to one of them here.

Flow of the application

Someone in charge of the data will make changes to the Google Sheets file and finally when everything is done, he/she will modify a single cell that acts as the "Apply button". For this example let's assume the cell F2 highlighted in the image is the Apply Button.

After you make changes, maybe add Meg Ryan to the list of cast, you just need to change the value of F2 into something else. It doesn't matter what as long as it changes.

The GAS (Google Apps Script) script to detect changes in a Sheets file is as follows. When a change happens on cell F2, sendFcm is fired.

function edited(e){
   var range = e.range;
  var cellAddress = e.range.getA1Notation();
  if (cellAddress === 'F2') {
    range.setNote('Last modified: ' + new Date());
    sendFcm();
  };
}

You need to go and register this script to the OnEdit trigger in your project dashboard. For more details on how to create Triggers on Google Sheets, check the documentation here.

sendFcm is a custom function that sends a payload to FCM. The GAS script to send payload to FCM is as follows.


function sendFcm(){
  text = {
   "to":"/topics/all",
   "data":
   {
      "title":"Sheets Settings Modified",
      "message":"recheck"
   }
}
    var response = UrlFetchApp.fetch('https://fcm.googleapis.com/fcm/send', {
    method: 'POST',
    contentType: 'application/json',
    headers: {
      Authorization: 'key=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
    },
    payload: JSON.stringify(text),
      muteHttpExceptions: false
  });
  
  console.log(response);
}

A few points worth mentioning.

  1. /topics/all refers to the message group that the app needs to subscribe to.
  2. We need to subscribe the app to the appropriate topic. In kotlin this can be done with : subscribeToTopic("topicName")
  3. We are posting to the /send API provided by FCM
  4. Authorization key is the key provided when registering FCM to your project. For more information on authorizing the request please check here.
The app will receive a notification from FCM and you can then get the app to access sheets again to retrieve new data.

Drawbacks

There are several drawbacks to this method
  1. If you have several apps that need to reference the sheets file, they will all get the notification at around the same time. This may cause the Google Sheets file to receive many requests at the same time.
  2. The input validation has to be done completely on the app. It is very hard to predict invalid input when there is no limitation whatsoever on the database side. You may expect string or a number for a certain field but can never know what may come.
  3. Authorization may be a problem because each app is authorized to access directly to the sheets database. For more sensitive information, a good backend to filter the requests may be a better idea.

Conclusion

Google Sheets can be a quick and dirty way to setup a database without backend and it works pretty well too. But make sure to build a proper backend and database as soon as possible!

Update

Since v1 of the HTTP API, we need to get OAuth2 tokens to access FCM. I will update the contents in a short while but please take note that this method will only work with the legacy API.

Comments

Post a Comment

Popular posts from this blog

Using FCM with the new HTTP v1 API and NodeJS

When trying to send FCM notifications I found out that Google has changed their API specifications. The legacy API still works but if you want to use the latest v1 API you need to make several changes. The list of changes is listed on their site so I won't be repeating them again but I'll just mention some of the things that caused some trial and error on my project. The official guide from Google is here : Official Migration Guide to v1 . The request must have a Body with the JSON containing the message data. Most importantly it needs to have "message" field which must contain the target of the notification. Usually this is a Topic, or Device IDs. Since my previous project was using GAS, my request had a field called "payload" instead of "body". Using the request from my previous project, my request in Node JS was as follows: request ({ url: 'https://fcm.googleapis.com/v1/projects/safe-door-278108/messages:send' , method: ...
Post a Comment
Read more

Building a native plugin for Intel Realsense D415 for Unity

Based on a previous post , I decided to write a plugin for the Intel Realsense SDK methods so we can use these methods from within Unity. FYI Intel also has their own Unity wrapper in their Github repository , but for our projects, I needed to perform image processing with OpenCV and passing the results to Unity instead of just the raw image/depth data. There is a plugin called OpenCVForUnity to use OpenCV functions from Unity but previous experiments indicate the image processing inside Unity can take a long time. I hope this post can help someone else who wants to use Intel's cameras or any other devices natively in Unity. Test Environment Windows 10 64bit Unity 2017.2.0f3 x64 bit Realsense SDK from Intel CMake 3.0 or higher Steps Checkout the native plugin code here . Don't worry about the other projects in the same repository. The relevant code is in the link above. Checkout the Unity sample project here . However, instead of master you need to go to the br...
5 comments
Read more

Microsoft Azure Face API and Unity

During one of my projects, I came across Microsoft's face recognition API (Azure Face API) and it looked good enough to recognize people's faces and detect if a person is a newcomer or a repeating customer to our store. As our installations mainly use the game engine Unity, I wanted to be able to use the Face API from Unity. Face API does not have an SDK for Unity but their requests are just HTTP requests so the Networking classes in Unity can be wrapped into methods to make it easy to call these APIs. First of all, to those who just want to see the code, here it is . My tests focus on the identification of a face in an input image. The full tutorial I followed can be found here . The Main scene goes through the steps in the tutorial such as creating a PersonGroup and adding Persons to the group if it is not created yet. Just make sure you: Change the API key. I used a free trial key which is no longer valid. Use whatever images you want. I don't mind you us...
Post a Comment
Read more