Bearbeiten Sie die Google-Tabelle mit Python (kostengünstiger RPA-Fall mit Google API und Python)
Einführung
Wie oft haben Sie die Möglichkeit, mit Diagrammsoftware wie SpreadSheet und Excel zu arbeiten? Es wird erwartet, dass viele Menschen viel Zeit in ihrer Arbeit verbringen.
Wenn Sie Diagrammdaten bearbeiten können, die mit Python viel Zeit in Anspruch nehmen, und Routinearbeiten automatisieren können, führt dies mit Sicherheit zu einer betrieblichen Effizienz. Lassen Sie uns nun mit SpreadSheet im beliebten Python arbeiten.
Überblick
In diesem Artikel veröffentlichen wir eine Python-Klasse zum "Lesen / Schreiben / Löschen / Ändern des Formats" für SpreadSheet in Google Drive.
Es gibt bereits eine praktische Python-Bibliothek namens gspread auf den Straßen, aber versuchen wir es für die folgenden Zwecke.
- Gewöhnen Sie sich an die Verwendung der Google API
- Versuchen Sie, das Format über Request zu ändern
Dieses Mal werde ich ein Beispiel für das Definieren, Instanziieren und Verwenden einer Klasse vorstellen. Sie können mehrere SpreadSheets gleichzeitig bearbeiten, indem Sie sie instanziieren. Probieren Sie es also aus. Außerdem ist Class nur ein Beispiel. Wenn Sie interessiert sind, passen Sie es bitte selbst an.
Annahme
- In diesem Artikel wird das Jupyter-Notizbuch verwendet, um die Tabelle im Pandas-Datenrahmen zur besseren Übersicht darzustellen. Es kann jedoch auch mit einer normalen Py-Datei verwendet werden. --Bitte führen Sie den folgenden Artikel aus, damit Sie sich von Python aus bei der Google Sheets API authentifizieren können
- Laden Sie token.pickle von der unten angegebenen offiziellen URL herunter und benennen Sie es in "tablesheet_token.pickle" um - https://developers.google.com/sheets/api/quickstart/python
- Wenn Sie überprüfen möchten, wie Sie das obige Token mit Erläuterungen herunterladen können, lesen Sie bitte die folgende URL - https://qiita.com/ken0078/items/ece6fe2a871446383481
Verfahren
** 1. Bereiten Sie die folgende Tabelle vor **
Dieses Mal habe ich Blätter mit Werten vorbereitet, die in den Spalten A bis E und den Zeilen 1 bis 10 eingegeben wurden. (Der Name des Arbeitsblatts lautet 'Beispiel')
** 2. Erstellen Sie ein Ipynb in Jupyter Notebook und kopieren Sie die folgende Klasse und führen Sie sie in den Schritt aus **
Code anzeigen
import re
import time
import json
import copy
import pickle
import os.path
from pprint import pprint
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
class SpreadSheetsApi:
def __init__(self,SPREADSHEET_ID,RANGE_NAME):
self.service = self.launch_api()
self.values = self.read_sheet(SPREADSHEET_ID,RANGE_NAME)
def launch_api(self):
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
creds = None
if os.path.exists('spreadsheet_token.pickle'):
with open('spreadsheet_token.pickle', 'rb') as token:
creds = pickle.load(token)
if not creds or not creds.valid:
if creds and creds.expired and creds.refresh_token:
creds.refresh(Request())
else:
flow = InstalledAppFlow.from_client_secrets_file(
'credentials.json', SCOPES)
creds = flow.run_local_server(port=0)
with open('spreadsheet_token.pickle', 'wb') as token:
pickle.dump(creds, token)
service = build('sheets', 'v4', credentials=creds)
return service
def read_sheet(self,SPREADSHEET_ID,RANGE_NAME):
# Call the Sheets API
sheet = self.service.spreadsheets()
result = sheet.values().get(spreadsheetId=SPREADSHEET_ID, range=RANGE_NAME).execute()
self.values = result.get('values', [])
return self.values
def write_values(self,SPREADSHEET_ID,RANGE_NAME,value_list):
# Call the Sheets API(Overwrite Sheets)
ValueInputOption = 'RAW'
body = {
'values': value_list,
}
result = self.service.spreadsheets().values().update(spreadsheetId=SPREADSHEET_ID, range=RANGE_NAME,valueInputOption=ValueInputOption, body=body).execute()
self.values = self.read_sheet(SPREADSHEET_ID,RANGE_NAME)
def clear_contents(self, SPREADSHEET_ID, sheet_num,start_row):
# Call the Sheets API(Append Texts)
ValueInputOption = 'USER_ENTERED'
requests = []
requests.append({
"updateCells": {
"range": {
"sheetId": sheet_num,
"startRowIndex": start_row
},
"fields": "userEnteredValue"
}
})
body = { 'requests': requests }
response = self.service.spreadsheets().batchUpdate(spreadsheetId=SPREADSHEET_ID, body=body).execute()
self.values = self.read_sheet(SPREADSHEET_ID,RANGE_NAME)
def change_format(self,SPREADSHEET_ID,sheet_num,value_list):
# Call the Sheets API(Append Texts)
ValueInputOption = 'USER_ENTERED'
requests = []
requests.append({
"repeatCell": {
"range": {
"sheetId": sheet_num,
"startRowIndex": 0,
"endRowIndex": 1,
"startColumnIndex": 0,
"endColumnIndex": len(value_list[0]),
},
"cell": {
"userEnteredFormat": {
"horizontalAlignment" : "CENTER",
"textFormat": {
"fontSize": 18,
"bold": True,
}
}
},
"fields": "userEnteredFormat(textFormat,horizontalAlignment)"
},
})
requests.append({
"updateBorders": {
"range": {
"sheetId":sheet_num,
"startRowIndex": 0,
"endRowIndex": len(value_list),
"startColumnIndex": 0,
"endColumnIndex": len(value_list[0])
},
"top": {
"style": "SOLID",
"width": 1,
},
"bottom": {
"style": "SOLID",
"width": 1,
},
"right": {
"style": "SOLID",
"width": 1,
},
"right": {
"style": "SOLID",
"width": 1,
},
"innerHorizontal": {
"style": "SOLID",
"width": 1
},
"innerVertical": {
"style": "SOLID",
"width": 1
},
}
}
)
requests.append({
"autoResizeDimensions": {
"dimensions": {
"dimension": "COLUMNS",
"startIndex": 0,
"endIndex": len(value_list[0])
}
}
})
body = { 'requests': requests }
response = self.service.spreadsheets().batchUpdate(spreadsheetId=SPREADSHEET_ID, body=body).execute()
import re
import time
import json
import copy
import pickle
import os.path
from pprint import pprint
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
class SpreadSheetsApi:
def __init__(self,SPREADSHEET_ID,RANGE_NAME):
self.service = self.launch_api()
self.values = self.read_sheet(SPREADSHEET_ID,RANGE_NAME)
def launch_api(self):
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
creds = None
if os.path.exists('spreadsheet_token.pickle'):
with open('spreadsheet_token.pickle', 'rb') as token:
creds = pickle.load(token)
if not creds or not creds.valid:
if creds and creds.expired and creds.refresh_token:
creds.refresh(Request())
else:
flow = InstalledAppFlow.from_client_secrets_file(
'credentials.json', SCOPES)
creds = flow.run_local_server(port=0)
with open('spreadsheet_token.pickle', 'wb') as token:
pickle.dump(creds, token)
service = build('sheets', 'v4', credentials=creds)
return service
def read_sheet(self,SPREADSHEET_ID,RANGE_NAME):
# Call the Sheets API
sheet = self.service.spreadsheets()
result = sheet.values().get(spreadsheetId=SPREADSHEET_ID, range=RANGE_NAME).execute()
self.values = result.get('values', [])
return self.values
def write_values(self,SPREADSHEET_ID,RANGE_NAME,value_list):
# Call the Sheets API(Overwrite Sheets)
ValueInputOption = 'RAW'
body = {
'values': value_list,
}
result = self.service.spreadsheets().values().update(spreadsheetId=SPREADSHEET_ID, range=RANGE_NAME,valueInputOption=ValueInputOption, body=body).execute()
self.values = self.read_sheet(SPREADSHEET_ID,RANGE_NAME)
def clear_contents(self, SPREADSHEET_ID, sheet_num,start_row):
# Call the Sheets API(Append Texts)
ValueInputOption = 'USER_ENTERED'
requests = []
requests.append({
"updateCells": {
"range": {
"sheetId": sheet_num,
"startRowIndex": start_row
},
"fields": "userEnteredValue"
}
})
body = { 'requests': requests }
response = self.service.spreadsheets().batchUpdate(spreadsheetId=SPREADSHEET_ID, body=body).execute()
self.values = self.read_sheet(SPREADSHEET_ID,RANGE_NAME)
def change_format(self,SPREADSHEET_ID,sheet_num,value_list):
# Call the Sheets API(Append Texts)
ValueInputOption = 'USER_ENTERED'
requests = []
requests.append({
"repeatCell": {
"range": {
"sheetId": sheet_num,
"startRowIndex": 0,
"endRowIndex": 1,
"startColumnIndex": 0,
"endColumnIndex": len(value_list[0]),
},
"cell": {
"userEnteredFormat": {
"horizontalAlignment" : "CENTER",
"textFormat": {
"fontSize": 18,
"bold": True,
}
}
},
"fields": "userEnteredFormat(textFormat,horizontalAlignment)"
},
})
requests.append({
"updateBorders": {
"range": {
"sheetId":sheet_num,
"startRowIndex": 0,
"endRowIndex": len(value_list),
"startColumnIndex": 0,
"endColumnIndex": len(value_list[0])
},
"top": {
"style": "SOLID",
"width": 1,
},
"bottom": {
"style": "SOLID",
"width": 1,
},
"right": {
"style": "SOLID",
"width": 1,
},
"right": {
"style": "SOLID",
"width": 1,
},
"innerHorizontal": {
"style": "SOLID",
"width": 1
},
"innerVertical": {
"style": "SOLID",
"width": 1
},
}
}
)
requests.append({
"autoResizeDimensions": {
"dimensions": {
"dimension": "COLUMNS",
"startIndex": 0,
"endIndex": len(value_list[0])
}
}
})
body = { 'requests': requests }
response = self.service.spreadsheets().batchUpdate(spreadsheetId=SPREADSHEET_ID, body=body).execute()
** 3. Erstellen Sie basierend auf der definierten Klasse eine Instanz mit der SpreadSheet ID (str) in 1. im vorherigen Abschnitt als Argument **
- Die Tabellenkalkulations-ID wird in alphanumerischen Zeichen unmittelbar vor "/ edit" in der Tabellenkalkulations-URL geschrieben.
spreadsheet_id ='XXXXXXXXXXX' #Tabellenkalkulations-ID als Zeichenfolge definiert
range_name ="sample" #Definieren Sie den Arbeitsblattnamen, den Sie extrahieren möchten
test_ss = SpreadSheetsApi(SPREADSHEET_ID = spreadsheet_id, RANGE_NAME = range_name) #Kalkulationstabelle/Erstellen Sie eine Instanz, um Arbeitsblattwerte zu extrahieren
Sobald Sie diese Instanz erstellen, werden die Werte des Zielblatts aus dem Arbeitsblatt extrahiert.
** 4. Konvertieren Sie die Python-Liste in Pandas Dataframe und überprüfen Sie den Lesewert ** Gleichzeitig mit dem Erstellen dieser Instanz wird die Liste der Elemente (Titel / Text / Tabelle usw.) aller Blätter aus SpreadSheet extrahiert. In diesem Beispiel werden die extrahierten Werte in test_ss.values gespeichert.
import pandas as pd
#Konvertieren Sie eine Wertetabelle in Pandas Dataframe
df = pd.DataFrame(test_ss.values,columns=['A','B','C','D','E'])
df.index = df.index + 1
Es hat den gleichen Inhalt wie die Tabelle.
** 5. Schreiben Sie die Tabelle mit den folgenden Werten mit der Methode neu ** Ich möchte den folgenden Beispielwert in dieser Tabelle wiedergeben.
example_value = [["A1","B1"],["A2","B2"]] #Wert zum Umschreiben
test_ss.write_values(SPREADSHEET_ID = spreadsheet_id, RANGE_NAME = range_name,value_list =example_value) #Methode zum Umschreiben
In der Sheets-API wird eine Sheet-Matrix durch eine zweischichtige Mehrfachliste dargestellt. Die Reihenfolge der Liste in der ersten Ebene zeigt die Blattzeile, und die Reihenfolge der Liste in der zweiten Ebene ist die Spalte.
** 6. Überprüfen Sie, ob der umgeschriebene Wert wiedergegeben wird
Genau, nur die Spalten A und B in den Zeilen 1-2 wurden auf die angegebenen Werte umgeschrieben.
** 7. Format umschreiben ** Als nächstes schreiben wir nicht nur den Wert, sondern auch das Format mit dem folgenden Code neu.
test_ss.change_format(sheet_num=0, SPREADSHEET_ID = spreadsheet_id, value_list=test_ss.values) #Format Umschreibemethode
Der Grund für die Verwendung von value_list als Argument besteht darin, die Anzahl der zu bearbeitenden Matrizen zu lesen.
** 8. Überprüfen Sie, ob das umgeschriebene Format wiedergegeben wird **
Das Format wurde nur dort geändert, wo der Wert eingegeben wurde.
Dieses Mal habe ich eine Regelzeile auf die gesamte Liste angewendet und die Zeichen in der ersten Zeile groß und fett gemacht. Wenn Sie es nach Ihren Wünschen formatieren möchten, schreiben Sie den Anforderungsinhalt unter Bezugnahme auf die Referenz der Google Sheets-API neu.
Recommended Posts