How to create a list range validation using the Android Google Spreadsheet API

Question

I am creating an android application that creates a new Spreadsheet in Google Drive and inserts some data inside. Currently I can insert free Text and Formulas, but I need to add some specific validations and specifically a drop down list with predefined values.

The expected result should be something like this: Dropdown Image

I am using this code:

SpreadsheetFeed feed = spreadsheetService.getFeed(
                FeedURLFactory.getDefault()
                        .getSpreadsheetsFeedUrl(),
                SpreadsheetFeed.class); 
        // Creating the list of spreasheets in GDrive
        List<com.google.gdata.data.spreadsheet.SpreadsheetEntry> spreadsheets = feed.getEntries();

            // parsing trough the feed entries
        for (int i = 0; i < spreadsheets.size(); i++) {
            com.google.gdata.data.spreadsheet.SpreadsheetEntry e = (com.google.gdata.data.spreadsheet.SpreadsheetEntry) spreadsheets.get(i);
            // IF WE LOCATE THE FILE BASED ON THE FILENAME
            if( e.getTitle().getPlainText().equals(Constants.FILE_PREFIX + mFileName)) {

                Log.d(TAG, "ENTRY: " + e.getTitle().getPlainText());

                URL worksheetFeedUrl = e.getWorksheetFeedUrl();

                Log.d(TAG, "worksheetFeedUrl: " + worksheetFeedUrl);

                // The first time this feed is used to create the new worksheet
                WorksheetFeed worksheetFeed = spreadsheetService.getFeed (worksheetFeedUrl, WorksheetFeed.class);

                Log.d(TAG, "worksheetFeed OK !");

                // Create the second worksheet 
                WorksheetEntry newWorksheet = new WorksheetEntry(15, 5);
                newWorksheet.setTitle(new PlainTextConstruct("Sheet2"));
                worksheetFeed.insert(newWorksheet);

                // The second time this feed is used to get the worksheets
                worksheetFeed = spreadsheetService.getFeed (worksheetFeedUrl, WorksheetFeed.class);

                // Get the first worksheet and insert the titles
                List <WorksheetEntry> worksheetEntrys = worksheetFeed.getEntries ();
                WorksheetEntry sheet1 = worksheetEntrys.get(0);
                WorksheetEntry sheet2 = worksheetEntrys.get(1);

                URL sheet1CellFeedUrl = sheet1.getCellFeedUrl ();
                CellFeed sheet1CellFeed = spreadsheetService.getFeed (sheet1CellFeedUrl, CellFeed.class);

                sheet1CellFeed.insert (new CellEntry (1, 1, getResources().getString(R.string.cell_title_name)));
                sheet1CellFeed.insert (new CellEntry (1, 2, getResources().getString(R.string.cell_title_description)));
                sheet1CellFeed.insert (new CellEntry (3, 2, getResources().getString(R.string.some_string)));                   
                sheet1CellFeed.insert (new CellEntry (13, 2, "=COUNTIF(Sheet1!F2:F,B3)"));
                sheet1CellFeed.insert (new CellEntry (14, 2, "=B9 - TODAY()"));

                break;
           }
       }

I need a way to insert this Criteria Range List From the API programmatically and not from the UI.

Thank you.

Can you please reformat your code? It seems that the `for` and `if` are not closed. — A.L, Dec 05 '14 at 12:46
I am looking for code to create spread sheet from android application can you share your code for create spread sheet . — Rakki s, Feb 05 '15 at 14:50

score 2 · Accepted Answer · answered Dec 07 '14 at 22:35

2

requireValueInList(values, showDropdown)

You can do it with Google Apps Script, you can call google apps script from a server, but there is no way to do it with the (gdata) spreadsheet-api.

From docs requireValueInList

 // Set the data validation for cell A1 to require "Yes" or "No", with no dropdown menu.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation().requireValueInList(['Yes', 'No'], false).build();
 cell.setDataValidation(rule);

answered Dec 07 '14 at 22:35

eddyparkinson

3,680
4
26
52

Thanks a lot! I did not know for the Google Apps Script existence at all. You saved me a lot of time. I will definitely try with Google Apps Script. I just don't like how messy Google did everything, mixing APIs around... – kirilv Dec 07 '14 at 22:55
Google apps script is JavaScript, so syntax and built in functions are all javascript. – eddyparkinson Dec 08 '14 at 00:15
I tend to use ethercalc these days, it is open source and can do much of what google sheets can do. ethercalc.org - has formulas for dropdown lists and suggestbox. – eddyparkinson Oct 06 '16 at 04:20

score 0 · Answer 2 · answered Dec 08 '14 at 20:49

0

Apps Script is primarily designed for workflow automation on top of Google Apps. And there is no easy way how to connect it with clients but there is a way. Depends what are your requirements and who will use the app.

I am more interested in Android integration with Google Spreadsheets. I was expecting that Drive API can handle creation and modification of Google Spreadsheets. But it seems the Google Spreadsheets API is the only way to go.

answered Dec 08 '14 at 20:49

jogo

91
1
2

It actually can create and modify spreadsheets, but with serious restrictions. For example you cannot set background colors, fonts styles, list validations etc... http://stackoverflow.com/a/27315850/3360693 – kirilv Dec 08 '14 at 20:55
1

With Google Apps Script you can do all this stuff. Check the webservices https://developers.google.com/apps-script/guides/html/ Basically you can expose functionality of the Script over web service and for example communicate with it in JSON. Send JSON data from Android client and the service will do the necessary stuff in Google Spreadsheets. But as I said, depends what is the use case scenario as all Google Apps services have quotas and limitations: https://developers.google.com/apps-script/guides/services/quotas – jogo Dec 09 '14 at 09:35

How to create a list range validation using the Android Google Spreadsheet API

2 Answers2

requireValueInList(values, showDropdown)

Linked