نحوه استفاده از Google Sheets API در Node.js

اول از همه یک مرور مختصر بر موارد استفاده آن داشته باشیم. فرض کنید ما یک صفحه گسترده در Google Sheets داریم که عمومی نیست و می‌خواهیم بتوانیم از طریق برخی فرایندهای دسته‌ای که روی دستگاه محلی یا برخی از سرورها اجرا می‌شود، به صورت برنامه‌ریزی شده بخوانیم یا اصلاح کنیم. این کاری است که اخیرا مجبور شدم با یک برنامه Node.js انجام دهم و برای درک قسمت احراز هویت آن کمی مشکل داشتم. بنابراین فکر کردم راه حل خود را به اشتراک بگذارم و امیدوارم به افرادی که با این مسئله مواجه شدند، کمک کند. ممکن است روش‌های بهتری هم برای انجام این کار وجود داشته باشد، اما من آنچه را که برای خودم بهتر بوده و راحت‌تر کار کرده به اشتراک می‌گذارم.

از آنجا که در مورد استفاده تعاملی با کاربر وجود ندارد، نمی‌خواهیم از فرایند OAuth زمانی که کاربر برای اجازه دادن به برنامه نیاز به باز کردن یک مرورگر و ورود به حساب گوگل خود دارد، استفاده کنیم. برای سناریوهایی از این دست، گوگل مفهومی به نام حساب سرویس دارد. حساب سرویس نوع خاصی از حساب گوگل است که برای نمایش یک کاربر غیر انسانی طراحی شده و نیاز به تأیید اعتبار و اجازه برای دسترسی داده‌ها در APIهای گوگل دارد.

درست مثل یک حساب عادی، حساب سرویس نیز دارای یک آدرس ایمیل است (اگرچه صندوق پستی واقعی ندارد و نمی‌توانید پیامی به ایمیل حساب سرویس ارسال کنید). مانند اینکه بتوانید یک صفحه گوگل را با استفاده از آدرس ایمیل با کاربر به اشتراک بگذارید، و این دقیقا همان کاری است که ما قصد داریم در این آموزش انجام دهیم.

در اینجا ما با استفاده از یک کاربر عادی یک صفحه گسترده در Google Sheets ایجاد خواهیم کرد و آن را با یک حساب سرویس (که ایجاد خواهیم کرد) به اشتراک می‌گذاریم و از اعتبار حساب سرویس در اسکریپت Node.js خود برای خواندن و اصلاح آن صفحه استفاده می‌کنیم.

پیش نیازها

در این آموزش فرض بر این است که شما موارد زیر را در اختیار دارید:

تجربه کار با Node.js
یک حساب گوگل
راه‌اندازی پروژه در کنسول Google developers که در آن شما دارای حق امتیاز admin باشید

مرور اجمالی مراحل کار

در اینجا لیستی از مراحلی که از طریق این آموزش دنبال خواهیم کرد وجود دارد:

صفحه گسترده‌ای را در صفحات گوگل ایجاد کنید
Google Sheets API را برای پروژه در کنسول Google developers فعال کنید
یک حساب سرویس ایجاد کنید
صفحه گسترده ایجاد شده در مرحله 1 را با حساب سرویس ایجاد شده در مرحله 3 به اشتراک بگذارید
برای دسترسی به برگه‌های گوگل ایجاد شده در مرحله 1 با استفاده از اعتبارنامه حساب سرویس، یک سرویس Node.js بنویسید
سرویسی را که در مرحله 5 نوشته شده است، تست کنید

اکنون که طرح کلی از آنچه که قرار است انجام دهیم را داریم، پس بیایید شروع کنیم.

مرحله 1: صفحه گسترده‌ای را در Google Sheets ایجاد کنید

این یکی در واقع به دستورالعمل نیاز ندارد. شما فقط باید وارد حساب گوگل خود شوید، Google Drive را باز کنید و یک Google Sheet جدید ایجاد کنید. همچنین می‌توانید برخی از داده‌های تصادفی را در آن قرار دهید. نکته‌ای که باید به آن توجه داشته باشید id صفحه است.

وقتی صفحه را در مرورگر خود باز کنید، URL چیزی شبیه به این خواهد بود:

https://docs.google.com/spreadsheets/d/1-.XXXXXXXXXXXXXXXXXXXXXSgGTwY/edit#gid=0

و در این URL، شناسه صفحه گسترده 1-XXXXXXXXXXXXXXXXXXXXXXSgGTwY است و برای هر صفحه متفاوت خواهد بود. آن را یادداشت کنید، زیرا برای دسترسی به این صفحه گسترده به این مورد در اسکریپت Node.js نیاز خواهیم داشت. برای این آموزش، در اینجا داده‌هایی است که ما در صفحه گسترده خود ذخیره کرده‌ایم:

مرحله 2: Google Sheets API را در پروژه خود در کنسول Google developers فعال کنید

برای اینکه بتوانیم از آن استفاده کنیم، باید Google Sheets API را فعال کنیم. در این آموزش فرض بر این است که شما در حال حاضر پروژه‌ای در کنسول Google developers دارید، و اگر پروژه‌ای هم ندارید می‌توانید به راحتی پروژه جدیدی ایجاد کنید. وقتی پروژه را روی این کنسول قرار دادید، داشبورد پروژه را باز کنید. در آنجا باید دکمه Enable APIs and Services را مشاهده کنید.

بر روی آن کلیک کنید و با استفاده از نوار جستجو API Google pages را جستجو کنید. پس از مشاهده آن، بر روی آن کلیک کرده و سپس Enable را بزنید.

مرحله 3: یک حساب سرویس ایجاد کنید

هنگامی که Google Sheets API را در پروژه خود فعال کنید، صفحه‌ای را مشاهده خواهید کرد که می‌توانید تنظیمات این API را پیکربندی کنید. بر روی تب Credentials در نوار کناری سمت چپ کلیک کنید. در اینجا لیستی از شناسه‌های کلاینت OAuth و حساب‌های سرویس را مشاهده خواهید کرد. اما به طور پیش فرض دفعه اول نباید وجود داشته باشد.

بر روی دکمه Create Credentials در بالا کلیک کرده و گزینه Service Account را انتخاب کنید.

نام و توضیحات حساب سرویس را وارد کنید و روی دکمه Create کلیک کنید.

در پنجره بعدی روی Continue کلیک کنید.

در صفحه بعد، گزینه‌ای برای ایجاد یک کلید دریافت می‌کنید. این یک گام مهم است. بر روی دکمه Create Key کلیک کنید و JSON را به عنوان قالب انتخاب کنید. با این کار فایل JSON را در سیستم خود بارگیری خواهید کرد.

برای این آموزش من نام فایل را تغییر داده و آن را به عنوان service_account_credentials.json در سیستم خودم ذخیره کردم.

آن را در جایی امن نگهداری کنید. این فایل اصلی حاوی اطلاعات اعتبار حساب سرویس است که برای دسترسی به صفحه گسترده خود از Google Sheets در اسکریپت Node.js نیاز داریم.

هنگامی که همه این مراحل را دنبال کردید، باید حساب سرویس ایجاد شده جدید را در صفحه Credentials مشاهده کنید.

آدرس ایمیل حساب سرویس را یادداشت کنید. در ادامه صفحه گسترده خود را با این حساب به اشتراک خواهیم گذاشت.

مرحله 4: صفحه گسترده ایجاد شده در مرحله 1 را با حساب سرویس ایجاد شده در مرحله 3 به اشتراک بگذارید

اکنون که یک حساب سرویس داریم، باید صفحه گسترده خود را با آن به اشتراک بگذاریم. این دقیقا مانند اشتراک صفحه گسترده با هر حساب کاربری عادی است. صفحه گسترده را در مرورگر خود باز کنید و بر روی دکمه Share در گوشه بالا سمت راست کلیک کنید. با این کار در جایی که باید آدرس ایمیل حساب سرویس را وارد کنید، یک modal باز می‌شود. تیک کادر مربوط به Notify people را بردارید، زیرا با این کار ایمیل ارسال می‌شود و از آنجا که حساب سرویس فاقد هرگونه صندوق پستی است، به شما اعلان عدم موفقیت تحویل ایمیل را می‌دهد.

برای به اشتراک گذاشتن صفحه گسترده با حساب سرویس، بر روی دکمه OK کلیک کنید.

با این کار تمام مراحل پیکربندی کامل می‌شود. اکنون می‌توانیم به قسمت سرگرم کننده کار برویم!

مرحله 5: برای دسترسی به صفحه گوگل با استفاده از اعتبارنامه حساب سرویس، یک سرویس Node.js بنویسید

ما اسکریپت خود را به عنوان سرویس ایجاد خواهیم کرد که می‌تواند به عنوان بخشی از یک پروژه بزرگتر مورد استفاده قرار گیرد. سپس آن را googleSheetsService.js نامگذاری می‌کنیم. این APIهای زیر را نشان می‌دهد:

getAuthToken
getSpreadSheet
getSpreadSheetValues

تابع getAuthToken جایی است که ما احراز هویت را کنترل خواهیم کرد و توکن را برمی‌گرداند. سپس ما از آن توکن استفاده می‌کنیم و آن را به متد‌های دیگر منتقل خواهیم کرد.

ما نوشتن داده‌ها را در صفحه گسترده پوشش نخواهیم داد، اما هنگامی که ایده اصلی نحوه استفاده از API را به دست آورید، گسترش سرویس برای افزودن توابع بیشتر پشتیبانی شده توسط API صفحه‌های گوگل آسان خواهد بود.

ما از ماژول npm googleapis استفاده خواهیم کرد. بنابراین بیایید با ایجاد یک دایرکتوری برای این پروژه شروع کنیم. اسمش را هم google-sheets-demo بگذاریم.

cd $HOME
mkdir google-sheets-demo
cd google-sheets-demo

فایل service_account_credentials.json که در مرحله 3 ایجاد کردیم را در این دایرکتوری کپی کنید (google-sheets-demo) و فایل جدید googleSheetsService.js را ایجاد کنید.

خطوط زیر را در فایل جایگذاری کنید:

// googleSheetsService.js
const { google } = require('googleapis')
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
async function getAuthToken() {
  const auth = new google.auth.GoogleAuth({
    scopes: SCOPES
  });
  const authToken = await auth.getClient();
  return authToken;
}

module.exports = {
  getAuthToken,
}

در حال حاضر سرویس ما فقط یک تابع دارد که توکن auth را برمی‌گرداند. به زودی تابع دیگر getSpreadSheet را اضافه خواهیم کرد. ابتدا اجازه دهید ببینیم تابع ما چه کار می‌کند.

سپس به ماژول npm googleapis نیاز داریم. بعد SCOPES را تعریف می‌کنیم. هنگامی که با استفاده از google API توکن auth ایجاد می‌کنیم، مفهومی از scopeها وجود دارد که سطح دسترسی کلاینتمان را تعیین می‌کند.

برای خواندن و ویرایش صفحات گسترده، باید به اسکوپ https://www.googleapis.com/auth/spreadsheets دسترسی داشته باشیم.

به همین ترتیب اگر مجبور بودیم فقط به صفحه گسترده دسترسی داشته باشیم، از اسکوپ https://www.googleapis.com/auth/spreadsheets.readonly استفاده می‌کردیم.

در داخل تابع getAuthToken، ما در حال فراخوانی سازنده new google.auth.GoogleAuth هستیم که در محدوده شی آرگومان‌ها منتقل می‌شود.

این تابع انتظار دارد دو متغیر محیطی در دسترس باشد، یکی GCLOUD_PROJECT که شناسه پروژه کنسول Google developers شما است و دیگری GOOGLE_APPLICATION_CREDENTIALS که نشان دهنده مسیر فایل حاوی اعتبارنامه حساب سرویس است.

ما باید این متغیرهای محیط را از خط فرمان تنظیم کنیم. برای دریافت شناسه پروژه می‌توانید هنگام باز کردن پروژه در مرورگر وب، آن را از آدرس اینترنتی دریافت کنید. می‌بایست شبیه زیر باشد:

https://console.cloud.google.com/home/dashboard?project={project ID}

و GOOGLE_APPLICATION_CREDENTIALS باید حاوی مسیر فایل service_account_credentials.json باشد. بنابراین به ترمینال بروید و از دایرکتوری google-sheets-demo دستورات زیر را برای تنظیم این متغیرهای محیط اجرا کنید:

export GCLOUD_PROJECT={project ID of your google project}
export GOOGLE_APPLICATION_CREDENTIALS=./service_account_credentials.json

شما باید مطمئن شوید که فایل اعتبارنامه را در دایرکتوری فعلی کپی کرده‌اید.

اکنون دو تابع دیگر به سرویس خود اضافه خواهیم کرد:

getSpreadSheet
getSpreadSheetValues

مورد اول metadata مربوط به صفحه گسترده را برمی‌گرداند، در حالی که مورد دوم داده‌های داخل صفحه گسترده را برمی‌گرداند. فایل googleSheetsService.js اصلاح شده ما باید به این شکل باشد:

// googleSheetsService.js

const { google } = require('googleapis');
const sheets = google.sheets('v4');

const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];

async function getAuthToken() {
  const auth = new google.auth.GoogleAuth({
    scopes: SCOPES
  });
  const authToken = await auth.getClient();
  return authToken;
}

async function getSpreadSheet({spreadsheetId, auth}) {
  const res = await sheets.spreadsheets.get({
    spreadsheetId,
    auth,
  });
  return res;
}

async function getSpreadSheetValues({spreadsheetId, auth, sheetName}) {
  const res = await sheets.spreadsheets.values.get({
    spreadsheetId,
    auth,
    range: sheetName
  });
  return res;
}


module.exports = {
  getAuthToken,
  getSpreadSheet,
  getSpreadSheetValues
}

در بالا یک خط اضافه کرده‌ایم.

const sheets = google.sheets('v4');

این برای استفاده از API صفحات است. سپس دو تابع جدید getSpreadSheet و getSpreadSheetValues اضافه کرده‌ایم. برای دیدن همه نقاط پایانی API پشتیبانی شده برای Google Sheets API، لینک زیر را بررسی کنید.

https://developers.google.com/sheets/api/reference/rest

برای این نسخه دمو، فقط از دو مورد استفاده می‌کنیم. تابع getSpreadSheet انتظار دارد که توکن auth و صفحه گسترده به عنوان پارامترهای آن ارائه شود و getSpreadSheetValues انتظار دارد که یک پارامتر اضافی وجود داشته باشد، یعنی sheetName که از آن داده‌ها را می‌آورد.

به طور پیش فرض، یک صفحه گسترده فقط شامل یک صفحه است و به عنوان Sheet1 نامگذاری شده است. در آخر ما توابع تازه اضافه شده را از طریق module.exports اکسپورت می‌کنیم.

با این کار googleSheetsService کامل می‌شود. اگر نیاز به پشتیبانی از توابع API بیشتری دارید، می‌توانید با استفاده از لینک بالا مرجع را بررسی کنید، توابع مربوطه را در این سرویس اضافه کرده و با استفاده از module.exports اکسپورت کنید.

مصرف کنندگان این سرویس ابتدا باید تابع ()getAuthToken را فراخوانی کنند تا توکن auth را بدست آورند و سپس آن را به توابع بعدی مانندgetSpreadSheet() و ()getSpreadSheetValues منتقل کنند.

اکنون که سرویس خود را آماده کردیم، فقط باید آن را تست کنیم تا مطمئن شویم که به درستی کار می‌کند.

مرحله 6: سرویس را تست کنید

اکنون سرویس خود را آماده کرده‌ایم. اما آیا به خوبی کار می‌کند؟ بیایید بررسی کنیم.

به طور معمول برای اجرای تست‌های واحد از یک فریمورک تست استفاده می‌شود. اما برای ساده نگه داشتن این آموزش می‌خواهیم یک اسکریپت ساده Node.js بنویسیم. از دایرکتوری پروژه یک فایل جدید به نام test.js ایجاد کرده و محتوای زیر را در آن کپی کنید:

const {
  getAuthToken,
  getSpreadSheet,
  getSpreadSheetValues
} = require('./googleSheetsService.js');

const spreadsheetId = process.argv[2];
const sheetName = process.argv[3];

async function testGetSpreadSheet() {
  try {
    const auth = await getAuthToken();
    const response = await getSpreadSheet({
      spreadsheetId,
      auth
    })
    console.log('output for getSpreadSheet', JSON.stringify(response.data, null, 2));
  } catch(error) {
    console.log(error.message, error.stack);
  }
}

async function testGetSpreadSheetValues() {
  try {
    const auth = await getAuthToken();
    const response = await getSpreadSheetValues({
      spreadsheetId,
      sheetName,
      auth
    })
    console.log('output for getSpreadSheetValues', JSON.stringify(response.data, null, 2));
  } catch(error) {
    console.log(error.message, error.stack);
  }
}

function main() {
  testGetSpreadSheet();
  testGetSpreadSheetValues();
}

main()

این فایل شامل دو تابع تست و یک تابع اصلی است که آن دو تابع تست را فراخوانی می‌کند. در پایین فایل، ما تابع اصلی را اجرا می‌کنیم. این اسکریپت انتظار دارد دو آرگومان خط فرمان داشته باشد:

spreadsheetId (این شناسه‌ای است که از مرحله 1 دریافت کردیم)
sheetName (این نام صفحه کاری است که می‌خواهید مقادیر آن را ببینید. وقتی صفحه گسترده جدید ایجاد می‌کنید، به طور پیش فرض Sheet1 است)

همچنین مطمئن شوید که متغیرهای env GCLOUD_PROJECT و GOOGLE_APPLICATION_CREDENTIALS به درستی تنظیم شده‌اند.

اکنون از ترمینال این اسکریپت را اجرا کنید:

node test.js <your google sheet's spreadsheet id> <sheet name of the worksheet>

اگر همه مراحل را به درستی دنبال کرده باشید، باید خروجی مانند زیر را ببینید:

output for getSpreadSheet {
  "spreadsheetId": "1-jG5jSgGTwXXXXXXXXXXXXXXXXXXY",
  "properties": {
    "title": "test-sheet",
    "locale": "en_US",
    "autoRecalc": "ON_CHANGE",
    "timeZone": "Asia/Calcutta",
    "defaultFormat": {
      "backgroundColor": {
        "red": 1,
        "green": 1,
        "blue": 1
      },
      "padding": {
        "top": 2,
        "right": 3,
        "bottom": 2,
        "left": 3
      },
      "verticalAlignment": "BOTTOM",
      "wrapStrategy": "OVERFLOW_CELL",
      "textFormat": {
        "foregroundColor": {},
        "fontFamily": "arial,sans,sans-serif",
        "fontSize": 10,
        "bold": false,
        "italic": false,
        "strikethrough": false,
        "underline": false
      }
    }
  },
  "sheets": [
    {
      "properties": {
        "sheetId": 0,
        "title": "Sheet1",
        "index": 0,
        "sheetType": "GRID",
        "gridProperties": {
          "rowCount": 1000,
          "columnCount": 26
        }
      }
    }
  ],
  "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/1-jG5jSgGTwXXXXXXXXXXXXXXXXXXY/edit"
}
output for getSpreadSheetValues {
  "range": "Sheet1!A1:Z1000",
  "majorDimension": "ROWS",
  "values": [
    [
      "Name",
      "Country",
      "Age"
    ],
    [
      "John",
      "England",
      "30"
    ],
    [
      "Jane",
      "Scotland",
      "23"
    ],
    [
      "Bob",
      "USA",
      "45"
    ],
    [
      "Alice",
      "India",
      "33"
    ]
  ]
}

اگر خطایی وجود داشته باشد به این معنی است که شما تمام مراحل را به درستی دنبال نکرده‌اید. در این آموزش نسخه ماژول googleapis npm 43.0.0 بود. اگر از نسخه قدیمی ماژول استفاده می‌کنید ممکن است با مشکل مواجه شوید. مطمئن شوید که spreadsheetId و sheetname درست است و متغیرهای محیط به درستی تنظیم شده‌اند. اگر باز هم خطایی دریافت کردید، باید پیام خطا و کد آن را بررسی کنید تا ببینید چه چیزی ممکن است باعث بروز این مشکل شده باشد.

منابع و مآخذ

مستندات Google API Node.js client
https://github.com/googleapis/google-api-nodejs-client
مرجع رسمی Google Sheet API
https://developers.google.com/sheets/api/reference/rest

قطعا توصیه می‌کنیم این منابع (خصوصا مرجع رسمی Google Sheets API) را بررسی کنید تا درک عمیق تری از API صفحات و نحوه استفاده از کلاینت Node.js داشته باشید.

امیدواریم که این آموزش برای شما مفید واقع شده باشد.

منبع