0

Good Morning Stackoverflow Community,

I have been working on a Android Application and currently running into a heap load of problems.

Scenario: I need to push and pull data to a Google Spreadsheet, but here is the issue, this application will only be deployed to about 10-15 10in tablets. Now the application is a estimating app for technicians, however when the techs are out in the field they will not have a data connection because the tablets are WiFi only. The only time the app will be able to connect, is when they meetup in the morning at the office and sync the application, to ensure that the data can sync to Google Sheets via Google Drive and Android.

So what I was thinking about doing is taking the initial spreadsheet, convert it to a sqlite db and have the application populate the data fields locally from the device, then when the estimate is completed, somehow turn that data retrieved in the app back to a Google Spreadsheet and saved to the device. (Is this possible, and if so where can I find information on converting data from a sqlite db into a Google Spreadsheet).

Then when the tech gets back to the office where there is a data connection, sync the newly created sheet to Google drive, so the administrator can manage what he needs to manage.

I have searched and searched but can not wrap my mind around this, so any help would be beneficial.

Another issue is that, since there is no data connection to the device when they are performing the estimate, the Google Plus login, that we implemented will not work when out in the field, so would I also need to make a local login procedure that the sqlite db can handle?

Thanks In Advance.

Will

Psy Chotic
  • 219
  • 1
  • 3
  • 11

2 Answers2

0

Can you install Google Sheets app on the device? If so, you might not have to make things so complicated. Here, you can see that Google Sheets are also available offline if you have the app installed. So all they would need to do is, work on the spreadsheet when they are out on the field, come back to office and sync it the next morning. Wouldn't that possibly solve all your problems? Please let me know if I missed something but from what I understood, this should be enough.

pointNclick
  • 1,584
  • 1
  • 12
  • 17
  • Hey thanks, pointNClick, yes, realistically that would solve all the problems and I even thought of that, but the client has us making an Android App that populates and saves the data from the spreadsheet with a database in between (Sqlite). The manager doesn't want his techs to screw up the spreadshseet and would rather have the app populate the needed fields for calculations. I know this is tedious because I have to download the spreadsheet, convert to a sqlite db, save the data then try to regenerate the google spreadsheet from the saved data fields and sync when in a data area. – Psy Chotic Apr 02 '15 at 18:23
  • So far as my knowledge goes, there isn't anything that would do all that for you. So you'd have to come up with your own solution. You could use the [Sheets API](https://developers.google.com/google-apps/spreadsheets/?csw=1#sending_a_structured_query_for_rows) to get all that data for you but going back and forth with sqlite will be messy, in my opinion. However, what about syncing the spreadsheet to a master sheet where the techs can only upload the new data and not mess with the old(Master) one? Any thoughts about that? – pointNclick Apr 02 '15 at 20:07
  • In no way am I suggesting it's not doable, just that it's something that would require you to write the major part of that implementation of transferring the data back and forth. Using the Master and slave sheets would only require you to write a script that does all that, no server implementations of your own to handle the data, login etc and data updated securely with live visibility. That ways, even if you gave them live devices, it wouldn't matter. Although I understand you might have concerns that you can't share but I am trying to understand if this might be a possibility to consider? – pointNclick Apr 02 '15 at 20:18
  • It can be easily done by using [Google Apps Script](https://developers.google.com/apps-script/) as mentioned [here](http://stackoverflow.com/questions/20444102/google-apps-script-is-there-a-way-to-keep-two-sheets-synchronized/20459220#20459220). – pointNclick Apr 02 '15 at 20:43
  • I think we bit off more than we can chew, trying some different approaches – Psy Chotic Apr 02 '15 at 21:01
0

I honestly can't tell you how to upload the changes to that file directly, but I can tell you how to download the contents as a csv using the Google Drive API for Android, which I think you could then edit however you please (or convert that to an SQL table).

Note: If your file isn't a Google Spreadsheets file, this is a lot easier. The demos from Google's Github link below could be used the way they are to do your task. If you want to use Google Spreadsheet files, then my (terrible) process may be of use.

WARNING: THIS IS A REALLY DUMB WAY TO DO THIS, BUT THE ONLY WAY I KNOW TO DO SO USING THE GOOGLE DRIVE API.

I'll assume you've seen the stuff at https://github.com/googledrive/android-demos/tree/master/src/com/google/android/gms/drive/sample/demo , the Google Drive API examples from Google. I'll also assume you figured out how to find the file you're looking for, but you can't access the contents of the file.

After you've been able to find the DriveFile, you should get the metadata of that file. Getting the metadata allows us to get the alternative link for that file. The alternative link looks like this: https://docs.google.com/spreadsheets/d/[unique_file_id]/edit?usp=drivesdk

We can do some string editing and remove everything after the last slash, and then add export?format=csv.

Your link now looks like this: https://docs.google.com/spreadsheets/d/[unique_file_id]/export?format=csv

You can call an AsyncTask to download that url, and get an InputStream that contains the contents of the file.

If you don't need to worry about having everyone upload to the same file (which would make sense since there's the possibility of overlapping data) then your second problem becomes a lot easier. Just look at this file to see an example how to create a file on the root folder: https://github.com/googledrive/android-demos/blob/master/src/com/google/android/gms/drive/sample/demo/CreateFileActivity.java

avbor
  • 43
  • 7