Modifier la feuille de calcul Google avec Python (cas RPA à faible coût avec API Google et Python)
introduction
À quelle fréquence avez-vous l'occasion de travailler avec des logiciels de création de graphiques tels que SpreadSheet et Excel? On s'attend à ce que de nombreuses personnes consacrent beaucoup de temps à leur travail.
Si vous pouvez manipuler des données de graphique qui prennent beaucoup de temps avec Python et automatiser le travail de routine, cela conduira sûrement à une efficacité opérationnelle. Maintenant, travaillons avec SpreadSheet dans le populaire Python.
Aperçu
Dans cet article, nous publierons une classe Python pour «lire / écrire / effacer / modifier le format» pour SpreadSheet dans Google Drive.
Il existe déjà une bibliothèque Python pratique appelée gspread dans les rues, mais essayons-la pour les raisons suivantes.
- Habituez-vous à utiliser l'API Google
- Essayez de changer le format via Request
Cette fois, je vais présenter un exemple de définition d'une classe, de son instanciation et de son utilisation. Vous pouvez modifier plusieurs feuilles de calcul en même temps en instanciant, alors essayez-le. De plus, Class n'est qu'un exemple, donc si vous êtes intéressé, veuillez le personnaliser vous-même.
supposition
- Cet article utilise le bloc-notes Jupyter pour illustrer le tableau dans le Pandas Dataframe pour plus de clarté visuelle. Cependant, il peut également être utilisé avec un fichier Py normal.
- Veuillez lire l'article suivant pour vous authentifier auprès de l'API Google Sheets depuis Python --Téléchargez token.pickle à partir de l'URL officielle ci-dessous et renommez-le en "spreadsheet_token.pickle" - https://developers.google.com/sheets/api/quickstart/python --Si vous souhaitez vérifier comment télécharger le jeton ci-dessus avec une explication, veuillez vous référer à l'URL suivante - https://qiita.com/ken0078/items/ece6fe2a871446383481
procédure
** 1. Préparez la feuille de calcul suivante **
Cette fois, j'ai préparé des feuilles avec des valeurs entrées dans les colonnes A à E et les lignes 1 à 10. (Le nom de la feuille de travail est «échantillon»)
** 2. Créez un ipynb dans Jupyter Notebook et copiez et exécutez la classe suivante dans l'étape **
<détails> ** 3. En vous basant sur la classe définie, créez une instance avec l'ID de feuille de calcul (str) dans 1. ci-dessus comme argument ** Dès que vous créez cette instance, les valeurs de la feuille cible seront extraites de la feuille de calcul. ** 4. Convertissez la liste Python en Pandas Dataframe et vérifiez la valeur de lecture **
En même temps que la création de cette instance, la liste des éléments (titre / corps / tableau, etc.) de toutes les feuilles sera extraite de SpreadSheet.
Dans cet exemple, les valeurs extraites sont stockées dans test_ss.values. ** 5. Réécrivez la feuille de calcul avec les valeurs suivantes en utilisant la méthode **
Je voudrais refléter l'exemple_valeur ci-dessous dans ce tableau. Dans l'API Sheets, une matrice de feuille est représentée par une liste multiple à deux couches.
L'ordre de la liste dans la première couche montre la ligne de la feuille, et l'ordre de la liste dans la deuxième couche est la colonne. ** 6. Vérifiez si la valeur réécrite est reflétée
Exactement, seules les colonnes A et B des lignes 1 et 2 ont été réécrites avec les valeurs spécifiées. ** 7. Format de réécriture **
Ensuite, réécrivons non seulement la valeur mais également le format avec le code suivant. La raison de prendre value_list comme argument est de lire le nombre de matrices à éditer. ** 8. Vérifiez si le format réécrit est reflété ** Le format n'a été modifié que là où la valeur est entrée. Cette fois, j'ai appliqué une ligne réglée à toute la liste et j'ai rendu les caractères de la première ligne grands et gras.
Si vous souhaitez le formater comme vous le souhaitez, veuillez réécrire le contenu de la demande en vous référant à la référence de l'API Google Sheets.
Recommended Posts
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()
spreadsheet_id ='XXXXXXXXXXX' #ID de feuille de calcul défini comme une chaîne
range_name ="sample" #Définissez le nom de la feuille de calcul que vous souhaitez extraire
test_ss = SpreadSheetsApi(SPREADSHEET_ID = spreadsheet_id, RANGE_NAME = range_name) #Tableur/Créer une instance pour extraire les valeurs de la feuille de calcul
import pandas as pd
#Convertir une table de valeurs en Pandas Dataframe
df = pd.DataFrame(test_ss.values,columns=['A','B','C','D','E'])
df.index = df.index + 1
Il a le même contenu que la feuille de calcul.
example_value = [["A1","B1"],["A2","B2"]] #Valeur à réécrire
test_ss.write_values(SPREADSHEET_ID = spreadsheet_id, RANGE_NAME = range_name,value_list =example_value) #Méthode de réécriture
test_ss.change_format(sheet_num=0, SPREADSHEET_ID = spreadsheet_id, value_list=test_ss.values) #Méthode de réécriture de format
