Wednesday, December 15, 2010

Google SpreadSheet Library for Android

You might have already tried using Google's GData Lib to access SpreadSheet from Android, and after few hours of try, you start Google for any alternate solution. I have also spent number of hours without any solution.

So, I have developed SpreadSheet client Lib [ works on Android :-) ] and ideally work on any Java platform-
http://code.google.com/p/google-spreadsheet-lib-android/

Latest version: 2.1 (Added support for List Feed. Please visit above link to get more info.)

Supported Features:

1. Create/Delete Spreadsheet

2. List all stored Spreadsheets

3. Add/Delete WorkSheet into/from a given SpreadSheet

4. List all Worksheets of a given Spreadsheet

5. Add Records into WorkSheet/SpreadSheet (It supports Table based record handling)

6. Retrieve Record from WorkSheet/SpreadSheet (Structured Query support)

7. Retrieve Record as List Feed from Worksheet

8. Update/Delete Records

9. Share ShreadSheet with user/group/domain.

10. Conditional data retrieval- Structured Query support

11. Conditional/Structured query support for List based data retrieval

12. Better Log handling. By default logging will be enabled. To turn off logging Log.disableLog()  

Would appreciate your feedback and love to hear any specific feature requirement.... happy to help :-)

117 comments:

  1. I think I am the first to download and use your Android Spreadsheet API. It does work for me and I congratulate you on the code you produced. I couldn't find any other code that worked on the Android.

    Are you going to finish it with methods for conditional queries and updates?

    ReplyDelete
    Replies
    1. hello i am anil carie

      can you mail me a source code updating, deleting , inserting data into google docs

      it would be great help for me carieanil@gmail.com is my mail id

      Delete
  2. Hi,

    thanks for your comment and trying this Lib.
    Yes, I am working on SpreadSheet "Share" and also on "Conditional Query". Probably I will publish the "Share" feature and then "Conditional Query".

    Hope it helps!

    Cheers,
    Prasanta

    ReplyDelete
  3. Hi,

    I am using your library and its great. Thanks for the effort.

    However I am facing one issue. When I query the spreadsheet, I am getting the right number of records back but the records are empty. It does not have any data.

    FYI, I am working on Android.

    Thanks

    ReplyDelete
  4. Hi,

    thanks for your comment. If you face any issue, pls raise them with details (log) on-
    http://code.google.com/p/google-spreadsheet-lib-android/

    I'll try to follow up.

    Cheers,
    Prasanta

    ReplyDelete
  5. Hi. Do you have a working sample for Android, just so I can see how the callbacks etc should be set up? Thanks

    ReplyDelete
  6. Hi, I am working on an Android App which is using this Lib. But it is still in the mid of development. So, I'll try to post few Android sample codes.
    It was on my TODO, but somehow unable to find out time to put them together :-)
    thanks for reminding...

    ReplyDelete
  7. I am trying to write a simple android app that will allow me to read and write to the cells googlespreadsheet. However I am amazed at how there are no good examples of how to do this. Basic authentication, and read/write cell data are non existent. If you could post some simple example that would be great. Thanks.

    ReplyDelete
  8. Hi Glen,

    thanks for your comments. Uploaded new release 2.0.u1 which includes "Android Sample" program.

    Hope it helps!
    Thanks,
    Prasanta

    ReplyDelete
  9. First thanks for your effort on this library.
    After read the sample code and java docs in the zip file, I still have some questions when I try to use this library on my project.

    First, I need to retrieve the only 3 records at the final part of a spreadsheet. How could I do this with this library?

    And another one problem is , how to read a published spreadsheet? (This spreadsheet is published through Google Docs web interface, and can be viewed by anyone who has the link without login required.)

    Thanks.

    ReplyDelete
  10. Hi,

    here is my answer-
    Q1. I need to retrieve the only 3 records at the final part of a spreadsheet. How could I do this with this library?

    this library supports conditional query for both "table" and "list" based feed. So you can retrieve only selected rows with matching consitions- it supports- Structured Query

    Please my sample code ConditionalDataRetrieve.java

    Q2. how to read a published spreadsheet?

    this library will list all Spreadsheets whether created by Google interface or this library itself. For spreadsheets created through Google Web interface, you need to use "List based" feed, which is also supported by this library.

    Pls check sample codes carefully to understand the capabilities of this library and drop me mail if you still have any doubt!
    Thanks,
    Prasanta

    ReplyDelete
  11. Thanks for your reply, Paul.

    Abuout the Q1:
    Yes, I've read the ConditionalDataRetrieve.java. But I just don't know how to set the max-results constraints in the library.

    I can do this with the following code when I use Google official GData API jav client library:
    //------------------------------------------
    // with GData API Java Client Library
    URL listFeedUrl = wsEntry.getListFeedUrl();
    ListQuery maxRowQuery = new ListQuery(listFeedUrl);
    maxRowQuery.setOrderBy("serialCol");
    maxRowQuery.setReverse(true);
    maxRowQuery.setMaxResults(3);
    //------------------------------------------

    In the gss_lib I use the following code , but don't know how to set the max-results :
    //------------------------------------------
    // with gss_lib
    ArrayList records = wk.getRecords(s.getKey(), true, "" , "column:serialCol");
    // How to set max-results parameter ?
    //------------------------------------------


    About the Q2:
    I just know the published link of the spreadsheet (something like https://spreadsheets.google.com/pub?key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx) , and then my program need to fetch data in the spreadsheet. So I don't have any google account name and password to do the SpreadSheetFactory.getInstance(accountName,pwd). Then , in this situation how could I get the SpreadSheet object which form the published spreadsheet with the gss_lib?


    It's all my confusion.
    Thanks in advance.

    ReplyDelete
  12. Hi,
    Thanks for your detail analysis.

    Q1. I agree, at present there is no way you can do "setMaxResult". I'll add this in my TODO :-)

    Q2: Well, without authorization, you can't access Google SpreadSheet API. You, need to pass auth_token with every request. So, this is not possible.

    Thanks,
    Prasanta

    ReplyDelete
  13. continuing my comment on Q2, SpreadSheetFactory.getAllSpreadSheets() will list all SpreadSheets which are either shared by other user or created by you.

    ReplyDelete
  14. Excuse me, about the Q2 , the google official Spreadsheet API developer's Guide said "Note: You don't need to authenticate to request data from a published spreadsheet. (A published spreadsheet is one that the user has marked as published using the Spreadsheets GUI.)" , the link is here : http://code.google.com/intl/zh-TW/apis/spreadsheets/data/3.0/developers_guide.html#Authenticating .

    Do I make any misunderstanding?

    ReplyDelete
  15. About the published soreadsheet , I've use HttpClient to access the data of a published spreadsheet on android smoothly instead of finding any available spreadsheet API.
    It now works fine, thank you.

    ReplyDelete
    Replies
    1. Hello Dolph,

      I am also having same kind of requirement as you already done. Can you please kindly guide me on this? How can i get published spreadsheet in my android app? And from that I want to fetch some data, so what steps i have to follow? Please kindly help me because I don't know where and how to start with.

      Thank you

      Delete
  16. Hi Dolph,
    thanks for sharing your exp...

    ReplyDelete
  17. Hi!

    I'm using your library and it's great!!

    It's working really well for me... but I need to modify records of a worksheet which was created using web UI. According to your library doc:

    It also supports List based Feed, but only to retrieve data. At present it doesn't provide any method to Add/Edit/Delete data in List based Feed.

    Are you planning to add these methods? And if not... could you give me some advice about where can I find info about it?

    I have been searching and I haven't found anything better than your library!

    Thanks in advance and thank you very much for sharing your knowledge and your work!

    ReplyDelete
  18. Hi Javi,

    thanks for your comments. At present I'm working on some other project, so Add/Edit/Delete for List feed will take sometime.
    To add this feature, just like Table feed, the code cache column names and then you need to send POST request to Add row- Add Row

    PUT for update and DELETE to delete a row. You can check my implementation of Add/Edit/Delete of Table feed.
    You need to add this feature in WorkSheet.java

    Please let me know if you need any additional help.
    Thanks,
    Prasanta

    ReplyDelete
  19. Hello from Japan.
    your lib worked like a magic in 3min.
    i will try to build a great app over it and donate you a big money then!

    ReplyDelete
    Replies
    1. hello can tell me how to use this lib

      i have imported this file on to my eclipse

      when i run the emulator i am getting message gss_client is getting closed

      Delete
    2. Update properties -> Add lib in Order and Export!

      Delete
  20. Hi

    Your library was great, thanks.

    But, i want to know how to send/add an image to spreadsheet google using this library?

    ReplyDelete
  21. Prasanta,

    I'm so glad I found your library after banging my head against the wall with both the gdata-java-client library and the new gdata-api-java-client library (supposedly for Android but not working for spreadsheets).

    Browsing the code, I believe you are doing everything securely through SSL (https where needed)? I just wanted to confirm that that is the case since I don't want to go broadcasting passwords or the cell data in the clear.

    Thanks for writing and posting this library.

    ReplyDelete
  22. Hi,

    you guessed it right. To get access, the Lib need to get Auth Token after doing Authenticatiion with Google Server. So, that is safe.

    Thanks for your comment BTW.
    Cheers,
    Prasanta

    ReplyDelete
  23. Hi Paul,

    I would like to thank you for the wonderful library you've just created. Please enlighten me on this-- I tried executing the GSS_Lib_Android_Sample in eclipse following the recommended 2.1 and API 7, but I got an error of "The application GSS_Lib_Android_Sample has stopped unexpectedly, please try again", I dont really know what cause this error. Help me please, I am new to Android.

    ReplyDelete
  24. Hi,
    this is interesting :-)
    Can you pls mail me the exact exception you see on DDMS? Are you trying on Emulator or on Device ?

    Thanks,
    Prasanta

    ReplyDelete
  25. Hi Paul,

    Thanks for the quick response. Yes I am using an Emulator for test. And for DDMS logs i will email the snapshots. Hope this helps.
    Again thank you and more power!

    ReplyDelete
  26. Hi Prasanta,

    with Table and Record feeds getting deprecated, are you planning to migrate the library to List/Cell?

    Many thanks,

    Fredo

    ReplyDelete
  27. Hi Fredo,

    thanks for the info. Right now I am into some other project, so don't think I'll get time to do this immediately.
    But the present API allows retrieval List Feed data with all query feature. So reading is available.

    Thanks,
    Prasanta

    ReplyDelete
  28. Dear All,

    today I uploaded a new binary and source (Rel 2.1) which supports List based feed. Please check the ListFeed.java sample code to understand how to use it.
    You can Add/Edit/Delete List based WorkSheet data Feed.
    http://code.google.com/p/google-spreadsheet-lib-android/
    Would appreciate your feedback.
    Cheers,
    Prasanta

    ReplyDelete
  29. Very interested in your lib. However there is nothing posted on googlecode as of now. Where is your lib available?

    ReplyDelete
  30. Pls find the latest rel- 2.1
    http://code.google.com/p/google-spreadsheet-lib-android/downloads/list

    ReplyDelete
  31. I'm trying your lib but it doesn't work for me...
    Maybe I done something wrong...
    My code...

    SpreadSheetFactory s=SpreadSheetFactory.getInstance(new AndroidAuthenticator(Main.this));
    Log.d("[Jin]",s.toString());
    List l=s.getAllSpreadSheets();
    if(l==null)
    Log.d("[Jin]", "No spreadsheet");
    else
    Log.d("[Jin]", l.size()+" ");

    Authentication it's ok but it stop at s.getAllSpreadSheets()
    I'm tryin on android and i added these permissions

    android.permission.GET_ACCOUNTS
    android:name="android.permission.USE_CREDENTIALS
    android:name="android.permission.INTERNET

    Pls Help me...

    ReplyDelete
  32. I'm having the somewhat same problem as described above. It does execute the s.getAllSpreadSheets() method but it takes a very long time on the http.doConnect (almost 3 minutes).

    ReplyDelete
  33. Hello Prasanta,

    Thanks for your code in GSS_Lib it works great!

    but i don't find a method for deleting rows in a worksheet. is there anyway to do that?

    Thanks!

    Dragon

    ReplyDelete
  34. Hi Dragon,

    You can delete a Row using following call (ver 2.2 of the Lib)-
    workSheet.deleteListRow([SpreadSheet_Key], [List_Row_Instance]);

    Please check the sample code- ListFeed.java. You should start using 2.2 Version of the API and List Feed based approach.

    Thanks,
    Prasanta

    ReplyDelete
  35. Hi Prasanta, thanks for creating this amazing API for us to use. However I tried to refer to the ListFeed.java and try to used the code in android, it doesn't work. Can you help me on this? really appreciate it because I need to use this for my project which is due soon. Thanks in advanced.





    public class ReadGoogleSpreadSheet extends Activity {

    @Override
    public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.readxml);
    TextView read = (TextView) findViewById(R.id.textView1);

    try{
    SpreadSheetFactory spf = SpreadSheetFactory.getInstance ("pink@gmail.com", "pass");

    ArrayList spreadSheets = spf.getSpreadSheet("hello", true);
    if(spreadSheets != null)
    readxml.setText("Number of SpreadSheets:" + spreadSheets.size() );
    else
    readxml.setText("cantread");

    }catch (Exception e){
    read.setText(e.getMessage());
    }
    }
    }

    ReplyDelete
  36. Hi,
    drop me an email with details error message.

    I'll look into that.
    -Prasanta

    ReplyDelete
  37. Hi !
    Thanks for your great work !
    I try to use the last version (2.2) of the library on android. But after solve a crash due to missing permission (INTERNET, GET_ACCOUNTS and USE_CREDENTIALS) It's seems that the request to google spreadsheet service, takes a lot of time. As result, i get a socketException "request time fail: adress family not supported by protocol". I got my response after more than 3 minutes ... Can you help me to fix it please ? Could you profide us an "Hello Android gss-lib project" ?

    ReplyDelete
  38. hello, i want to demo code for retrieve some fields from the google spreadsheet to android ? plz help me. how to do it? as well as how to add GSS lib in eclipse? plz reply me as soon as possible.

    and mail me on shahurvish91@gmail.com

    ReplyDelete
  39. Hi,

    I have already added sample folder in the 2.2 release. Those are Core Java application and you can easily use them in Android.

    I would appreciate any volunteer who can work on the Android sample. I can share whatever I have.

    Thanks,
    Prasanta

    ReplyDelete
  40. Dear All,

    Uploaded a sample Android client-
    http://code.google.com/p/google-spreadsheet-lib-android/downloads/list

    -Prasanta

    ReplyDelete
  41. Hi prasanta.

    Have u been workin on something new on ur library?what are the next changes u are thinkin implimenting?

    ReplyDelete
  42. Hi prasanta
    I am trying to use your api to retrieve data from spreadsheet in my android app.
    I retrieved the name of the spreadsheet and then of the worksheet also.
    I also retrieved the number of columns and rows in the worksheet correctly.
    but when I try to retrieve the records it always return null. Please tell me what I am doing wrong.

    ReplyDelete
  43. hi Prasanta
    I figured out what i was doing wrong.
    congratulation on creating such great library.

    ReplyDelete
  44. Dear All,

    please help me in promoting MateOnGo.
    This is a location based recommendation engine on which I'm working right now.

    If you like the product, please share your feedback on our facebook page-
    http://www.facebook.com/mateongo

    Thanks,
    Prasanta

    ReplyDelete
  45. Hi Prasanta

    Your library solve my nightmare! do you plan to upgrade it to Spreadsheet API 3.0?

    With Regards
    Yong Lin

    ReplyDelete
  46. Hello Prasnata,

    You library is great... I was able to write a nice wine cellar application for the android that uses spreadsheets for the inventory control and can be viewed by all the members of the family... but as of two days ago it just stopped working, the Google spreadsheet feed says that it can not find the file... even though there have been no changes to the code not the sheet name... are you aware of any changes to the Google doc spreadsheet feed protocol that may have changed? Just a shot in the dark. Thanks Larry

    ReplyDelete
  47. Hi, this is Larry again... I don't know this just must be a timing issue, the same app still works on my Samsung Galaxy using a wifi connection, but getting a response from the Google docs site on a Droid phone just takes way to long... I mean well over minute maybe two, and the sheet only has some twenty rows and eight columns. It is too bad that the Google app api's are so slow, but it is not viable for an application. I have no idea what makes it take so long, because it is fairly snappy when using the Google docs app etc, but getting a feed is intolerable. I'll have to figure out some other persistence strategy, but it is too bad because the collaboration aspect is cool.

    ReplyDelete
  48. First off, I really like the project. I have not been able to find another way to connect to a google doc spreadsheet. I do find that it is extremely slow though. I wrote a basic app that just creates a SpreadSheetFactory and then performs the getSpreadSheet routine looking for a specific spreadsheet with the isTitleExact set to true. Then I display the title of the Spreadsheet in a textView along with the time the Activity started and when it ended. It took over 6 minutes just to get the Spreadsheet title. The spreadsheet only has a few rows so it is not very big. Before I go ahead and start using this library to update the spreadsheet in my app I want to know if these types of response times are typical and if there are any settings to speed this up? I am running this on my Emulator on a PC that has over 20 MB speeds When I try it on my 3G phone I waited for over 30 minutes. When I try the Google Docs app in comes up with the document data in 5 seconds so I don't think it is a slow connection issue. Any help on making this faster or another way to access Google Docs from Android? I have a idea for a new app but it is dependent on being able to update a simple Google Docs spreadsheet.

    ReplyDelete
  49. Dear All,
    there was one Proxy reference in my code and it was causing connection delay.
    Please start using the latest Lib-
    gss-lib-2.2.b1.jar
    http://code.google.com/p/google-spreadsheet-lib-android/downloads/list

    Thanks,
    Prasanta

    ReplyDelete
  50. Jerin Michael JoseNovember 1, 2011 at 7:55 PM

    Hi Prasanta,
    Thank you for the awesome GSS library !!
    I would like to let you know that I have used your library for the DroidconIndia 2011 android app
    (open-source). I have mentioned your name and library link.

    ReplyDelete
  51. @Jerin: glad to know that. Is there anyway I can test the App ? would love to check it!
    Cheers.

    ReplyDelete
  52. I have mailed you at your gmail address

    ReplyDelete
  53. Prasanta,

    Thanks for the quick fix for the Proxy error! Seems to be working now and I am going to modify my app to use your library. Is there a way using your library to tell the last time the SpreadSheet or WorkSheet has been modified? I keep the contents of the Spreadsheet locally on my app to improve performance time and reduce data usage and only want to read the entire contents of the SpreadSheet when it has been modified to update the app data.

    ReplyDelete
  54. it doesn't support any auto caching or update detection functionality. You can add some solumn like date/time and track your data changes.
    Keeping a local cache is good approach. But u also need to take care of periodic background synch.
    -Prasanta

    ReplyDelete
  55. hi Prasanta this library helped me a lot but i am getting one problem i am retrieving data using conditional query for that i am using ContentHandler but i dont know how to write the query for the following requirement

    i,e
    i want to retrieve values based on two columns year==XXXX and country=XXX
    how to write the query here year and country are the column name of spreadsheet

    ReplyDelete
  56. i did the same as above but i am getting the error
    bad request 400

    ReplyDelete
  57. hi prasanta this is laxminarayana i am using using your library working great but i am facing one issue i,e i am having columns like category year ..etc i want to retrieve the records i,e category column only it contains duplicate data
    so in sql i can write it as select distinct category from table where year=2006
    , but how to query this by using your library?
    i dont need entire row only category column only
    waiting for your reply

    ReplyDelete
  58. Thank you soooo much.. your library is fantastic.. it helped me a lot

    ReplyDelete
  59. Hi, Prasanta
    Thanks for brilliant tiny library! )

    Don't you try using http://code.google.com/p/google-api-java-client for that?

    Seems it has full support out of the box but a lot of overhead (http://stackoverflow.com/questions/8633534/google-spreadsheet-api-from-android-google-api-java-client-or-handmade)

    ReplyDelete
  60. Hi, Prasantha.

    Keep it up the great work......

    Amila

    ReplyDelete
  61. Hi Prasantha.

    I am getting a error when trying to insert a record into a spreadsheet on android.
    this is the error in Logcat.

    01-03 12:06:49.868: I/System.out(1059): [HttpConHandler] URL=https://spreadsheets.google.com/feeds/list/taPqz7PMkAv4aiyb5Z1rnUw/od6/private/full
    01-03 12:06:51.757: I/System.out(1059): [HttpConHandler] Error in connection: java.io.FileNotFoundException: https://spreadsheets.google.com/feeds/list/taPqz7PMkAv4aiyb5Z1rnUw/od6/private/full
    01-03 12:06:51.779: I/System.out(1059): [HTTP_Response] HTTP Response Code: 400
    01-03 12:06:51.779: I/System.out(1059): [HTTP_Response] HTTP Response: Bad Request
    01-03 12:06:51.798: I/System.out(1059): [HTTP_Response] Error Msg from Server: We're sorry, a server error occurred. Please wait a bit and try reloading your spreadsheet.
    01-03 12:06:51.798: I/System.out(1059): [HTTP_Response] Error in Connection: https://spreadsheets.google.com/feeds/list/taPqz7PMkAv4aiyb5Z1rnUw/od6/private/full
    01-03 12:06:51.798: W/System.err(1059): java.io.FileNotFoundException: https://spreadsheets.google.com/feeds/list/taPqz7PMkAv4aiyb5Z1rnUw/od6/private/full
    01-03 12:06:51.808: W/System.err(1059): at libcore.net.http.HttpURLConnectionImpl.getInputStream(HttpURLConnectionImpl.java:177)
    01-03 12:06:51.808: W/System.err(1059): at libcore.net.http.HttpsURLConnectionImpl.getInputStream(HttpsURLConnectionImpl.java:270)
    01-03 12:06:51.818: W/System.err(1059): at com.pras.conn.HttpConHandler.doConnect(HttpConHandler.java:131)
    01-03 12:06:51.818: W/System.err(1059): at com.pras.WorkSheet.addListRow(WorkSheet.java:342)
    01-03 12:06:51.818: W/System.err(1059): at com.oknet.test.SpredSheetSampleActivity$MyTask.doInBackground(SpredSheetSampleActivity.java:134)
    01-03 12:06:51.828: W/System.err(1059): at android.os.AsyncTask$2.call(AsyncTask.java:264)
    01-03 12:06:51.828: W/System.err(1059): at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:305)
    01-03 12:06:51.838: W/System.err(1059): at java.util.concurrent.FutureTask.run(FutureTask.java:137)
    01-03 12:06:51.838: W/System.err(1059): at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1076)
    01-03 12:06:51.867: W/System.err(1059): at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:569)
    01-03 12:06:51.867: W/System.err(1059): at java.lang.Thread.run(Thread.java:856)

    ReplyDelete
  62. Hi All
    I could overcome this issue.

    ReplyDelete
    Replies
    1. how would you solve that problem can please explain me.

      Delete
  63. Hello! Thanks a lot for the great library! It has been working just find for my Android application. However, after the Dec 31 of the last year, I've started to get error messages, when I try to authenticate with the provided AndroidAuthenticator. Here is the logcat:


    01-11 16:35:06.613: I/System.out(14454): [HttpConHandler] Error in connection: java.io.FileNotFoundException: https://spreadsheets.google.com/feeds/spreadsheets/private/full?title=test&title-exact=false
    01-11 16:35:06.623: I/System.out(14454): [HTTP_Response] HTTP Response Code: 401
    01-11 16:35:06.623: I/System.out(14454): [HTTP_Response] HTTP Response: Token expired

    Why could it be that the token that I get from the Android accounts has expired? Can you, please, assist - is it possible to fix the issue and still be able to login using build-in Android account? Thanks!

    ReplyDelete
    Replies
    1. @Yuriy: I just tested the latest sample client- (gss-lib-2.2b1), it is able to get Autho Token and fetch spreadsheet list from my account. Can u pls try with the latest lib and some other Gmail account ?
      -Prasanta

      Delete
    2. Yes, I am using the latest lib. I've tried on another phone with different account - and it worked. So, seems like there is some problem with my phone and account. I wonder, if the Google gave me the token and now it has expired. I will need to dig deeper into the authenticating code to overcome this problem. If you have any suggestions or guidelines I would greatly appreciate this!

      Delete
    3. @Yutiy, what did you find out? It seems like I am having a similar issue. Thanks

      Delete
    4. I've found the root of the problem and also found a solution. The problem was with my auth token. It was expired and thus the connection to the spreadsheet service failed. I found in the documentation to the AccountManager (http://developer.android.com/reference/android/accounts/AccountManager.html) that if request fails then token should be invalidated and request tried again. So, basically what I do now: I get the auth token as usual, then I submit request to Google Spreadsheets (I just get all sheets). If request fails, then I invalidate the token and get it again from account manager. I've modified the AndroidAuthenticator class by adding this new logic. You can get the java file here: http://db.tt/Ig95jr7t
      @Prasanta, I did a quick fix for the problem to make it work. Maybe you can make more elegant solution and add it to the next release? In a nutshell, there should be a check for the reason of request failure. And if the reason is token expiration, then the token should be invalidated. Otherwise, throw an error. Something like this...

      Delete
    5. Thanks Yuriy. I will take a look.

      Delete
  64. Hello,

    This library is excellent , i would like to use this in my example. How can i change the default login methods , so that the user can enter their gmail id and password.

    thank you

    ReplyDelete
    Replies
    1. @Kumar: You can use com.pras.auth.BasicAuthenticatorImpl to prompt user for Gmail ID and Password.
      It supports 2 ways of authentication- Android's Authenticator and User input based authentication.
      -Prasanta

      Delete
    2. @Prasanta : Thank you for quick response. I will use BasicAuthenticatorimpl class and get back if i have any issues.

      I have two google accounts setup on the phone
      I loaded your spreadsheet app yday , i am trying to switch to a different account on the app's main screen, i dont find way to change the account, by default i see only first account.

      is there any way to switch accounts on your app.

      Thank you , kumar

      Delete
  65. Hi Prasanta!

    Thank you so much for providing such an awesome library!!!

    Few queries:
    1) I am able to create the google spreadsheet with the help of the API, but am unable to create the same manually (google docs, through gmail). Can you help me in this regard?

    2) Also, when I create the table with the help of your API and manually add records, the added records are not being retrieved by the the API. Can you tell me how this can be resolved as well?

    Thanks in advance! :)

    -Vignesh

    ReplyDelete
  66. Prasanta,
    I see your lib only allows editing of spreadsheets created with your lib. Is there a modification that would make editing of existing spreadsheets possible?

    ReplyDelete
  67. Pls use List Feed instead of Table feed. (gss-lib-2.2b1.zip)

    ReplyDelete
  68. Hi Prasanta,

    Thanks for a great library to access Google Spreadsheet. Definitely helpful.

    Have a question though. Does editing of data allowed for List Feed, or is it just for Table Feed? I have read the comments on WorkSheet.class which states that Add/Edit/Delete of data is not yet allowed for List based feed. Is this still correct?

    Thanks in advance

    ReplyDelete
    Replies
    1. Comments might be misleading. But you can Add/Edit/Delete records for both Table (which is deprecated) and List feed. Please try with the latest Lib (2.2b1)

      Delete
    2. Thanks for the quick reply.

      Delete
  69. Hi Prasanta,

    I still don't understand how it is done.
    My query again:
    1) I am able to create the google spreadsheet with the help of the API, but am unable to create the same manually (google docs, through gmail). Can you help me in this regard?

    2) Also, when I create the table with the help of your API and manually add records, the added records are not being retrieved by the the API. Can you tell me how this can be resolved as well?

    ReplyDelete
    Replies
    1. 1. I have no idea on this.
      2. Table feed doesn't support manually entered records. For this you need to use List feed. Pls use Lib 2.2b1 and List feed methods.

      Delete
  70. Hi Paul,

    I am testing sample app GSS_Client_Android on 2.3 and 2.2 emulators and on 2.3.5 Galaxy S. Even thought I set the google account and the synch on, I get "no spreadsheet exists in your account" (and I have a few spreadsheets).

    I added the code snippet to the onCreate of the GSSAct.java as follows (without the comments)

    super.onCreate(savedInstanceState);

    Log.v(TAG, Build.PRODUCT);
    // if("sdk".equals(Build.PRODUCT) || "google_sdk".equals(Build.PRODUCT))
    // {
    // Log.v(TAG, Build.PRODUCT + ": doCustomizationForSDK method " );
    // ParseFeed.doCustomizationForSDK();
    // }

    Any ideas?

    ReplyDelete
    Replies
    1. Sorry Paul nevermind, I should have suspected the line :

      spreadSheets = factory.getAllSpreadSheets(true, "World", false);

      "World".. Thank you for the lib btw, it seems like a super time saver.

      Delete
  71. hi,I find that when we want to addListRow(HashMap records)
    in the methode, i find that workSheetURL has not been defined before ! it lacks the URL to the workSheetURL !

    ReplyDelete
  72. Hello!
    I am using your project for a small project but I ran into some strange limitation (or maybe I'm doing something wrong?)
    Do i have to put the rownames in lower case? When reading the worksheet the columns array is correct and gives mit the correct cases, but when I read the data with getData(false) the cells return the column name (.getName()) all in lower case. Any idea why this happens? Actually I have to put the names in lower case also to write with updateListRow in the Hashmap. Why is there this strange limitation? I looked into the code a little bit and it seems like google is returning it already in lower case?

    ReplyDelete
    Replies
    1. Its the limitation of Google Server API. You shouldn't use any "space" in column name and if possible don't use case, lower case is suggested to avoid any conflict.
      Thanks,
      Prasanta

      Delete
  73. Hello Prasnata,
    Your library is great.It makes me easy to acess the spreadsheet. Thx for your library.
    I have a problem and I have tried to solve it two days. Can you give me some suggestions?Thanks you.

    I want to create a table that type of cells is "String".Because I have some cells values start with "0" add record to table and Google Spreadsheet always ignore "0". ex. 0912->912.
    I use library setType() function but nothing happened. Maybe I did something wrong.

    ReplyDelete
  74. Can anyone explain the steps on how to use the library. . i am new to android. . ?

    ReplyDelete
  75. hi i hv downloaded ur gss+client sample code..but whn i run it in android emulator it says "FORCE CLOSE"..plz help

    ASAP..

    ReplyDelete
  76. hey dude...can u give me ur contact no..i need u to help me on dis..its urgent..any help will b appreciable
    thanks.

    ReplyDelete
  77. Hi Prasanta,

    I am using ur lib. I'm unable to run ur sample code. The sample code always crashes while running in simulator. I'm new in android. Classes in ur lib like Spreadsheet , SpreadsheetFactory etc could not found in this project. I dnt have any errors. Lib using is latest gss_lib_2.2.b1.jar. Please help as early as you can....

    Error log is


    04-17 22:07:11.335: I/dalvikvm(504): Failed resolving Lcom/prasanta/auth/AndroidAuthenticator; interface 28 'Lcom/pras/auth/Authenticator;'
    04-17 22:07:11.335: W/dalvikvm(504): Link of class 'Lcom/prasanta/auth/AndroidAuthenticator;' failed
    04-17 22:07:11.346: E/dalvikvm(504): Could not find class 'com.prasanta.auth.AndroidAuthenticator', referenced from method com.prasanta.GSSAct$MyTask.doInBackground
    04-17 22:07:11.346: W/dalvikvm(504): VFY: unable to resolve new-instance 45 (Lcom/prasanta/auth/AndroidAuthenticator;) in Lcom/prasanta/GSSAct$MyTask;
    04-17 22:07:11.355: D/dalvikvm(504): VFY: replacing opcode 0x22 at 0x0000
    04-17 22:07:11.355: D/dalvikvm(504): VFY: dead code 0x0002-001e in Lcom/prasanta/GSSAct$MyTask;.doInBackground ([Ljava/lang/Object;)Ljava/lang/Object;
    04-17 22:07:11.395: E/dalvikvm(504): Could not find class 'com.pras.SpreadSheet', referenced from method com.prasanta.GSSAct$MyTask.onPostExecute
    04-17 22:07:11.395: W/dalvikvm(504): VFY: unable to resolve check-cast 23 (Lcom/pras/SpreadSheet;) in Lcom/prasanta/GSSAct$MyTask;
    04-17 22:07:11.405: D/dalvikvm(504): VFY: replacing opcode 0x1f at 0x00a0
    04-17 22:07:11.405: D/dalvikvm(504): VFY: dead code 0x00a2-00ab in Lcom/prasanta/GSSAct$MyTask;.onPostExecute (Ljava/lang/Object;)V
    04-17 22:07:13.715: W/dalvikvm(504): threadid=9: thread exiting with uncaught exception (group=0x40015560)
    04-17 22:07:13.886: E/AndroidRuntime(504): FATAL EXCEPTION: AsyncTask #1
    04-17 22:07:13.886: E/AndroidRuntime(504): java.lang.RuntimeException: An error occured while executing doInBackground()
    04-17 22:07:13.886: E/AndroidRuntime(504): at android.os.AsyncTask$3.done(AsyncTask.java:200)
    04-17 22:07:13.886: E/AndroidRuntime(504): at java.util.concurrent.FutureTask$Sync.innerSetException(FutureTask.java:274)
    04-17 22:07:13.886: E/AndroidRuntime(504): at java.util.concurrent.FutureTask.setException(FutureTask.java:125)
    04-17 22:07:13.886: E/AndroidRuntime(504): at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:308)
    04-17 22:07:13.886: E/AndroidRuntime(504): at java.util.concurrent.FutureTask.run(FutureTask.java:138)
    04-17 22:07:13.886: E/AndroidRuntime(504): at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1088)
    04-17 22:07:13.886: E/AndroidRuntime(504): at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:581)
    04-17 22:07:13.886: E/AndroidRuntime(504): at java.lang.Thread.run(Thread.java:1019)
    04-17 22:07:13.886: E/AndroidRuntime(504): Caused by: java.lang.NoClassDefFoundError: com.prasanta.auth.AndroidAuthenticator
    04-17 22:07:13.886: E/AndroidRuntime(504): at com.prasanta.GSSAct$MyTask.doInBackground(GSSAct.java:85)
    04-17 22:07:13.886: E/AndroidRuntime(504): at android.os.AsyncTask$2.call(AsyncTask.java:185)

    ReplyDelete
  78. Hey,

    Library is working great but i got little problem today. I tried to add new feature to display list of collaborators but getCollaborators method returns empty list. Any idea why?

    ReplyDelete
  79. Hi Prasanta,

    Thanks for this awesome library! I am having some issues, though.

    When I try to get all worksheets from a specified spreadsheet, I receive the following error:


    05-17 14:03:23.049: E/AndroidRuntime(7189): FATAL EXCEPTION: main
    05-17 14:03:23.049: E/AndroidRuntime(7189): java.lang.NullPointerException
    05-17 14:03:23.049: E/AndroidRuntime(7189): at libcore.net.http.HttpEngine.writeRequestHeaders(HttpEngine.java:628)
    05-17 14:03:23.049: E/AndroidRuntime(7189): at libcore.net.http.HttpEngine.readResponse(HttpEngine.java:774)
    05-17 14:03:23.049: E/AndroidRuntime(7189): at libcore.net.http.HttpURLConnectionImpl.getResponse(HttpURLConnectionImpl.java:274)
    05-17 14:03:23.049: E/AndroidRuntime(7189): at libcore.net.http.HttpURLConnectionImpl.getErrorStream(HttpURLConnectionImpl.java:100)
    05-17 14:03:23.049: E/AndroidRuntime(7189): at libcore.net.http.HttpsURLConnectionImpl.getErrorStream(HttpsURLConnectionImpl.java:123)
    05-17 14:03:23.049: E/AndroidRuntime(7189): at com.pras.conn.HttpConHandler.doConnect(HttpConHandler.java:160)
    05-17 14:03:23.049: E/AndroidRuntime(7189): at com.pras.SpreadSheet.getAllWorkSheets(SpreadSheet.java:461)
    05-17 14:03:23.049: E/AndroidRuntime(7189): at com.pras.SpreadSheet.getAllWorkSheets(SpreadSheet.java:411)
    05-17 14:03:23.049: E/AndroidRuntime(7189): at com.pras.SpreadSheet.getAllWorkSheets(SpreadSheet.java:401)
    05-17 14:03:23.049: E/AndroidRuntime(7189): at test.gss.TestGssAndroidActivity$MyTask.onPostExecute(TestGssAndroidActivity.java:81)

    This is running the sample android app. It is connecting to the spreadsheets site ok, since I can get the title of the spreadsheet as well as the resourceID, but trying to access a worksheet throws the exception.

    ReplyDelete
  80. Hi Prasanta,
    I am new in android development. i want to build android application that display data in specific spreadsheet that had already created. I read your library in that code where we have to mention our google account information.
    Please help me

    Regards,
    Mrunal

    ReplyDelete
  81. Mrunal,
    Please check the sample code included in the Lib-
    // Create SpreadSheet Factory
    SpreadSheetFactory spf = SpreadSheetFactory.getInstance("[email]", "[password]");

    You can also use AndroidAuthenticator-
    gss-lib-2.2b1\sample\SpreadSheet_Auth\src\com\gss\sample\auth

    Thanks,
    Prasanta

    ReplyDelete
  82. Hi,

    I did a very simple application with this library which read a QR code and put the code in a spreedsheet of Google Docs. The application was working ok for Android 2.1 and Google Docs.
    After some weeks without using, and after to update to Android 2.3.4, the application don't work, and I dont known if the problem is Android 2.3.4, the change from Google Docs to Google Drive or anoher problem.

    I started from the beginning, downloading the sample GSS_Client_Android and compiling for differents Android versions, but I'm not to able to work this sample.

    Do you know if there is some problem with Google Drive?

    Thanks and regards,
    Sergio

    ReplyDelete
  83. Hello Prasanta,

    thx for sharing your great library with us.
    I am trying to upload some records to Google-Spreadsheet, but I am facing an issue here; maybe you can help.
    First of all, the spreadsheet was not created using your library, it was created by importing an xls-file. So this is, I suppose, the reason why it is not possible to add records to existing worksheets. For that reason I tried to create a new worksheet (within the same spreadsheet) using your library (method addWorkSheet). But adding records to this newly created worksheet using "addRecord" fails due to "HTTP Response Code 400". I suppose the reason is that addRecord is based an table-feed, but the spreadsheet itself was created through the Web-API (although the worksheet has been created using your library).
    Next try was using the method addListWorkSheet in conjunction with addListRow. This basically works as long as the column headers are all lower-case; if there is one letter uppercase in the column header, addListRow won't add the cell's content.
    I know that uppercase letters are possible using the table-feed based methods.
    Is there anything I can do to have uppercase letters in the column headers?

    Best Regards,
    bid

    ReplyDelete
  84. Hi,
    When i try i get an error saying that appliation stopped unexpectedly.Pls help

    ReplyDelete
  85. Hi Prasanta,

    I've tried your lib, but I have a problem about how to implement the lib for unpublished spreadsheet. How can I access my spreadsheet on my google account through its key? How to modify the spreadsheet cell and get the value of a cell?

    Thank you

    ReplyDelete
  86. Hi Prasanta,

    Your library works well but I have a problem. Can I retrieve a cell contains multibyte characters like Chinese or Korean ?
    Cell A1 in my spreadsheet contains words in English while cell B2 contains =GoogleTranslate(A1,"en","kr"), how to modify the value of cell A1 and retrieve the value of B2, with your library I can retrieve the value of A1 but it doesn't go for B2,your lib doesn't display the Korean characters produced from the function on Android screen. What the problem might be? any idea?

    Thanks

    ReplyDelete
  87. Hi Mr.Prasanta,
    i have tried your lib,its nice, but i have only see the spreadsheet filename and inner worksheet name but i cant see any data and how could i insert data into it...please reply soon...i stuck in it dude...

    ReplyDelete
    Replies
    1. sorry i can see the data,but i don't know how to insert or update into the spreadsheet...Thanks in advance dude...

      Delete
  88. I have just found out your library 30 mins ago, just want to say thank you.
    You did a great job.

    ReplyDelete
  89. Hi,

    Is this post still relevant.
    sample dint worked for me. Can I get some help on it?

    ReplyDelete
    Replies
    1. It works for me but i need help on how to write data to spreadsheets

      Delete
  90. Hello Hi Prasanta,

    What do i need to do so that I can have the sample insert data to spreadsheet
    like step 1 2 3......or in the manifest

    ReplyDelete
  91. Hi Prasanta, thanks for sharing your library, its a great job. I have some problems with "Share" feature. The Collaborators list return empty, and I tried add a Collaborator in this way:

    Collaborator col = new Collaborator();
    col.setRole(Collaborator.ROLE_OWNER);
    col.setScopeValue("email user");
    col.setScopeType(Collaborator.SCOPE_USER);
    spreadsheet.addCollaborator(col);

    I'm doing it correctly? thanks again!

    ReplyDelete
  92. Hi Paul, All,

    I've found the API quite interesting and I was even able to realize the lightweight database back-end for android via google spreadsheets like it was proposed here

    https://groups.google.com/forum/#!topic/google-api-java-client/O1CGZzEWRts

    Also, I've noticed that a few features in the api would be highly useful, I mean anonymous access.

    1) So, I'd include the anonymous token support introducing a new public class similar to "public class BasicAuthenticatorImpl". But instead of "http.doConnect(GOOGLE_CLIENT_LOGIN_URL,....",
    GoogleOAuthGetTemporaryToken() should be used,
    more details here

    http://translate.google.com/translate?hl=en&sl=ja&tl=en&u=http%3A%2F%2Fabekatsu.blogspot.de%2F2011%2F03%2Fgoogle-spreadsheets-android-1.html

    Now the api is limited to google users only.

    2) I didn't find the way how someone can use "Collaborator.geteditLink()" with SpreadSheetFactory class. I mean that
    SpreadSheetFactory doesn't have an interface to find "anyone" shared spreads.

    3) I'm not sure if google spreadsheets api supports "anonymous" sharing, but it's easy to insert support for "published to web" spreadsheets via
    "https://spreadsheets.google.com/feeds/list/key...," where key is some public id.

    Here is an example,
    http://blog.restphone.com/2011/05/very-simple-google-spreadsheet-code.html

    That's all from side. I've developed a simple android application (spending three days for introduction of myself to android programing and google api) just for fun (to help a friend of mine with his student project). If my time allows, I'll add 1) and 3) into
    the current snip of "google-spreadsheet-lib-android", but it would be great if you, Paul, could do it yourself.

    Cheers,
    iggy.floyd.de@gmail.com

    ReplyDelete
  93. I Got Exception java.lang.NoClassDefFoundError: com.example.spreadsheetauth.AndroidAuthenticator and application Stop working.
    You created .apk which work fine but when i compile your sample code its gave me exception. also in my application i have a authtoken string then how can getIstance of spreadsheetFactory using Authtoken string?please help

    ReplyDelete
  94. I would recommend you to try Aspose.Cells for Android, this library offers many features which developers can use in developing their android application.

    ReplyDelete
  95. Your libray is not working now please update to new version.

    ReplyDelete
  96. "I did a very simple application with this library which read a QR code and put the code in a spreedsheet of Google Docs. The application was working ok for Android 2.1 and Google Docs." Yes, we can use a web java qr code reading tool to scan and decode the barcodes on documents. http://www.businessrefinery.com/products/barcode_reader_java/main.html

    ReplyDelete
  97. Hi Mr. Prasanta,

    I am haivng an issue with your lib. Im using Spreadsheet.getInstance("email","password") auth solution and I get the following error:
    [HttpConHandler] Error in connection: java.io.FileNotFoundException: https://www.google.com/accounts/ClientLogin
    08-04 16:16:48.999 29026-29049/? I/System.out﹕ [HTTP_Response] HTTP Response Code: 404
    08-04 16:16:48.999 29026-29049/? I/System.out﹕ [HTTP_Response] HTTP Response: Not Found

    08-04 16:16:49.004 29026-29049/? W/System.err﹕ java.io.FileNotFoundException: https://www.google.com/accounts/ClientLogin
    08-04 16:16:49.005 29026-29049/? W/System.err﹕ at com.android.okhttp.internal.http.HttpURLConnectionImpl.getInputStream(HttpURLConnectionImpl.java:206)
    08-04 16:16:49.005 29026-29049/? W/System.err﹕ at com.android.okhttp.internal.http.DelegatingHttpsURLConnection.getInputStream(DelegatingHttpsURLConnection.java:210)


    it seems that ClientLogin wont work. Any fixes for that?

    ReplyDelete