2

I need to download an excel sheet from Google Docs via Gspread and then multiple times I'll need to read the values of different cells in 'A1' notation. Thus, I can't just get the spreadsheet and then call val = worksheet.acell('B1').value, because the script will freeze out of too many API calls. My solution for now:

def download_hd_sheet():
    worksheet = gc.values().get(spreadsheetId=excel_id, range='variables', valueRenderOption='FORMULA').execute()['values']
    df = pd.DataFrame(worksheet)
    writer = pd.ExcelWriter("Variables.xlsx", engine='xlsxwriter')
    df.to_excel(writer, sheet_name='Sheet1', index=False, header=False)
    workbook = writer.book
    worksheet = writer.sheets['Sheet1']
    writer.save()
    book = openpyxl.load_workbook('Variables.xlsx', data_only=False)
    global hd_sheet
    hd_sheet = book.active

So far what I'm doing is:

  1. I download the values from the worksheet.
  2. Transform it (list of lists) into a pandas dataframe.
  3. Then I write the df to a .xlsx file.
  4. I read the .xlsx file to a global variable

It seems to me that I am doing so many things just to achieve something that can be done in two lines. Please, let me know what would be more effective than the above.

Linda Lawton - DaImTo
  • 106,405
  • 32
  • 180
  • 449
Karolina Andruszkiewicz
  • 459
  • 1
  • 6
  • 18
  • Although I'm not sure whether I could correctly understand about your goal, in your situation, for example, how about directly downloading the Google Spreadsheet as a XLSX file by converting with Drive API? If that was not the direction you expect, I apologize. – Tanaike Jul 14 '20 at 23:29
  • @Tanaike what I mean is - I think I actually don't have to write the sheet to a file at all. What I'd like to do is to get the google sheet and assign it to an object so I won't have to make too many API calls when getting the value of cells. If such a solution is not possible and I really need to write it to a file - could you please let me know how to dowload the XLSX file straight away? – Karolina Andruszkiewicz Jul 15 '20 at 09:10
  • Thank you for replying. From your replying, I proposed a sample script as an answer. Could you please confirm it? If I misunderstood your goal and that was not the direction you expect, I apologize. – Tanaike Jul 15 '20 at 12:02
  • Did my answer show you the result what you want? Would you please tell me about it? That is also useful for me to study. If this works, other people who have the same issue with you can also base your question as a question which can be solved. If you have issues for my answer yet, I apologize. At that time, can I ask you about your current situation? I would like to study to solve your issues. – Tanaike Jul 23 '20 at 07:10

1 Answers1

3

I believe your goal as follows.

  • You want to download the Google Spreadsheet as the XLSX data.
  • You want to use the downloaded XLSX data without saving as the file.
  • You have already been able to get and put values for Google Spreadsheet using gspread.
  • You want to achieve this using python.

In order to achieve your goal, I would like to propose the following flow.

  1. Download the Google Spreadsheet as the XLSX data using the method of Files: export in Drive API.
  2. Open the XLSX data using the downloaded binary data with openpyxl.load_workbook().

Sample script:

In this sample script, from your situation, the access token is used from the authorization for gspread.

spreadsheetId = "###"  # Please set the Spreadsheet ID.

client = gspread.authorize(credentials)
access_token = client.auth.token
url = "https://www.googleapis.com/drive/v3/files/" + spreadsheetId + "/export?mimeType=application%2Fvnd.openxmlformats-officedocument.spreadsheetml.sheet"
res = requests.get(url, headers={"Authorization": "Bearer " + access_token})
book = openpyxl.load_workbook(filename=BytesIO(res.content), data_only=False)
hd_sheet = book.active

  • By above script, the XLSX data is directly downloaded from Google Spreadsheet and openpyxl.load_workbook

  • In this case, the following libraries in addition to gspread are used.

      import openpyxl
  import requests
  from io import BytesIO

Note:

  • In this case, please include the scope of https://www.googleapis.com/auth/drive or https://www.googleapis.com/auth/drive.readonly. When you modified the scopes, please reauthorize the scopes. By this, the new scopes are reflected to the access token. So please be careful this.

References:

StephenK
  • 685
  • 5
  • 16
Tanaike
  • 181,128
  • 11
  • 97
  • 165