Як інтегрувати API Google Sheets з PHP

Останнє оновлення Жов 17, 2021

Зміст

Зареєструйте програму та створіть облікові дані

Базова конфігурація

Створити маркер доступу

Створіть електронну таблицю за допомогою API Таблиць та PHP

Запишіть дані в електронну таблицю

Додайте дані до аркуша Google

Зчитування даних з таблиці Google

Статті, пов’язані з цим

Раніше я працював над проектом, де ми хотіли динамічно записувати дані в електронну таблицю Google. Для цього ми взаємодіяли з API таблиць. Я знайшов це цікавою темою для публікації в блозі. Отже, у цій статті ми вивчаємо, як інтегрувати API Google Sheets за допомогою PHP. Ми збираємось виконати операції створення, запису, додавання та читання електронних таблиць за допомогою API.

Google Spreadsheet – це безкоштовний веб-пакет програмного забезпечення, який підтримується Google. Можна використовувати електронну таблицю для своїх цілей у службі Google Drive.

Що стосується веб-сайтів, ви можете використовувати аркуші Google для різних цілей. Можна зберігати контактну інформацію свого користувача (телефон та електронну адресу), платіжні реквізити, реєстрації подій, дії та багато іншого в аркуші Google. На веб-сайті вам може знадобитися автоматизована система, яка виконує всі операції читання, запису в електронних таблицях, коли спрацьовує певна дія. Це можна зробити за допомогою API Sheets.

Зареєструйте програму та створіть облікові дані

Я збираюся використовувати OAuth для взаємодії з API. OAuth – це більш безпечний і рекомендований спосіб виконання операцій API. Виконайте наведені нижче кроки, необхідні для інтеграції OAuth.

Перейдіть на Консоль розробника Google https://console.developers.google.com
Створіть новий проект. Ви також можете вибрати існуючий проект.
Дайте назву своєму проекту. Google Console створить для нього унікальний ідентифікатор проекту.
Ваш проект з’явиться у верхній частині лівої бічної панелі.
Клацніть на Бібліотека. Ви побачите список API Google.
Увімкніть API Google Sheets.
Клацніть на Повноваження. Виберіть ідентифікатор клієнта Oauth у розділі Створення облікових даних. Виберіть перемикач для веб-програми.
Дайте ім’я. У розділі Авторизовані джерела JavaScript введіть URL-адресу вашого домену. В авторизованих URI-адресах переспрямування додайте посилання на URL-адресу переспрямування. У моєму випадку я передав URL-адресу http: //localhost/google-sheets-api/callback.php
Клацніть на кнопку Створити. У спливаючому вікні ви отримаєте ідентифікатор клієнта та секрет клієнта. Скопіюйте ці деталі. Це нам знадобиться за мить.

Базова конфігурація

Як вже було сказано, ми будемо використовувати OAuth для виконання операцій API. Для цього потрібно згенерувати маркер доступу, який виконує роль ідентифікатора вашого облікового запису. Використовуючи цей маркер доступу, API Google перевіряє, чи є вхідний запит дійсним та авторизованим.

Токен доступу має коротке життя. Вони скоро закінчуються. А після закінчення терміну дії ми не можемо робити виклики API, оскільки Google трактує запит із простроченим маркером як несанкціонований запит. Щоб вирішити цю проблему, ми використовуємо ‘refresh_token’ для регенерації маркера доступу у фоновому режимі. Ось як працює OAuth.

Почнемо будувати потік OAuth. Створіть composer.jsonфайл і додайте в нього рядки нижче.

{
    "require": {
        "google/apiclient": "2.0",
        "hybridauth/hybridauth": "~3.0"
    }
}

Далі запустіть команду нижче для встановлення цих бібліотек.

composer install

Коли ми генеруємо маркер доступу, його потрібно зберігати в базі даних. Зберігаючи його, ви можете отримати маркер у будь-який час і здійснити виклики API. Запустіть наведений нижче запит, щоб створити таблицю в базі даних.

CREATE TABLE `token` ( `id` int(11) NOT NULL AUTO_INCREMENT,
 `access_token` text NOT NULL,
 PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Тепер створіть клас DB, який буде взаємодіяти з базою даних та отримувати, зберігати, оновлювати інформацію про маркери в таблиці.

class-db.php

<?php
class DB {
    private $dbHost     = "DB_HOST";
    private $dbUsername = "DB_USERNAME";
    private $dbPassword = "DB_PASSWORD";
    private $dbName     = "DB_NAME";
      
    public function __construct(){
        if(!isset($this->db)){
            // Connect to the database
            $conn = new mysqli($this->dbHost, $this->dbUsername, $this->dbPassword, $this->dbName);
            if($conn->connect_error){
                die("Failed to connect with MySQL: ". $conn->connect_error);
            }else{
                $this->db = $conn;
            }
        }
    }
  
    public function is_table_empty() {
        $result = $this->db->query("SELECT id FROM token");
        if($result->num_rows) {
            return false;
        }
  
        return true;
    }
  
    public function get_access_token() {
        $sql = $this->db->query("SELECT access_token FROM token");
        $result = $sql->fetch_assoc();
        return json_decode($result['access_token']);
    }
  
    public function get_refersh_token() {
        $result = $this->get_access_token();
        return $result->refresh_token;
    }
  
    public function update_access_token($token) {
        if($this->is_table_empty()) {
            $this->db->query("INSERT INTO token(access_token) VALUES('$token')");
        } else {
            $this->db->query("UPDATE token SET access_token = '$token' WHERE id = (SELECT id FROM token)");
        }
    }
}

Замініть заповнювачі у наведеному вище коді вашими справжніми обліковими даними бази даних. Тут я припускаю, що ви хочете використовувати один обліковий запис Google. Якщо ви маєте намір використовувати кілька облікових записів, змініть запити відповідно до ваших вимог.

Створити маркер доступу

Ми закінчили зі створенням таблиці та встановленням бібліотек. Наступне, що потрібно зробити, це авторизувати обліковий запис Google і зберегти маркер доступу в таблиці ‘token’.

Створіть config.phpфайл і напишіть конфігурацію відповідно до вказівок бібліотеки HybridAuth.

config.php

<?php
require_once 'vendor/autoload.php';
require_once 'class-db.php';
  
define('GOOGLE_CLIENT_ID', 'PASTE_CLIENT_ID_HERE');
define('GOOGLE_CLIENT_SECRET', 'PASTE_CLIENT_SECRET_HERE');
  
$config = [
    'callback' => 'YOUR_DOMAIN_URL/callback.php',
    'keys'     => [
                    'id' => GOOGLE_CLIENT_ID,
                    'secret' => GOOGLE_CLIENT_SECRET
                ],
    'scope'    => 'https://www.googleapis.com/auth/spreadsheets',
    'authorize_url_parameters' => [
            'approval_prompt' => 'force', // to pass only when you need to acquire a new refresh token.
            'access_type' => 'offline'
    ]
];
  
$adapter = new HybridauthProviderGoogle( $config );

Обов’язково визначте значення констант GOOGLE_CLIENT_ID, GOOGLE_CLIENT_SECRET. Також передайте URL-адресу зворотного виклику у наведеному вище коді. Коли користувач завершить авторизацію, він перенаправить на URL зворотного дзвінка.

Використовуючи callback.phpфайл, ми отримаємо деталі маркера доступу та збережемо їх у базі даних наступним чином.

callback.php

<?php
require_once 'config.php';
  
try {
    $adapter->authenticate();
    $token = $adapter->getAccessToken();
    $db = new DB();
    if($db->is_table_empty()) {
        $db->update_access_token(json_encode($token));
        echo "Access token inserted successfully.";
    }
}
catch( Exception $e ){
    echo $e->getMessage() ;
}

На цьому етапі перейдіть до браузера та запустіть YOUR_DOMAIN_URL / callback.php, він перенаправить вас до облікового запису Google. Завершіть процес авторизації. Після завершення процесу перевірте таблицю "маркер". Він повинен зберігати інформацію про маркер.

Створіть електронну таблицю за допомогою API Таблиць та PHP

Тепер ви зберегли маркер доступу у своїй таблиці. Це означає, що ви можете продовжувати подальші операції на аркушах Google вашого облікового запису. Почнемо спочатку зі створення електронної таблиці за допомогою API Sheets.

create-sheet.php

<?php
require_once 'config.php';
 
create_spreadsheet();
 
function create_spreadsheet() {
 
    $client = new Google_Client();
 
    $db = new DB();
 
    $arr_token = (array) $db->get_access_token();
    $accessToken = array(
        'access_token' => $arr_token['access_token'],
        'expires_in' => $arr_token['expires_in'],
    );
 
    $client->setAccessToken($accessToken);
 
    $service = new Google_Service_Sheets($client);
 
    try {
        $spreadsheet = new Google_Service_Sheets_Spreadsheet([
            'properties' => [
                'title' => 'API Sheet'
            ]
        ]);
        $spreadsheet = $service->spreadsheets->create($spreadsheet, [
            'fields' => 'spreadsheetId'
        ]);
        printf("Spreadsheet ID: %sn", $spreadsheet->spreadsheetId);
    } catch(Exception $e) {
        if( 401 == $e->getCode()) {
            $refresh_token = $db->get_refersh_token();
 
            $client = new GuzzleHttpClient(['base_uri' => 'https://accounts.google.com']);
 
            $response = $client->request('POST', '/o/oauth2/token', [
                'form_params' => [
                    "grant_type" => "refresh_token",
                    "refresh_token" => $refresh_token,
                    "client_id" => GOOGLE_CLIENT_ID,
                    "client_secret" => GOOGLE_CLIENT_SECRET,
                ],
            ]);
 
            $data = (array) json_decode($response->getBody());
            $data['refresh_token'] = $refresh_token;
 
            $db->update_access_token(json_encode($data));
 
            create_spreadsheet();
        } else {
            echo $e->getMessage(); //print the error just in case your video is not uploaded.
        }
    }
}

Цей код спочатку отримує деталі маркера з БД. За допомогою цього маркера він викликає службу аркушів Google і створює електронну таблицю. Я надрукував ідентифікатор створеної електронної таблиці.

Якщо термін дії маркера доступу закінчився, код переходить до блоку catch. У блоці catch він відновлює маркер доступу, оновлює його в базі даних і продовжує роботу API.

Той самий код із незначними змінами буде використаний для інших операцій з електронними таблицями.

Запишіть дані в електронну таблицю

Для виконання операцій запису потрібно передати діапазон комірок, які потрібно записати. Наприклад, ви хочете додати заголовки Ім’я та Електронна пошта вгорі. У цьому випадку діапазон стає "A1: B1". Це означає, що ми будемо писати заголовки Ім’я та Електронна пошта відповідно в клітинках А1 та В1.

<?php
require_once 'config.php';
 
write_to_sheet('SPREADSHEET_ID');
 
function write_to_sheet($spreadsheetId = '') {
  
    $client = new Google_Client();
  
    $db = new DB();
  
    $arr_token = (array) $db->get_access_token();
    $accessToken = array(
        'access_token' => $arr_token['access_token'],
        'expires_in' => $arr_token['expires_in'],
    );
  
    $client->setAccessToken($accessToken);
  
    $service = new Google_Service_Sheets($client);
  
    try {
        $range = 'A1:B1';
        $values = [
            [
                'Name',
                'Email',
            ],
        ];
        $body = new Google_Service_Sheets_ValueRange([
            'values' => $values
        ]);
        $params = [
            'valueInputOption' => 'USER_ENTERED'
        ];
        $result = $service->spreadsheets_values->update($spreadsheetId, $range, $body, $params);
        printf("%d cells updated.", $result->getUpdatedCells());
    } catch(Exception $e) {
        if( 401 == $e->getCode()) {
            $refresh_token = $db->get_refersh_token();
  
            $client = new GuzzleHttpClient(['base_uri' => 'https://accounts.google.com']);
  
            $response = $client->request('POST', '/o/oauth2/token', [
                'form_params' => [
                    "grant_type" => "refresh_token",
                    "refresh_token" => $refresh_token,
                    "client_id" => GOOGLE_CLIENT_ID,
                    "client_secret" => GOOGLE_CLIENT_SECRET,
                ],
            ]);
  
            $data = (array) json_decode($response->getBody());
            $data['refresh_token'] = $refresh_token;
  
            $db->update_access_token(json_encode($data));
  
            write_to_sheet($spreadsheetId);
        } else {
            echo $e->getMessage(); //print the error just in case your video is not uploaded.
        }
    }
}

Тут я передав ідентифікатор електронної таблиці методу. Ви можете змінити цю функцію та передати додаткові параметри. У попередньому коді ми повернули ідентифікатор електронної таблиці, який може бути використаний у наведеному вище коді. Крім того, ви можете отримати ідентифікатор електронної таблиці з URL-адреси вашого аркуша. Дивіться знімок екрана нижче.

Рядок, що з’являється між "d" та "edit" – це ідентифікатор електронної таблиці Google.

Додайте дані до аркуша Google

Якщо ви хочете зберегти конкретну інформацію в аркуші Google, вам потрібно додати її до існуючої електронної таблиці. Можуть бути випадки, коли ви хочете додати один або кілька рядків за один раз. Користувач може передавати один або кілька елементів масиву та додавати дані до аркуша, як показано нижче.

<?php
require_once 'config.php';
 
append_to_sheet('SPREADSHEET_ID');
  
function append_to_sheet($spreadsheetId = '') {
  
    $client = new Google_Client();
  
    $db = new DB();
  
    $arr_token = (array) $db->get_access_token();
    $accessToken = array(
        'access_token' => $arr_token['access_token'],
        'expires_in' => $arr_token['expires_in'],
    );
  
    $client->setAccessToken($accessToken);
  
    $service = new Google_Service_Sheets($client);
  
    try {
        $range = 'A1:B1';
        $values = [
            [
                'John Doe',
                '[email protected]',
            ],
            [
                'Jack Waugh',
                '[email protected]',
            ],
            // Additional rows ...
        ];
        $body = new Google_Service_Sheets_ValueRange([
            'values' => $values
        ]);
        $params = [
            'valueInputOption' => 'USER_ENTERED'
        ];
        $result = $service->spreadsheets_values->append($spreadsheetId, $range, $body, $params);
        printf("%d cells appended.", $result->getUpdates()->getUpdatedCells());
    } catch(Exception $e) {
        if( 401 == $e->getCode()) {
            $refresh_token = $db->get_refersh_token();
  
            $client = new GuzzleHttpClient(['base_uri' => 'https://accounts.google.com']);
  
            $response = $client->request('POST', '/o/oauth2/token', [
                'form_params' => [
                    "grant_type" => "refresh_token",
                    "refresh_token" => $refresh_token,
                    "client_id" => GOOGLE_CLIENT_ID,
                    "client_secret" => GOOGLE_CLIENT_SECRET,
                ],
            ]);
  
            $data = (array) json_decode($response->getBody());
            $data['refresh_token'] = $refresh_token;
  
            $db->update_access_token(json_encode($data));
  
            append_to_sheet($spreadsheetId);
        } else {
            echo $e->getMessage(); //print the error just in case your video is not uploaded.
        }
    }
}

Я використав діапазон ‘A1: B1’ і передав 2 окремі елементи масиву. API Sheets додає ці деталі після даних таблиці. Зверніть увагу, хоча ми передаємо в діапазоні значення "A1: B1", воно не замінить значення, вже записані в цих клітинках. Натомість він записує дані в наступні доступні комірки.

Зчитування даних з таблиці Google

Ми написали код для створення електронної таблиці, запису в електронну таблицю та додавання даних до електронної таблиці. Нарешті, давайте подивимось, як читати дані з аркуша Google.

<?php
require_once 'config.php';
 
read_sheet('SPREADSHEET_ID');
  
function read_sheet($spreadsheetId = '') {
  
    $client = new Google_Client();
  
    $db = new DB();
  
    $arr_token = (array) $db->get_access_token();
    $accessToken = array(
        'access_token' => $arr_token['access_token'],
        'expires_in' => $arr_token['expires_in'],
    );
  
    $client->setAccessToken($accessToken);
  
    $service = new Google_Service_Sheets($client);
  
    try {
        $range = 'A:B';
        $response = $service->spreadsheets_values->get($spreadsheetId, $range);
        $values = $response->getValues();
 
        if (empty($values)) {
            print "No data found.n";
        } else {
            print "Name, Email:n";
            foreach ($values as $row) {
                // Print columns A and E, which correspond to indices 0 and 4.
                printf("%s, %sn", $row[0], $row[1]);
            }
        }
    } catch(Exception $e) {
        if( 401 == $e->getCode()) {
            $refresh_token = $db->get_refersh_token();
  
            $client = new GuzzleHttpClient(['base_uri' => 'https://accounts.google.com']);
  
            $response = $client->request('POST', '/o/oauth2/token', [
                'form_params' => [
                    "grant_type" => "refresh_token",
                    "refresh_token" => $refresh_token,
                    "client_id" => GOOGLE_CLIENT_ID,
                    "client_secret" => GOOGLE_CLIENT_SECRET,
                ],
            ]);
  
            $data = (array) json_decode($response->getBody());
            $data['refresh_token'] = $refresh_token;
  
            $db->update_access_token(json_encode($data));
  
            read_sheet($spreadsheetId);
        } else {
            echo $e->getMessage(); //print the error just in case your video is not uploaded.
        }
    }
}

Сподіваюся, ви розумієте, як інтегрувати API Google Sheets за допомогою PHP. Я хотів би почути ваші думки та пропозиції у розділі коментарів нижче.

Статті, пов’язані з цим

Джерело запису: artisansweb.net