استفاده از Google sheets در کنار پایتون، امکانات و انعطاف‌پذیری خیلی خوبی به توسعه‌دهندگان برای انجام کارها میده، برای مثال اگر می‌خواین درصد پیشرفت یک پروسه (process) رو به صورت اتومات داخل spreadsheet بروز کنید تا بتونین با استفاده از چارت نمایش بدین، یا اطلاعات یک Sheet رو برای محاسبات آماری داخل Jupyter NoteBooks وارد کنید، و یا حتی، یک دیتابیس ساده و سریع می‌خواین که اعمال CRUD رو پشتیبانی کنه، استفاده از google sheets گزینه سریع و راحتیه.
در این پست، به عنوان اولین قسمت چای، با استفاده پایتون و gspread کتابخونه‌ای که Anton Burnashev توسعه داده تنها توی چند خط کد، روی Google Sheets داده اضافه، ویرایش و حذف می‌کنیم.
قبل از شروع، موارد زیر رو آماده کنین:
برای شروع می‌تونید از این Sheet که در این آموزش استفاده می‌کنم، استفاده کنید. کافیه لینک رو باز کنید و اون رو کپی کنید:
برای دسترسی به spreadsheetتون روی Google Sheets به‌صورت برنامه‌نویسی شده، نیاز دارید تا از Google API Console یک سرویس و گواهی‌نامه OAuth2 دریافت کنید. برای اینکار قبل از هرکار نیاز است یک پروژه ایجاد کنیم، برای ایجاد پروژه وارد کنسول بشین و مطابق مراحل زیر یک پروژه ایجاد کنید:
برای دسترسی به Google Sheets نیاز است سرویس‌های Google Drive API و Google Sheets API را به ترتیب فعال کنیم. برای اینکار وارد کنسول خود شده و روی دکمه ENABLE APIS AND SERVICES کلیک کنید و هرکدام از موارد گفته شده را جستجو کرده و روی Enable کلیک کنید.
همچنین می‌توانید روی لینک هرکدام در همین پست کلیک کرده و آن‌ها را Enable کنید.
اگر دکمه Enable برای شما غیر فعال است، احتمالا پروژه ساخته نشده‌است. از اجرای درست مرحله قبل مطمئن شوید و یا تا زمانی که پروژه کامل ساخته شود، صبر کنید.
بعد از فعال شدن Google Sheet API، به داشبوردتون منتقل می‌شین، حالا مطابق مراحل زیر تنظیمات API را انجام دهید تا یک ربات با اختیارات لازم برای کار با spreadheetمون بسازیم:
اگر این گزینه را پیدا نکردین از منوی سمت چپ وارد بخش Credentials شده و روی گزینه CREATE CREDENTIALS و سپس گزینه آخر کلیک کنید:
یک service account را می‌توان به صورت یک ربات با یک اکانت Google تصور کرد که با تنظیماتی که کردیم، تنها به Google Sheetsای که اضافه شود دسترسی دارد.
پس از اتمام مراحل، یک فایل json دانلود می‌شود که نام آن را client_secret.json می‌گذاریم و در ادامه از آن برای احراز هویت استفاده می‌کنیم. این فایل رو باز کرده و Email مربوط به ربات (service account) رو پیدا و کپی کنید، سپس به Google Sheet مورد نظرتون برید و اون Sheet رو با این ربات اشتراک بذارین.
پس از تنظیم دسترسی‌ها و گواهی‌نامه‌های (credentials) لازم، حالا می‌تونیم با استفاده از پایتون به Sheet مورد نظر دسترسی داشته باشیم.
برای اینکار درواقع به رباتی که در مرحله قبل به Sheet اضافه کردیم فرمان می‌دهیم.
برای ادامه به دو پکیج پایتونی نیاز داریم:
این پکیج‌ها با استفاده از pip به‌راحتی نصب می‌شوند:
توجه‌کنید که، بهتر است قبل از نصب آن‌ها یک محیط مجازی برای این پروژه بسازیم و بعد اقدام به نصب کنیم. (اختیاری)
pip install gspread oauth2client
سپس یک فایل پایتون (برای مثال spreadsheet.py) ساخته و کد زیر را کپی کنید:
import gspread # Find a workbook by name and open the first sheet, # Make sure you use the right name here. client = gspread.service_account(filename='client_secret.json') sheet = client.open("virgool_template").sheet1 # Extract and print all of the values list_of_records = sheet.get_all_records() print(list_of_records)
حال، مقادیر زیر را در کد، مطابق اطلاعات Sheet خودتون تغییر بدید:
حال کد مورد نظر را اجرا کنید:
python3 spreadsheet.py
مشاهده می‌کنیم که اطلاعات Sheet به صورت لیستی (List) از دیکشنریها (Dictionaries) چاپ می‌شود:
در اینجا ما اطلاعات Sheet را به‌صورت یک لیست از دیکشنریها دریافت کردیم که هر کدام از المان‌های این دیکشنریها مطابق یکی از ستون‌های جدول در Sheet است و در هر جدول اولین سطر به عنوان header در نظر گرفته می‌شود (در جدول ما Title و ...).
اگر بخواهیم اطلاعات را به‌صورت لیستی از لیست‌ها دریافت کنیم می‌توانیم از متد زیر استفاده کنیم:
sheet.get_all_values()
همینطور، اگر تنها داده‌های یک سطر، ستون و یا سلول خاص از جدول را بخواهیم، می‌توانیم از متد‌های زیر استفاده کنیم:
# Getting data for a cell by (row, column) indices sheet.cell(1, 1).value # Getting data for a cell using A1 notation sheet.acell('A1').value # Getting data for a row by index sheet.row_values(1) # Getting data for a column by index sheet.col_values(1)
برای نوشتن در یکی از خانه‌های جدول می‌توانیم از متد update_cell به‌صورت زیر استفاده کنیم:
# update cell using row and column coordinates sheet.update_cell(1, 1, "I just wrote to a google sheet using Python, amazing!") # update cell using A1 notation worksheet.update('B1', 'Bingo!')
به عنوان نمونه، در مثال زیر مقدار پیشرفت Task3 در جدولمان را هر ثانیه ۱۰ درصد افزایش می‌دهیم:
به‌صورت مشابه، برای اضافه کردن یک سطر به جدول:
row = ["I'm", "inserting", "a", "row", "into", "a,", "google sheet", "with", "Python"] index = 10 sheet.insert_row(row, index)
برای پاک‌کردن محتویات یک سلول می‌توانیم محتویات آن را برابر یک رشته (String) خالی ("") قرار دهیم، اما برای پاک‌کردن یک ردیف از جدول، می‌توانیم از متد delete_row استفاده کنیم:
sheet.delete_rows(10)
توجه‌کنید که متد‌هایی که بررسی کردیم، تنها بخش کوچکی از کتابخانه gspread هستند، برای اطلاعات بیشتر و آشنایی با باقی متد‌ها، مستندات آن را بخوانید.
کتابخانه gspread توی ورژن‌های جدیدش عمل احراز هویت رو به‌صورت خودکار انجام میده، اما پیش از این نیاز بود تا خود کاربر اینکار رو انجام بده. از اونجایی که ممکنه با کد‌هاش مواجه بشین، روش انجام کار بدین صورت بود:
import gspread from oauth2client.service_account import ServiceAccountCredentials # use creds to create a client to interact with the Google Drive API scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive'] creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope) client = gspread.authorize(creds) # Find a workbook by name and open the first sheet, # Make sure you use the right name here. sheet = client.open("virgool_template").sheet1 # Extract and print all of the values list_of_records = sheet.get_all_records() print(list_of_records)
این پست، قسمت سوم از چای، مجموعه‌ای در باب «چیزی که امروز یادگرفتم» است. باقی چای‌ها رو می‌تونید از اینجا مشاهده کنید و در مورد فلسفه‌ی این کار بخونید.
