Use API : Google Drive Google Sheet Google Mail
# Put gdapi.py into Lib folder
import gdapi
# Drive_1=gdapi.Drive(r'D:\Python\All_Practice\GoogleAPI\token\mnbbtoken.pickle')
# Before ues API , log in your drive first.
# Login Function:
Drive=gdapi.Drive(jsonpath or picklepath)
# You can change different Drive to use or use more than 1 drive at the same time
Drive.chose_json(jsonpath)
Drive.chose_pickle(picklepath)
Drive.Download_path=yourpath # set download path, default is argvdirpath
Drive.download("filename","destination of computer")
Drive.upload("filename","destination of drive")
Drive.delete("filename")
Drive.delete_emptyfolder()
Drive.emptytrash()
Drive.list_all_file(view=1)# (self,*args,**kwargs) parameters: view=0/1 (list the found file , default 1)
dict_of_find=Drive_1.find_file(filename) # return a dict
folder_id=Drive_1.find_folder_id(folder) # return Folder id
Drive.create_newsheet('sheetname')
Drive.create_newsheet('sheetname')
Drive.create_doc('Docname')
Drive.create_Slides('Slidename')
Drive.create_form('Formname')
Link=Drive.change_permissions(file_id) # change permission to anyone can write and read , return a webViewLink
webViewLink=Drive.get_weblink(file_id) # return weblink (string)
size=Drive.get_size(file_id) # return size (string)
type_=Drive.get_type(file_id) # return type of file
user=Drive.get_lastModifyingUser(file_id) # return dict of user
sharestate=Drive.get_shared(file_id) # return boolean True / False
Drive.get_filelist() # will write a list csv to default path
Before use the sheet api , you need to login first ( >>Drive=gdapi.Drive("PICKLE PATH OR JSON PATH") )
sheet=gdapi.Sheet(sheetid) # if no sheetid it will create a newsheet
or
sheet=Drive.sheet(sheetid)
sheet=gdapi.Sheet(name="NewTitle") # it will create a new create which is named NewTitle
Data='Hi'
sheet.write("A1",Data) # A1='Hi'
Data=[['Hi','I'],['am','Test']]
sheet.write("A1:B2",Data) # A1='Hi' | B1='I'
# A2='am' | B2='Test'
sheet.write("A1:B2",Data,1) # A1='Hi' | B1='am'
# A2='I' | B2='Test'
Data=[1,2,3,4,5,6]
sheet.write("A1:F1",Data) # A1=1 | B1=2 | C1=3 | D1=4 | E1=5 | F1=6 |
arr=['abc','def','g','ad','ee','bb']
for i in range(0,len(arr)):
sheet.write("A"+str(i),arr[i])
# A1 = abc
# A2 = def
# A3 = g
# A4 = ad
sheet.sub # retrun all sub sheet
sheet.getsheet() # It will be executed when creating sheet or calling sheet
sheet.properties # reutnr color/title/defaultFormat/spreadsheetTheme etc.
string=sheet.read("A1") # return string
list_value=sheet.read("workbook2!:A1:B3") # return Value list
sheet.delete("A1:F1") #A1~F1 will be deleted
sheet.add_sheet("workbook1","workbook2")
sheet.title # return spreadsheet name
sheet.id # return sheet id
sheet.url # return sheet url
sublist=sheet.sub # return sheet sub list
sheet.title="newtitle"
sheet.resubtitle("oldtitle","newtitle")
sheet.setcolor("A1",(255,120,0),,) # (RangeSetColor,Tuple(R,G,B),HexColor,Alpha=0.5,SheetId=0)
# Description | Accept Value
#----------------------------------------------------------------
# RangeSetColor = set color on which cell | Set > "1:1"= "A1"
# Tuple(R,G,B) = set color RGB | Set > (255,255,0) # accept tuple and int
# HexColor = set color in Hex | Set > #CAFFFF , if set Hexcolor , (R,G,B) can put anything except tuple.
# Alpha = set transparent | Set > [0,1] , default=1
# SheetId = set sheetid | Set > long int , default=0 , the number in url gid=[sheetid]
Colrange="1:5" # Column A-D =1~4
Col_pixel= 20 # 20 pixels between column
Rowrange="1:4" # Row= 1~3
Row_pixel= 40 # 40 pixels between row
sheetid=0 # default=0
sheet.adjust_column_row(Colrange,Col_pixel,Rowrange,Row_pixel,*sheetid):
sheet.adjust_col(Colrange,Col_pixel,*sheetid)
sheet.adjust_row(Rowrange,Row_pixel,*sheetid)
sheet.append_col(length,*sheetid,kwargs) #append length column
# parameter:
# sheetid = 123456 # add a sheet id to access
# kwargs:
# sheetname= 'abc' # choose sheetname to access
start=1
end=6
sheet.append_col((start,end),*sheetid,**kwargs) # (start,end) is a tuple data
# also can use alphabet to represent Column number
sheet.append_col('a:e',*sheetid,**kwargs) # delete column from a to e , a will keep
sheet.append_row(length,*sheetid) #append length row
start=1
end=6
sheet.append_row((start,end),*sheetid,**kwargs) # (start,end) is a tuple data
find='string' # find string
reapcle='test' # replace string
allsheet=0 # 0=find/replace only one page,1=find/replace all page | [Optional]
sheet.FNR(find,replace,*sheetID,**kwargs)
id=123456789 # must be positive number
sheet.FNR(find,replace,id=id)
sheet.FNR(find,replace,id)
name="workbookname" # must be string
sheet.FNR(find,replace,name=name)
sheet.FNR(find,replace,allsheet=1)
colname="ABC" # sort by colname
col=1 # sort by column 1
UpOrDown=0 # 0/1 ( DESCENDING / ASCENDING ) ,Defalut 1
# sheet.sort_col(colname,*UpOrDown,**col)
sheet.sort_col('',0,col=col) # DESCENDING and sort by col 1
sheet.sort_col(colname,1) # ASCENDING and sort by colname
workrange= "A1" # put formula in which cell
formula="sum(B1:B20)" # Excel/Sheet Formula format
sheet.formula(workrange,formula,*sheetid)
workrange= "A1"
findwhat="name"
findrange="B1:B20"
formula='match("'+findwhat+","+findrange+'",0)'
sheet.formula(workrange,formula,*sheetid)
sheet.formula("workbook2!B25",'sum(workbook1!B2:B5)') # Get result in workbook2>B25 , execute function in wrokbook1 >B2:B25
findname="ABC"
index_col=sheet.get_col_index(sheetname,*sheetID,**kwargs) # return index of column
# kwargs :
# sheetname | example : sheetname='A' , find the column name in Sheetname A
findname="ABC"
index_row=sheet.get_col(sheetname,*sheetID,**kwargs) # return index_row number
sheet_id=sheet.getsub_id(findname)
sheetname="WORKBOOK"
sheet_max_column,sheet_max_row=sheet.get_sheet_size(sheetname=name)
find_sting="string to find"
find_string(find_sting,*sheetID,**kwargs) # return a list of cell