Google Sheets APIでシートの最終行を取得(Nodejs)

18342 ワード
SheetsAPI GoogleSpreadSheet GoogleSpreadSheet テキストリンク

結論

GASのgetLastRowのようなメソッドは無いので、
spreadsheets.values.append で空の行を追加し戻り値から最終行を取得すればOKです。

セットアップ

① GCP で Sheets API を有効化
② Credentials ページで OAuth Client ID を作成（Application Typeは Desktop app を選択）し、client_secret ファイルを credentials.json という名前で保存
③ google api パッケージをインストール

npm install googleapis@39 --save

コード

下記のファイルをコピペして、credentials.jsonと同じ場所に保存し実行してください。
※SPREADSHEET_ID と SHEET_NAME はご自身のものに変更してください）
※認証部分のコードは Google公式のquickstart と全く同じです。

getLastRow.js
const fs = require('fs');
const readline = require('readline');
const { google } = require('googleapis');

//最終行を取得したいシートの名前とスプレッドシートIDを指定=====

const SPREADSHEET_ID = '*************************';
const SHEET_NAME = 'シート1';

//====================================================

const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];
const CLIENT_SECRET_PATH = 'credentials.json';
const TOKEN_PATH = 'token.json'; // トークンファイル名（トークンファイルは初めてこのファイルを実行する際に自動生成される）


fs.readFile(CLIENT_SECRET_PATH, (err, content) => {
    if (err) return console.log('Error loading client secret file:', err);
    authorize(JSON.parse(content), getLastRow);
});

/**
認証関数　既に認証されていればcallbackのみ実行
 */
function authorize(credentials, callback) {
    const { client_secret, client_id, redirect_uris } = credentials.installed;
    const oAuth2Client = new google.auth.OAuth2(
        client_id, client_secret, redirect_uris[0]);

    // Check if we have previously stored a token.
    fs.readFile(TOKEN_PATH, (err, token) => {
        if (err) return getNewToken(oAuth2Client, callback);
        oAuth2Client.setCredentials(JSON.parse(token));
        callback(oAuth2Client);
    });
}

/**
トークン生成関数　既にtokenファイルがあればcallbackのみ実行
 */
function getNewToken(oAuth2Client, callback) {
    const authUrl = oAuth2Client.generateAuthUrl({
        access_type: 'offline',
        scope: SCOPES,
    });
    console.log('Authorize this app by visiting this url:', authUrl);
    const rl = readline.createInterface({
        input: process.stdin,
        output: process.stdout,
    });
    rl.question('Enter the code from that page here: ', (code) => {
        rl.close();
        oAuth2Client.getToken(code, (err, token) => {
            if (err) return console.error('Error while trying to retrieve access token', err);
            oAuth2Client.setCredentials(token);
            // Store the token to disk for later program executions
            fs.writeFile(TOKEN_PATH, JSON.stringify(token), (err) => {
                if (err) return console.error(err);
                console.log('Token stored to', TOKEN_PATH);
            });
            callback(oAuth2Client);
        });
    });
}

/**
最終行を取得する関数
 */
async function getLastRow(auth) {
    const sheets = google.sheets({ version: 'v4', auth });
    const lastRow = await new Promise((resolve, reject) => {
        sheets.spreadsheets.values.append({
            spreadsheetId: SPREADSHEET_ID,
            range: `${SHEET_NAME}!A1`,
            valueInputOption: 'USER_ENTERED',
            insertDataOption: 'INSERT_ROWS',
            resource: {
                values: [
                    [],
                ],
            }
        }, (err, result) => {
            if (err) {
                // Handle error.
                console.log(err);
            } else {
                //result.data.tableRangeが無い = シートのどのセルにも値が入力されていないので、0を返す
                if(!result.data.tableRange) return resolve(0);
                const match = result.data.tableRange.match(/^.*![A-Z]+\d+:[A-Z]+(?<lastRow>\d+)$/);
                if(match){
                    resolve(match.groups.lastRow);
                } else {
                    //1つのセルのみ入力されているシートの場合は上記の正規表現ではエラーになる
                    const match_only1cell = result.data.tableRange.match(/^.*![A-Z]+(?<lastRow>\d+)$/);
                    resolve(match_only1cell.groups.lastRow);
                }
            }
        })
    });

    //最終行を出力
    console.log(`lastRow: ${lastRow}`);
}

. 解説

sheets.spreadsheets.values.append の result.data は下記のようになっています。
tableRangeが空行追加前の既存データ範囲なので、ここの範囲の末尾を正規表現で抜き出すことで最終行を取得できます。