Automating the Google Spreadsheet Tasks with Python and TSSFL Stack

[Eli]

Significant part of today’s success in any industry, business or even the most basic level of functionality is dependent on speed and automation of various tasks.

Instead of wasting a lot of time daily to extract data, copying and pasting them into spreadsheets and manually creating reports. We can just run a single script to upload data into a spreadsheet and generate a desired report with just one click. Report automation is a great time saver since it saves you from spending a lot of time on manual boring tasks and hence giving you more time to focus on the data analysis part.

With excellent combination of TSSFL ODF tools, the only thing you can do is to set up a Google service account. This requires the use of Google APIs (Google Sheets API v4 and Google Drive API) to read google spreadsheets data using Python, update the data in the spreadsheet, plot graphs/charts, generate reports, and even carry out more advanced data tasks.

An excellent combination of TSSFL Open Discussion Forums, Python gspread, Pandas and other libraries together with Google Sheets API v4 is an extremely robust approach to manipulating Google spreadsheets.

The requirements are, go to Google Cloud Console/Platform (initially, Google Developers Console, see Python Quickstart and gspread documentation), and:

• Create Google Service Account.
• Create a new project.
• Download the credentials file and name it "credentials.json". Put it in the same directory as the Python code below. In our case .json file will be downloaded from the Dropbox and placed in the same directory as the code below.
• Create a Google spreadsheet, put some data, and share it with the "client_email" from the credentials.json file.
• Get the spreadsheet ID from the spreadsheet URL. This ID is the part of URL between d/ and /edit.

But, you are lucky -- you do not need to do all of these, we have worked them out for you. Run the code below at https://www.tssfl.com/programming-and-c ... forum-6300 (copy and paste the code into the box under this URL, choose Python from the dropdown menu on the bottom right of the box, then click Compute) and check how the data in the spreadsheet at the end of this post is manipulated!

Code: [Select all] [Expand/Collapse]

1. import gspread
2. import urllib.request
3. urllib.request.urlretrieve("https://www.dropbox.com/s/m728v370159b2xm/credentials.json?dl=1", "credentials.json")
4.  
5. gc = gspread.service_account(filename="credentials.json")
6. sh = gc.open_by_key("1O19ketp1DpZwUytV_WqAlxafOBfwiCHiR-0F5eTir6k") #Open spreadsheet
7. #Open by url
8. #sh = gc.open_by_url("https://docs.google.com/spreadsheets/d/1O19ketp1DpZwUytV_WqAlxafOBfwiCHiR-0F5eTir6k/edit#gid=0")
9. #the spreadsheet ID starts with 1019ke.... between "" in the line above
10. worksheet = sh.sheet1
11. #Get all data records as dictionary
12. data1 = worksheet.get_all_records()
13. print("All records:", data1)
14. #Get all data records as list of lists
15. data2 = worksheet.get_all_values()
16. print("Records as lists of lists:", data2)
17. #Get a specific row data
18. data3 = worksheet.row_values(1)
19. print("First row:", data3)
20. #Get column one values
21. data4 = worksheet.col_values(1)
22. print("First column:", data4)
23. #Get data for specific cell, say A1, A2
24. data5 = worksheet.get("A2")
25. print("Cell A2:", data5)
26. #Get data across a range of cells
27. data6 = worksheet.get("A2:C2")
28. print("A2:C2:", data6)
29. #Get Cell B1 value
30. val = worksheet.acell('B1').value
31.  
32. #Update the spreadsheet
33. #new_user1 = ["Mishaeli", 1, "Africa"]
34. #worksheet.insert_row(new_user1, 3) #This will insert row 3 everytime you run the code
35.  
36. #Append rows
37. #new_user2 = ["Lyne", 24, "Australia"]
38. #worksheet.append_row(new_user2) #This will append row everytime you run the code
39.  
40. #Update a cell B3 (row 3, column 2) and C3 (row 3, col 3)
41. #worksheet.update_cell(3, 2, 12)
42. #worksheet.update_cell(3, 3, "America")
43.  
44. #Delete row/column
45. #worksheet.delete_rows(5)
46.  
47. import pandas as pd
48. #Pandas is extremely very useful for Google spreadsheets
49. #Convert the json to Pandas dataframe
50. df = pd.DataFrame.from_dict(data1)
51. print(df)
52.  
53. #View the top records
54. #print(df.head())
55. #Finally,
56. import os
57. os.remove("credentials.json")

If you want to test your own spreadsheets and data, you can generate spreadsheets from your Google Drive, and share them with this email "tssfl-odf-sheets-api@sheets-api-demo-305109.iam.gserviceaccount.com", then copy the ID of the spreadsheet you want to test, and replace the one in the code above with it.

[Eli]

Using Apps Script and JavaScript programming language to automate Google Drive, Google Docs & Google Sheets, or imported Excel file:

[Eli]

We can just use Pandas only to pre/process Google Spreadsheet data:

Code: [Select all] [Expand/Collapse]

1. import pandas as pd
2.  
3. #Alternative
4. #If your file only has one sheet, replace sheet_url
5. sheet_url = "https://docs.google.com/spreadsheets/d/1O19ketp1DpZwUytV_WqAlxafOBfwiCHiR-0F5eTir6k/edit#gid=0"
6. url_1 = sheet_url.replace('/edit#gid=', '/export?format=csv&gid=')
7.  
8. data = pd.read_csv(url_1) #Pandas DataFrame
9. #Get all data records as dictionary
10. print(data)
11. #Get all data records as list of lists
12. lol = data.values.tolist()
13. print("Records as lists of lists:", lol)
14.  
15. #Get a specific row data
16. print("Value of row 0")
17. display(data.iloc[0])
18.  
19. #Get column names of Pandas dataframe
20. cols = data.columns
21. print(cols)
22. print("The first column:")
23. print(data["Name"])
24.  
25. #Select multiple columns using column names as list
26. print(data[['Name','Age']].head())
27.  
28. #Select first two columns
29. print(data[data.columns[0:2]].head())
30.  
31. #Get data for specific cell, say A1, A2,
32. #Pandas assumes that the first row is a header
33. print(data.iloc[0]['Name'])
34. print(data.at[1, "Continent"])
35. data["None"] = data["Number"]
36. data['F'] = 'foo'
37. data['H'] = 'oof'
38. print(data)