چای ۱: بروزرسانی Google Sheets با استفاده از پایتون

استفاده از Google sheets در کنار پایتون، امکانات و‌ انعطاف‌پذیری خیلی خوبی به توسعه‌دهندگان برای انجام کارها میده، برای مثال اگر می‌خواین درصد پیشرفت یک پروسه (process) رو به صورت اتومات داخل spreadsheet بروز کنید تا بتونین با استفاده از چارت نمایش بدین، یا اطلاعات یک Sheet رو برای محاسبات آماری داخل Jupyter NoteBooks وارد کنید، و یا حتی، یک دیتابیس ساده و سریع می‌خواین که اعمال CRUD رو پشتیبانی کنه، استفاده از google sheets گزینه سریع و راحتیه.

در این پست، ‌به عنوان اولین قسمت چای، با استفاده پایتون و gspread کتابخونه‌ای که Anton Burnashev توسعه داده تنها توی چند خط کد، روی Google Sheets داده اضافه، ویرایش و حذف می‌کنیم.

قبل از شروع، موارد زیر رو آماده کنین:

پیش‌نیاز‌ها

• پایتون ۲.۶+ یا ۳+
• مدیر بسته pip
• یک حساب‌کاربری Google و یک Google Sheet که باهاش کار کنیم.

برای شروع می‌تونید از این Sheet که در این آموزش استفاده می‌کنم، استفاده کنید. کافیه لینک رو باز کنید و اون رو کپی کنید:

احراز هویت

برای دسترسی به spreadsheetتون روی Google Sheets به‌صورت برنامه‌نویسی شده، نیاز دارید تا از Google API Console یک سرویس و گواهی‌نامه OAuth2 دریافت کنید. برای اینکار قبل از هرکار نیاز است یک پروژه ایجاد کنیم، برای ایجاد پروژه وارد کنسول بشین و مطابق مراحل زیر یک پروژه ایجاد کنید:

1. روی Create Project کلیک کنید.
2. نام پروژه رو انتخاب کنید.
3. روی Create کلیک کنید.

برای دسترسی به Google Sheet‌s نیاز است سرویس‌های Google Drive API و Google Sheets API را به ترتیب فعال کنیم. برای اینکار وارد کنسول خود شده و روی دکمه ENABLE APIS AND SERVICES کلیک کنید و هرکدام از موارد گفته شده را جستجو کرده و روی Enable کلیک کنید.

همچنین می‌توانید روی لینک هرکدام در همین پست کلیک کرده و آن‌ها را Enable کنید.

اگر دکمه Enable برای شما غیر فعال است، احتمالا پروژه ساخته نشده‌است. از اجرای درست مرحله قبل مطمئن شوید و یا تا زمانی که پروژه کامل ساخته شود، صبر کنید.

بعد از فعال شدن Google Sheet API، به داشبوردتون منتقل می‌شین، حالا مطابق مراحل زیر تنظیمات API را انجام دهید تا یک ربات با اختیارات لازم برای کار با spreadheetمون بسازیم:

1. در صفحه داشبورد، روی Create Credentials کلیک کنید.

اگر این گزینه را پیدا نکردین از منوی سمت چپ وارد بخش Credentials شده و روی گزینه CREATE CREDENTIALS و سپس گزینه آخر کلیک کنید:

1. از منوی اول (نوع API)، مورد Google Sheet API را انتخاب کنید.
2. از منوی دوم (نوع دسترسی)، گزینه Web server را انتخاب کنید.
3. در قسمت بعدی (دسترسی مورد نیاز)، گزینه Application Data رو انتخاب کنید.
4. در قسمت بعدی (استفاده از سرویس‌های ابری گوگل)، گزینه No را انتخاب کرده و روی دکمه آبی‌رنگ کلیک کنید.
5. در صفحه بعد، یک نام به این Service Account داده و Role مربوطه را برابر Project/Editor قرار دهید.

یک service account را می‌توان به صورت یک ربات با یک اکانت Google تصور کرد که با تنظیماتی که کردیم، تنها به Google Sheetsای که اضافه شود دسترسی دارد.

پس از اتمام مراحل، یک فایل json دانلود می‌شود که نام آن را client_secret.json می‌گذاریم و در ادامه از آن برای احراز هویت استفاده می‌کنیم. این فایل رو باز کرده و Email مربوط به ربات (service account) رو پیدا و کپی کنید، سپس به Google Sheet مورد نظرتون برید و اون Sheet رو با این ربات اشتراک بذارین.

خواندن اطلاعات Spreadsheet

پس از تنظیم دسترسی‌ها و گواهی‌نامه‌های (credentials) لازم، حالا می‌تونیم با استفاده از پایتون به Sheet مورد نظر دسترسی داشته باشیم.

برای اینکار درواقع به رباتی که در مرحله قبل به Sheet اضافه کردیم فرمان می‌دهیم.

برای ادامه به دو پکیج پایتونی نیاز داریم:

• پکیج oauth2client برای احراز هویت توسط OAuth2.0 (اختیاری)
• پکیج gspread برای کار با Google Sheets

این پکیج‌ها با استفاده از 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(&quotvirgool_template&quot).sheet1

# Extract and print all of the values
list_of_records = sheet.get_all_records()
print(list_of_records)

حال، مقادیر زیر را در کد، مطابق اطلاعات Sheet خودتون تغییر بدید:

• فایل client_secret.json باید در کنار این فایل قرار داشته باشد. برای استفاده از آن در مکان دیگر آدرس را در خط ۵ تغییر دهید.
• مقدار virgool_template در خط ۶ را برابر نام Sheet بذارید (اگر از همین Sheet استفاده می‌کنید نیازی به تغییر نیست).
• مقدار sheet1 در همان سطر را مطابق صفحه مورد نظر خود تغییر دهید. (اختیاری)

حال کد مورد نظر را اجرا کنید:

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)

اضافه‌کردن، ویرایش و حذف اطلاعات Spreadsheet

برای نوشتن در یکی از خانه‌های جدول می‌توانیم از متد update_cell به‌صورت زیر استفاده کنیم:

# update cell using row and column coordinates
sheet.update_cell(1, 1, &quotI just wrote to a google sheet using Python, amazing!&quot)

# update cell using A1 notation
worksheet.update('B1', 'Bingo!')

به عنوان نمونه، در مثال زیر ‌مقدار پیشرفت Task3 در جدولمان را هر ثانیه ۱۰ درصد افزایش می‌دهیم:

به‌صورت مشابه، برای اضافه کردن یک سطر به جدول:

row = [&quotI'm&quot, &quotinserting&quot, &quota&quot, &quotrow&quot, &quotinto&quot, &quota,&quot, &quotgoogle sheet&quot, &quotwith&quot, &quotPython&quot]
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(&quotvirgool_template&quot).sheet1

# Extract and print all of the values
list_of_records = sheet.get_all_records()
print(list_of_records)

منابع

• ‌ویدیو معرفی Jupyter Notebooks از Andrew Ng مدرس استنفورد و موسس deeplearning.ai
• این پست از وبلاگ twilio
• مستندات gspread

این پست، قسمت سوم از چای، مجموعه‌ای در باب «چیزی که امروز یادگرفتم» است. باقی چای‌ها رو می‌تونید از اینجا مشاهده کنید و در مورد فلسفه‌ی این کار بخونید.