Geekly Articles each Day
📜 ⬆️ ⬇️

We generate a beautiful Google spreadsheet from our program (using the Google Sheets API v4)

Formulation of the problem


Suppose we need to create a table like this in the Python language program:

image

Features of this table:
')

Interesting? Then welcome under cat.

Decision


Immediately sweep out inappropriate libraries. For example, gspread . This is a wrapper over Google Sheets API v3 , in which there are no methods for customizing the table layout. Even the width of the column can not be set.

We will use Google Sheets API v4 .

Step 1. Create a service account


  1. Enter the Google Developers Console and create a new project (or use some of those that already exist).
  2. Enable the Drive API and Sheets API for this project.
  3. Create credentials and save private key:



Step 2. Install the necessary libraries


Namely, google-api-python-client . You can install using pip , for example:

pip install --upgrade google-api-python-client

This library will attract the necessary dependencies (such as oauth2client and others).

Step 3. Encode


3.1. Service object


Import the necessary:

 import httplib2 import apiclient.discovery from oauth2client.service_account import ServiceAccountCredentials

Create a service object to work with Google-tables:

 CREDENTIALS_FILE = 'test-proj-for-habr-article-1ab131d98a6b.json' #      credentials = ServiceAccountCredentials.from_json_keyfile_name(CREDENTIALS_FILE, ['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive']) httpAuth = credentials.authorize(httplib2.Http()) service = apiclient.discovery.build('sheets', 'v4', http = httpAuth)

3.2. Terms and id'shniki


Now for a moment we stop and discuss the terminology.


Link to a specific sheet is formed as follows:
https://docs.google.com/spreadsheets/d/ spreadsheetId /edit#gid= sheetId
If you discard #gid= sheetId , then the link opens the first sheet in the document.

3.3. New spreadsheet


Let's go back to the code. The service object we created gives us only 9 functions . One of them is called spreadsheets.create , it creates a new spreadsheet . As an argument, pass a Spreadsheet object. It is not required to fill in all of its fields, most have default values.

 spreadsheet = service.spreadsheets().create(body = { 'properties': {'title': '   ', 'locale': 'ru_RU'}, 'sheets': [{'properties': {'sheetType': 'GRID', 'sheetId': 0, 'title': '   ', 'gridProperties': {'rowCount': 8, 'columnCount': 5}}}] }).execute()

In response, we receive again the Spreadsheet object, only the filled parameters are greater:

Watch answer
 {'properties': {'autoRecalc': 'ON_CHANGE', 'defaultFormat': {'backgroundColor': {'blue': 1, 'green': 1, 'red': 1}, 'padding': {'bottom': 2, 'left': 3, 'right': 3, 'top': 2}, 'textFormat': {'bold': False, 'fontFamily': 'arial,sans,sans-serif', 'fontSize': 10, 'foregroundColor': {}, 'italic': False, 'strikethrough': False, 'underline': False}, 'verticalAlignment': 'BOTTOM', 'wrapStrategy': 'OVERFLOW_CELL'}, 'locale': 'ru_RU', 'timeZone': 'Etc/GMT', 'title': '   '}, 'sheets': [{'properties': {'gridProperties': {'columnCount': 5, 'rowCount': 8}, 'index': 0, 'sheetId': 0, 'sheetType': 'GRID', 'title': '   '}}], 'spreadsheetId': '1Sfl7EQ0Yuyo65INidt4LCrHMzFI9wrmc96qHq6EEqHM'}

It was possible to specify many of them in the request, but the default parameters suit us for solving the current problem.
The locale parameter was set to ru_RU not by chance, but more on that later.

The response contains spreadsheetId . Hooray! We go to look at the created document with our eyes ... and break off, because we do not have access to it. Even reading. Just like the usual hand-freshly created Google-table user.
And who has access? At the service account.

Click on the button "Request permission for access"?
Do not spam yourself. Clicking this button will send a letter to an e-mail like account@test-proj-for-habr-article.iam.gserviceaccount.com . It will not be possible to deliver this letter (because the domain does not exist), and you will receive a message about the unsuccessful delivery of the letter. The content of the letter also does not help, because the link for issuing access can only work if you are logged in under the owner’s account, that is, under the service account.

What to do? The answer is obvious: give access to the document using the API too.

Well, or another option
You can create a document manually on your Google disk and give access to the service account (that is, manually issue permissions to that e-mail like account@test-proj-for-habr-article.iam.gserviceaccount.com ). Then work with this document through the API.

This option did not suit me, because I needed to teach the program to create many different documents.

3.4. Access to a new document


Our service object has no method for setting up access to the document. It is simply not in the Google Sheets API. But he is in Google Drive API v3 . We write the code.

 driveService = apiclient.discovery.build('drive', 'v3', http = httpAuth) shareRes = driveService.permissions().create( fileId = spreadsheet['spreadsheetId'], body = {'type': 'anyone', 'role': 'reader'}, #      fields = 'id' ).execute()

This code gives everyone access to reading on the link . Suppose we wish instead to give edit access to user@example.com . For this instead

 {'type': 'anyone', 'role': 'reader'}

write

 {'type': 'user', 'role': 'writer', 'emailAddress': 'user@example.com'}

3.5. Some more theory


We begin the design of the table by setting the width of the columns. Oh, and where is the function? Everything is not so transparent and a bit more clever than just any setColumnWidth .

There is a function spreadsheets.batchUpdate . She immediately applies a wad of changes to the document. Or rather, she first checks the entire bundle for correctness. If everything is OK, then it atomically applies everything and returns the corresponding stack of results. The list of changes that can be applied by this function is here .

3.6. Column width


To set the width of the columns you need to do UpdateDimensionPropertiesRequest .

Read code
 results = service.spreadsheets().batchUpdate(spreadsheetId = spreadsheet['spreadsheetId'], body = { "requests": [ #    A: 317  { "updateDimensionProperties": { "range": { "sheetId": 0, "dimension": "COLUMNS", # COLUMNS -    "startIndex": 0, #     "endIndex": 1 # startIndex  , endIndex -  , # ..        [0,1), ..    A }, "properties": { "pixelSize": 317 #    }, "fields": "pixelSize" #    pixelSize       } }, #    B: 200  { "updateDimensionProperties": { "range": { "sheetId": 0, "dimension": "COLUMNS", "startIndex": 1, "endIndex": 2 }, "properties": { "pixelSize": 200 }, "fields": "pixelSize" } }, #    C  D: 165  { "updateDimensionProperties": { "range": { "sheetId": 0, "dimension": "COLUMNS", "startIndex": 2, "endIndex": 4 }, "properties": { "pixelSize": 165 }, "fields": "pixelSize" } }, #    E: 100  { "updateDimensionProperties": { "range": { "sheetId": 0, "dimension": "COLUMNS", "startIndex": 4, "endIndex": 5 }, "properties": { "pixelSize": 100 }, "fields": "pixelSize" } } ] }).execute()

It turned out very cumbersome and a lot of copy-paste. At this stage, I decided to write a small wrapper class over the Sheets API, which will give me the necessary methods in a convenient form.

3.7. Wrapper class logic


Let the wrapper class (let's call it Spreadsheet ) store the requests list and, in its runPrepared method , pass it to the spreadsheets.batchUpdate function and then clear it. The items in this list will be added by the methods of the prepare_ type of the corresponding Request .

Now the code for setting the width of the columns looks like this:

 # ss -    Spreadsheet ss.prepare_setColumnWidth(0, 317) ss.prepare_setColumnWidth(1, 200) ss.prepare_setColumnsWidth(2, 3, 165) ss.prepare_setColumnWidth(4, 100) ss.runPrepared()

And here is the code for the prepare_setColumnWidth and prepare_setColumnsWidth methods :

 class Spreadsheet: # ... def prepare_setDimensionPixelSize(self, dimension, startIndex, endIndex, pixelSize): self.requests.append({"updateDimensionProperties": { "range": {"sheetId": self.sheetId, "dimension": dimension, "startIndex": startIndex, "endIndex": endIndex}, "properties": {"pixelSize": pixelSize}, "fields": "pixelSize"}}) def prepare_setColumnsWidth(self, startCol, endCol, width): self.prepare_setDimensionPixelSize("COLUMNS", startCol, endCol + 1, width) def prepare_setColumnWidth(self, col, width): self.prepare_setColumnsWidth(col, col, width)

I will give the code of the runPrepared method a little further, because it will be replenished with something else.

3.8. Filling cells with data


To fill the cells with information in Google Sheets API v4, the function spreadsheets.values.batchUpdate is provided, which works on the same principle as spreadsheets.batchUpdate . It takes a list of rectangles and values ​​to write to each of them. In addition, it accepts the ValueInputOption parameter:


We need the first option, because it is required that the table recognize dates and formulas.

This is how you can fill in data with a pair of rectangles on a sheet without using our wrapper class:

 results = service.spreadsheets().values().batchUpdate(spreadsheetId = spreadsheet['spreadsheetId'], body = { "valueInputOption": "USER_ENTERED", "data": [ {"range": "   !B2:C3", "majorDimension": "ROWS", #   ,   (..     values -  ) "values": [["This is B2", "This is C2"], ["This is B3", "This is C3"]]}, {"range": "   !D5:E6", "majorDimension": "COLUMNS", #   ,   (..     values -  ) "values": [["This is D5", "This is D6"], ["This is E5", "=5+5"]]} ] }).execute()

We will receive such a document .

Now we will make our wrapper class provide convenient methods to achieve the same result.
Let the spreadsheets.values.batchUpdate function be called in the runPrepared method, and the prepare_setValues method add a rectangle and data to the valueRanges list, which when called runPrepared will be passed to spreadsheets.values.batchUpdate .

The code for the prepare_setValues and runPrepared methods are :

 class Spreadsheet: # ... def prepare_setValues(self, cellsRange, values, majorDimension = "ROWS"): self.valueRanges.append({"range": self.sheetTitle + "!" + cellsRange, "majorDimension": majorDimension, "values": values}) # spreadsheets.batchUpdate and spreadsheets.values.batchUpdate def runPrepared(self, valueInputOption = "USER_ENTERED"): upd1Res = {'replies': []} upd2Res = {'responses': []} try: if len(self.requests) > 0: upd1Res = self.service.spreadsheets().batchUpdate(spreadsheetId = self.spreadsheetId, body = {"requests": self.requests}).execute() if len(self.valueRanges) > 0: upd2Res = self.service.spreadsheets().values().batchUpdate(spreadsheetId = self.spreadsheetId, body = {"valueInputOption": valueInputOption, "data": self.valueRanges}).execute() finally: self.requests = [] self.valueRanges = [] return (upd1Res['replies'], upd2Res['responses'])

Fill in the data with the same pair of rectangles as in the example above, but already using our wrapper class:

 # ss -    Spreadsheet ss.prepare_setValues("B2:C3", [["This is B2", "This is C2"], ["This is B3", "This is C3"]]) ss.prepare_setValues("D5:E6", [["This is D5", "This is D6"], ["This is E5", "=5+5"]], "COLUMNS") ss.runPrepared()

3.9. Combining cells, setting boldness, display format, background color and other things


Who can not wait, you can immediately read the full code of the class Spreadsheet and an example of its use , which is a solution to the problem posed at the beginning of the article.

For a more patient reader:


Some subtleties


Q1: Why is it that when creating the document (in 3.3) the locale parameter was set to ru_RU ?
A1: The fact is that in this case, a string that looks like 2 2016 17:57:52 will be recognized by the table as a date and time. Accordingly, such cells can be used in a formula to calculate the duration (as the difference of two dates, for example).

Q2: From where it is received that the format "duration" is {'numberFormat': {'pattern': '[h]:mm:ss', 'type': 'TIME'}} ?



A2: You can get to the bottom of this by studying the documentation. But I just manually set the specific duration display format for a specific cell, and then I got the document with the program using the spreadsheets.get function, setting the includeGridData parameter to True , and looked at the value of the numberFormat parameter in that cell.

Q3: In the queries sent to the spreadsheets.batchUpdate function, the range parameter has the format GridRange :

 { 'sheetId': , 'startRowIndex': , 'endRowIndex': , 'startColumnIndex': , 'endColumnIndex':  }

And in the data rectangles for the spreadsheets.values.batchUpdate function, the range parameter is a string, of the type _!A5:E7 ( A1 notation ). Strange.
A3: Yes. Perhaps in the comments on the article, someone will explain why.
In the wrapper class, I made the toGridRange method for convenience.

Q4: Pikachu, who in the table at the beginning of the article , is planted there programmatically?
A4: No, Pikachu I placed in the table manually. I'm not sure that Google Sheets API v4 allows you to do this programmatically, but I didn’t find the necessary function right away.

Q5: Are there any restrictions on using Google Sheets API v4?
A5: Yes, they are called quotas. You can follow them in the Google Developers Console . You can also send a request to increase the quota, if it is not enough.


Conclusion


If you’ve read this far, you’ve probably learned how to programmatically create a spreadsheet, and now you’ll be eager to use Google spreadsheets in all your projects :)

Let me reiterate the most important links:

Source: https://habr.com/ru/post/305378/

More articles:


All Articles