0

I'm working with http://pygsheets.readthedocs.io/en/latest/index.html a wrapper around the google sheets api v4.

I have a small script where I am trying to select a json sheet to upload to a google sheets worksheet. The file name is made up of the:

spreadsheetname_year_month_xxx.json

The code:

import tkinter as tk
import tkFileDialog
import pygsheets


root = tk.Tk()
root.withdraw()
file_path = tkFileDialog.askopenfilename()
print file_path
file_name = file_path.split('/')[-1]
print file_name
file_name_segments = file_name.split('_')
spreadsheet = file_name_segments[0]
worksheet = file_name_segments[1]+'_'+file_name_segments[2]

gc = pygsheets.authorize(outh_file='client_secret_xxxxxx.apps.googleusercontent.com.json')

ssheet = gc.open(spreadsheet)
ws = ssheet.add_worksheet(worksheet(ssheet,str(raw_input(file_path))))

The file path leads to a generated json file that looks like:

{
  "count": 12, 
  "results": [
    {
      "case": "abc1", 
      "case_name": "invalid", 
      "case_type": "invalid", 

    }, 
    {
      "case": "abc2", 
      "case_name": "invalid", 
      "case_type": "invalid", 
      },
............

I am getting :

File "upload_to_google_sheets.py", line 27, in <module>
ws = ssheet.add_worksheet(worksheet(ssheet,str(raw_input(file_path))))
TypeError: 'unicode' object is not callable

As you can see I'm trying to instantiate a worksheet with the json data. What am I doing wrong?

user1592380
  • 34,265
  • 92
  • 284
  • 515
  • are you sure you are doing it right? because the jsonSheet param contains the properties of the worksheet sheet, hence should be of this format https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#Sheet . Are you trying to update the sheet with the values in the json document? – Nithin Feb 20 '17 at 19:46

1 Answers1

1

The JsonSheet param is not for values of the worksheet, it is for specifying the properties of the worksheet, hence should be of this format.

As directly converting a json to spreadsheet is rather ambiguous, you will need to first convert it to a numpy array (matrix) or an pandas DataFrame. Then you can use set_as_df of just update_values for updating the spreadsheet.

Nithin
  • 5,470
  • 37
  • 44
  • Thank you for the fast answer. I have created the pygsheets tag as you requested. I really appreciate your work here. I will investigate numpy arrays and pandas dfs. If I can ask a follow up: I have a lot of CSVs which I would like to upload, what would be your recommended approach using your library? – user1592380 Feb 20 '17 at 20:31
  • by numpy array i meant just a list of lists. About csv you could just use the csv module and read the csv's as matrix(list of lists), and use the update_cells. Or you can use the pandas read_csv for reading csv and set_as_df. I would recommend using csv module as it might be a bit faster and pandas support might be removed as it's adding heavily to import time. – Nithin Feb 20 '17 at 20:39
  • Thank you. I must have missed it, but where is the csv module in pygsheets? – user1592380 Feb 20 '17 at 20:56
  • pygsheets just take an generic matrix, you can use the csv module to read a csv into that format. https://stackoverflow.com/questions/24662571/python-import-csv-to-list – Nithin Feb 20 '17 at 21:05
  • I have a follow up http://stackoverflow.com/questions/42381184/conditional-formatting-with-pygsheets-for-google-sheets-api , if you have time. – user1592380 Feb 22 '17 at 01:44