How can I format a Google Sheets spreadsheet with an API?

Question

My application creates a data table and creates a new spreadsheet document in Google Drive. How to add formatting (color, font size, width, etc.) to individual cells? It seems that I can not find any documentation, especially since I could implement it through google-api-ruby-client .

Most of my results are dated by the Google API mailing lists, which indicate that it is not supported.
However, I found that another application fulfills my desired result. Example "Smartsheet" exporting a document to Google Drive:

From Smartsheet.com:

And the resulting sheet on my Google Drive:

Answer 1

Smartsheet uses the Google API ability to import an Excel file. The code roughly corresponds to the following 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 opportunity to export Smartsheet to an Excel file with formatting via Apache POI. Adding export to a Google spreadsheet was pretty simple for us, and it provided some additional features beyond what you could do through the API.

Sorry for the delayed reply - just happened on this.

Answer 2

(February 2017) Starting with Google I / O 2016 , developers can now format cells using the Google Sheets API . Here's a short Python example that highlights the 1st line (assuming the file identifier SHEET_ID and SHEETS are the API service endpoint):

 DATA = {'requests': [ {'repeatCell': { 'range': {'endRowIndex': 1}, 'cell': {'userEnteredFormat': {'textFormat': {'bold': True}}}, 'fields': 'userEnteredFormat.textFormat.bold', }} ]} SHEETS.spreadsheets().batchUpdate( spreadsheetId=SHEET_ID, body=DATA).execute() 

I also made a developer video on this subject if that helps (see below). BTW, you can do the same in Ruby (see its quick API launch example ) or any other language supported by Google API Client libraries .

The sheet API provides functions not available in older versions, namely providing developers with programmatic access to the Sheet as if you were using the user interface (frozen rows, cell formatting [!], Resizing rows / columns, adding pivot tables, creating a chart and etc.). If you are new to the API, I created some videos with a few more “real” examples:

• Migrate SQL data per sheet plus deep dive post code
• Formatting text using the sheet APIs plus deep immersion of post code
• Create slides from spreadsheet data plus deep dive post code

As you can say, the table API is mainly intended for document- oriented functionality, as described above, but for executing a file- level access, such as import / export, copy, move, rename, etc. instead of the Google Drive API .

Answer 3

The APIs provide only access to data and do not provide any methods for adding formatting.

Answer 4

Another option (and one that is ultimately used) is to manually create a Google Sheet file with all the pre-configured formatting as a template. Then, instead of creating a new spreadsheet document in Google Drive, copy the template as follows:

 var config = require('./config'); var google = require('googleapis'); function createSheetFromTemplate(user, templateFileId, done) { var oauth2Client = new google.auth.OAuth2(config.google.clientId, config.google.clientSecret); oauth2Client.setCredentials({ access_token: user.google.token, refresh_token: user.google.refreshToken, }); var drive = google.drive({ version: 'v2', auth: oauth2Client }); drive.files.copy({ fileId: templateFileId, resource: { title: 'New Google Sheet', parents: [{ id: 'root' }] } }, function(err, response) { if (err) done(err) initializeSpreadsheet(response.id, user, done); }); } 

In this code, templateFileId is the file identifier of your shared template. You can get this file from your shared template file in any way, but the quick and dirty way is to simply copy and paste it from the URL when sharing it.

For example, if the sharing URL is:

https://docs.google.com/spreadsheets/d/1234567890abcdefghijklmnop/edit?usp=sharing

Then file ID is 1234567890abcdefghijklmnop

In my case, there is nothing private about the template itself, so I just shared it with “everyone with a link” configured to “view”, as described here:

https://support.google.com/drive/answer/2494886

If you need to keep the contents of the template file private, you need to find a way to make sure that the account specified in the config.google.clientId file has access to it.

Hope this helps!

Answer 5

If, like me, loading a pre-formatted Excel worksheet is not enough, Google Apps Script looks like it might be the way to go. The Range class specifically allows you to manipulate at least some of the formats you asked for.

https://developers.google.com/apps-script/reference/spreadsheet/range

setFontColor () and setFontWeight () are, but I don’t know anything about cell width yet.

It is important to note that I have not yet figured out how to link Google Apps Script to a worksheet that I create using the Google Drive API SDK (Node / Javascript in my case, Ruby in yours).

https://developers.google.com/apps-script/guides/bound

Some time has passed since your question, so I am sure that you have already decided this in a different way. I also do not necessarily propose to transfer everything in your application to Google Apps Script (although I seriously consider it myself ...), but if you or any other reader will know how to link the Google Script application to the table with the google-api- client ruby, you can be fine.