TSSFL TECHNOLOGY STACK

Posted: **Wed Feb 17, 2021 4:33 pm**

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 programming-and-computing-with-python-r ... 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]

import gspread
import urllib.request
urllib.request.urlretrieve("https://www.dropbox.com/s/m728v370159b2xm/credentials.json?dl=1", "credentials.json")
 
gc = gspread.service_account(filename="credentials.json")
sh = gc.open_by_key("1O19ketp1DpZwUytV_WqAlxafOBfwiCHiR-0F5eTir6k") #Open spreadsheet
#Open by url
#sh = gc.open_by_url("https://docs.google.com/spreadsheets/d/1O19ketp1DpZwUytV_WqAlxafOBfwiCHiR-0F5eTir6k/edit#gid=0")
#the spreadsheet ID starts with 1019ke.... between "" in the line above
worksheet = sh.sheet1
#Get all data records as dictionary
data1 = worksheet.get_all_records()
print("All records:", data1)
#Get all data records as list of lists
data2 = worksheet.get_all_values()
print("Records as lists of lists:", data2)
#Get a specific row data
data3 = worksheet.row_values(1)
print("First row:", data3)
#Get column one values
data4 = worksheet.col_values(1)
print("First column:", data4)
#Get data for specific cell, say A1, A2
data5 = worksheet.get("A2")
print("Cell A2:", data5)
#Get data across a range of cells
data6 = worksheet.get("A2:C2")
print("A2:C2:", data6)
#Get Cell B1 value
val = worksheet.acell('B1').value
 
#Update the spreadsheet
#new_user1 = ["Mishaeli", 1, "Africa"]
#worksheet.insert_row(new_user1, 3) #This will insert row 3 everytime you run the code
 
#Append rows
#new_user2 = ["Lyne", 24, "Australia"]
#worksheet.append_row(new_user2) #This will append row everytime you run the code
 
#Update a cell B3 (row 3, column 2) and C3 (row 3, col 3)
#worksheet.update_cell(3, 2, 12)
#worksheet.update_cell(3, 3, "America")
 
#Delete row/column
#worksheet.delete_rows(5)
 
import pandas as pd
#Pandas is extremely very useful for Google spreadsheets
#Convert the json to Pandas dataframe
df = pd.DataFrame.from_dict(data1)
print(df)
 
#View the top records
#print(df.head())
#Finally,
import os
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.

Posted: **Mon Mar 01, 2021 3:29 pm**

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

Posted: **Thu Apr 29, 2021 8:45 pm**

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

Code: [Select all] [Expand/Collapse]

import pandas as pd
 
#Alternative
#If your file only has one sheet, replace sheet_url
sheet_url = "https://docs.google.com/spreadsheets/d/1O19ketp1DpZwUytV_WqAlxafOBfwiCHiR-0F5eTir6k/edit#gid=0"
url_1 = sheet_url.replace('/edit#gid=', '/export?format=csv&gid=')
 
data = pd.read_csv(url_1) #Pandas DataFrame
#Get all data records as dictionary
print(data)
#Get all data records as list of lists
lol = data.values.tolist()
print("Records as lists of lists:", lol)
 
#Get a specific row data
print("Value of row 0")
display(data.iloc[0])
 
#Get column names of Pandas dataframe
cols = data.columns
print(cols)
print("The first column:")
print(data["Name"])
 
#Select multiple columns using column names as list
print(data[['Name','Age']].head())
 
#Select first two columns
print(data[data.columns[0:2]].head())
 
#Get data for specific cell, say A1, A2, 
#Pandas assumes that the first row is a header
print(data.iloc[0]['Name'])
print(data.at[1, "Continent"])
data["None"] = data["Number"]
data['F'] = 'foo'
data['H'] = 'oof'
print(data)

TSSFL TECHNOLOGY STACK

Automating the Google Spreadsheet Tasks with Python and TSSFL Technology Stack

Automating the Google Spreadsheet Tasks with Python and TSSFL Technology Stack

Re: Automating the Google Spreadsheet Tasks with Python and TSSFL Open Discussion Forums

Re: Automating the Google Spreadsheet Tasks with Python and TSSFL Open Discussion Forums