README

• C: 创建
• R: 读取
• U: 更新
• D: 删除

使用方法

此库假设所有被操作的范围的第一行是列标题。

在一个新的Google Sheets文档中，创建一个名为 People 的工作表，包含以下内容

id	name	country	city
1	Julie	France	Paris
2	Julien	France	Montpellier
3	Marek	Slovakia	Košice
4	Tobias	Austria	Vienna
5	Agnieszka	Poland	Sosnowiec
6	Giorgi	Georgia	Kutaisi
7	John	USA	Los Angeles
8	Ivan	Russia	Norilsk
9	Marina	Russia	Moscow
10	Andreas	Germany	Berlin

设置

require('vendor/autoload.php');

use Mutusen\GoogleSheetsCRUD\GoogleSheetsCRUD;

$gs = new GoogleSheetsCRUD(
    'sheet id', // Found in the URL of the Google Sheet: https://docs.google.com/spreadsheets/d/.../edit
    'service account' // JSON object given by the Google Sheets API
);

读取整个范围

/*
 * Returns:
 * Array
    (
        [0] => Array
            (
                [id] => 1
                [name] => Julie
                [country] => France
                [city] => Paris
            )
    
        [1] => Array
            (
                [id] => 2
                [name] => Julien
                [country] => France
                [city] => Montpellier
            )
        ...
    )
 */
$data = $gs->readAll('People');

// You can also specify a range in the sheet (works for all other functions except appendRow())
$data = $gs->readAll('People!B1:D6');

获取特定行

/*
 * Returns:
 * Array
    (
        [id] => 1
        [name] => Julie
        [country] => France
        [city] => Paris
    )
 * If there are several matches, it stops at the first one
 */
$data = $gs->getRowWhere('People', 'id', 1);

获取特定行

/*
 * Returns:
 * Array
    (
        [0] => Array
            (
                [id] => 1
                [name] => Julie
                [country] => France
                [city] => Paris
            )
    
        [1] => Array
            (
                [id] => 2
                [name] => Julien
                [country] => France
                [city] => Montpellier
            )
    
    )
 */
$data = $gs->getRowsWhere('People', 'country', 'France');

插入行

// You cannot use a range after the name of the sheet
// The values have to be in the right order
$gs->appendRow('People', [11, 'Maria', 'Italy', 'Milan']);

插入多行

// You cannot use a range after the name of the sheet
// The values have to be in the right order
$rows = [
    [11, 'Maria', 'Italy', 'Milan'],
    [12, 'Oleh', 'Ukraine', 'Lviv']
];
$gs->appendRows('People', $rows);

更新行

// You can update multiple values in a single row
$gs->updateFieldsWhere('People', 'id', 11, [
    'country' => 'Spain',
    'city' => 'Madrid',
]);

// If you use a search criterion that matches several rows, they all will be updated
$gs->updateFieldsWhere('People', 'country', 'France', [
    'country' => 'Belgium',
    'city' => 'Brussels',
]);

更新多行

连续多次调用 updateFieldsWhere() 是低效的，因为它会每次都进行API调用，因此请使用以下方法

// If you want to update a sheet according to several criteria (e.g. several ids)
use Mutusen\GoogleSheetsCRUD\GSCMultipleLinesUpdate;

$query = new GSCMultipleLinesUpdate($gs, 'People');
$query->updateWhere('id', 9, ['name' => 'Irina']);
$query->updateWhere('id', 10, ['name' => 'Jonas']);
$query->updateWhere('id', 11, ['name' => 'Ines']);
$query->execute();

删除行

$data = $gs->deleteRowWhere('People', 'id', 11);

选项

值渲染选项

值渲染选项（请参阅 文档）可以在构造函数中或之后定义

$gs = new GoogleSheetsCRUD(
    'sheet id',
    'service account',
    'UNFORMATTED_VALUE'
);
$gs->setValueRenderOption('UNFORMATTED_VALUE');

默认值是 FORMATTED_VALUE，它的缺点是从工作表中获取所有数据时都作为字符串处理。

日期时间渲染选项

日期时间渲染选项（请参阅 文档）可以在之后定义

$gs->setDateTimeRenderOption('SERIAL_NUMBER');

默认值是 FORMATTED_STRING。