google-sheetsgoogle-apigoogle-drive-apigoogle-api-clientgoogle-sheets-api

How can I format a Google Sheets spreadsheet cell with the API?


My application generates a table of data and creates a new spreadsheet document in a user's Google Drive. How can I add formatting (color, font-weight, width, etc.) to individual cells? I can't seem to find any documentation, much less how I could implement this through the google-api-ruby-client.

Most of my findings date back to Google API mailing lists that state it isn't supported.
However, I found that another application accomplishes my desired result. An example of "Smartsheet" exporting a document to Google Drive:

From Smartsheet.com:

At Smartsheet.com

And the resulting sheet in my Google Drive:

In my Google Drive


Solution

  • Smartsheet utilizes the ability of the Google API to import an Excel file. The code is roughly along these lines:

    DocsService client = new DocsService(<YOUR APP NAME>);
    client.setOAuthCredentials(<OAUTH PARAMETERS>);
    
    DocumentListEntry newEntry = new SpreadsheetEntry();
    newEntry.setMediaSource(new MediaByteArraySource(<EXCEL FILE BYTE ARRAY OUTPUT STREAM>, DocumentListEntry.MediaType.XLS.getMimeType()));
    newEntry.setTitle(new PlainTextConstruct(<FILE NAME>));
    
    DocumentListEntry insertedEntry = client.insert(new URL("https://docs.google.com/feeds/default/private/full/"), newEntry);
    
    // This is your URL to the new doc
    String docUrl = insertedEntry.getDocumentLink().getHref();
    

    We already had the ability to export a Smartsheet to an Excel file with formatting via Apache POI. Adding export to a Google Spreadsheet was quite simple for us to implement and it provided some additional functionality beyond what you could do via the API.

    Sorry for the delayed response - just happened across this question.