How to programmatically create a Google Docs spreadsheet WITH CONTENT?

Question

I have found multiple StackOverflow questions dealing with how to create or edit Google Doc spreadsheets using the Google Spreadsheets API, or older API's. However, this Spreadsheets API seems to be part of the "gdata" library, which to my understanding is deprecated.

Newer StackOverflow answers show how to create an empty spreadsheet using the Drive API, which seems more current. However, from looking at the documentation and examples for that API, it seems to only let you create new EMPTY files with the spreadsheet MIME type. I have not found any functionality for creating a spreadsheet with actual content (i.e. rows, columns, worksheets, etc).

What is the current process for creating a new Google Doc spreadsheet AND populating it with content? Does the Drive API have functionality that I'm not understanding? Is the "gdata" library (or at least its Spreadsheets API portion) not completely deprecated after all? Is there some third approach that I've missed altogether? I'm working with Java code, to the extent that matters, although I'm sure that any Python API would have a Java equivalent.

score 3 · Answer 1 · answered Feb 05 '17 at 13:57

with reference to the new spreadsheet API v4

I have The Best and easy method:

Step 1

Create AsyncTask class, pass the 'GoogleAccountCredential credential' to it.

Step 2

Use the API to create the a new SpreadSheet.

CODE

 private class MakeRequestTask extends AsyncTask<Void, Void, Void> {
    private com.google.api.services.sheets.v4.Sheets mService = null;

    // The constructor
    MakeRequestTask(GoogleAccountCredential credential) {
        HttpTransport transport = AndroidHttp.newCompatibleTransport();
        JsonFactory jsonFactory = JacksonFactory.getDefaultInstance();
        mService = new com.google.api.services.sheets.v4.Sheets.Builder(
                transport, jsonFactory, credential)
                .setApplicationName("Android spreadsheet client")
                .build();
    }                    

    protected void doInBackground(Void... params) {

        // function to create the spreadsheet
        creadSpreadSheet();
    }

    // creates a new spreadsheet
    private void creadSpreadSheet() throws IOException{
        com.google.api.services.sheets.v4.model.Spreadsheet mSpreadsheet, newSpreadSheet;
        mSpreadsheet = new Spreadsheet();
        SpreadsheetProperties spreadsheetProperties = new SpreadsheetProperties();
        spreadsheetProperties.setTitle("Demo SpreadSheet");// name of your spreadsheet
        mSpreadsheet = mSpreadsheet.setProperties(spreadsheetProperties);


        newSpreadSheet = mService.spreadsheets()
                .create(mSpreadsheet)
                .execute();

        // this 'newSpreadsheet' is ready to use for write/read operation.
    }

}

NOTE: Don't forget to put 'SheetsScopes.SPREADSHEETS' scope in the 'credential' in onCreate().

String[] SCOPES = { SheetsScopes.SPREADSHEETS};
    credential = GoogleAccountCredential.usingOAuth2(
            getApplicationContext(), Arrays.asList(SCOPES))
            .setBackOff(new ExponentialBackOff());

score 2 · Accepted Answer · answered Jan 19 '14 at 14:35

2

Answer in bullet form ...

It's only the old docslist API which is deprecated. The spreadsheet API is still alive and kicking since there is no replacement
The gdata libs are possibly no longer supported, but you will probably be better served using the spreadsheet API directly anyway
The Drive API is only concerned with operations at the whole file level
You could create a populated spreadsheet using the Drive API by uploading a file in a format that can be converted to a Google Spreadsheet, eg. MS Excel
Be aware that the spreadsheet API (and possibly the Drive API) do not yet support the new (end 2013) spreadsheet format

answered Jan 19 '14 at 14:35

pinoyyid

21,499
14
64
115

1

1) Gdata is still supported. 2) to answer the original question you use the spreadsheet cell feed to write data. If its a table you use the cells feed to write the header then the rows feed for rows. Drive api lets make a copy of a spreadsheet too so you start with a template. – Zig Mandel Jan 19 '14 at 16:33
1

Thanks. Amazingly enough, the Drive API (https://developers.google.com/api-client-library/java/apis/drive/v2) offers a couple of "Quickstart" project skeletons that are Maven-based... yet the Spreadsheets API is not available on Maven Central or any other public repo. It doesn't appear to be Mavenized at all. I've never worked on a more frustrating platform in my career. Google offers a lot of very nice tools, but there is no coherent structure or direction to any of it. It feels more like academia than enterprise... – Steve Perkins Jan 19 '14 at 23:54
2

@StevePerkins Welcome to developing with Drive :-) A bit of history helps. Google recognised that their APIs were a mess, and so in 2012 launched a new API infrastructure based around OAuth/Cloud Console/REST/JSON etc etc. As each old API was replaced with a new equivalent, it was deprecated. Some APIs such as spreadsheet, haven't been replaced yet. What tends to confuse beginners (we've all been there) is that the old documentation (Google and third party) hasn't been withdrawn , and often ranks highly in search results due to its age. – pinoyyid Jan 20 '14 at 02:53

score 0 · Answer 3 · answered Jan 19 '14 at 23:18

0

Google-apps-script will also create Spreadsheets and let you add data. See https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app It can do more than the GData api, i.e. google-apps-script can set cell fonts/colors etc.

But depends on your needs, GData is low level in style, and so I find it faster. There is a simple google GData demo CellDemo.java. http://gdata-java-client.googlecode.com/svn-history/r51/trunk/java/sample/spreadsheet/cell/CellDemo.java It show off the Gdata Spreadsheets API features.

answered Jan 19 '14 at 23:18

eddyparkinson

3,680
4
26
52

You can not call google apps script commands from the api. The api only provides an execute command,that may run a script _previously_ stored in the file. – mvbentes Oct 25 '16 at 09:28
@mvbentes Not sure what you mean. Do you want to modify code via an API? – eddyparkinson Oct 26 '16 at 03:10
I think the OP meant to create and populate a table from from outside the google docs environment, hence the API. That is what I need anyway. I tried the apps script approach, but, as it says on its overview: "Apps Script lets you do more with Google. All on a JavaScript platform in the cloud.". – mvbentes Oct 26 '16 at 07:22
[I hit enter to get a new line. To conclude] One can not run its commands _directly_ from _outside_, as it is not an API. What it does provide is an _execution API_ that has the single command `run`, letting you _indirectly_ run a _previously_ stored script in the "cloud". – mvbentes Oct 26 '16 at 07:33
You can pass parameters to the 'single command' But yes, if you have a lot of data, that it is not a good solution. The spreadsheet API, or even ethercalc would be better. (ethercalc has some google docs like features, but is open source, ethercalc.org) – eddyparkinson Oct 28 '16 at 01:00

How to programmatically create a Google Docs spreadsheet WITH CONTENT?

3 Answers3

Linked