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

Question

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:

And the resulting sheet in my Google Drive:

Answer 1

(Feb 2017) As of Google I/O 2016, developers no longer need to export to Excel nor create a new Sheet w/the desired formatting, so the other answers are now dated. You can now format cells using the Google Sheets API. Here's a short Python example that bolds the 1st row (assuming the file ID is SHEET_ID and SHEETS is 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 API quickstart sample) or any other language supported by the Google APIs Client Libraries.

The Sheets API provides features not available in older releases, namely giving developers programmatic access to a Sheet as if you were using the user interface (frozen rows, cell formatting[!], resizing rows/columns, adding pivot tables, creating charts, etc.). If you're new to the API, I've created a few videos with somewhat more "real-world" examples:

• Migrating SQL data to a Sheet plus code deep dive post
• Formatting text using the Sheets API plus code deep dive post
• Generating slides from spreadsheet data plus code deep dive post

To see what else you can do with Google Sheets via its REST API or Google Apps Script, check out my other videos. As you can tell, the Sheets API is primarily for document-oriented functionality as described above, but to perform file-level access such as import/export, copy, move, rename, etc., use the Google Drive API instead.

Answer 2

The APIs only provide access to the data and do not expose any methods to add formatting.

Answer 3

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.

Answer 4

Another option (and the one that ended up using) is to manually create a Google Sheet file, with all of the formatting pre-configured, as a template. Then, instead of creating a new spreadsheet document in the user's Google Drive, copy the template, like so:

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 that code, templateFileId is the file id of your shared template. You can get this fileId from your shared template file in any number of ways, but the quick-and-dirty way is just to copy-and-paste it out of the URL when you share it.

For instance, if the sharing URL is:

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

Then the file id is 1234567890abcdefghijklmnop

In my case there is nothing private in the template itself, so I just shared it with 'anyone with the link' configured for 'can view', as described here:

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

If you need to keep the contents of the template file private, then you'll need to find some way to ensure that the account specified by config.google.clientId has access to it.

Hope that helps!

Answer 5

If, like me, uploading a pre-formatted Excel sheet isn't sufficient, then Google Apps Script looks like it might be the way to go. The Range class specifically lets you manipulate at least some of the formatting you were asking about.

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

setFontColor() and setFontWeight() are there, but I don't know of anything for cell width yet.

Importantly, I have also not yet figured out how to bind a Google Apps Script to the sheet that I'm creating using the Google Drive API SDK (Node/Javascript in my case, Ruby in yours).

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

It's been a while since your question, so I'm betting you've already solved it some other way. I'm also not necessarily suggesting porting everything in your app over to Google Apps Script (although I'm seriously considering it myself...), but if you or some other reader figures out how to bind a Google App Script to a spreadsheet with the google-api-ruby-client, you might be good-to-go.