My Note

自己理解のためのブログ

Google App Script（GAS）で スクレイピングとAPIコール で情報を取得 & スプレッドシートのバックアップ を定期実行する

GAS

はじめに

Google App Scriptを確認あたってメモしておいた方がいいと思った部分について記載します。

主な内容は以下の通り。

スクレイピングする

"App Script" の画面で 「ライブラリ」を追加を押して 以下のスクリプトIDを入力して追加する。追加したら "Parser" が使えるようになります。

スクリプトID：1Mc8BthYthXx6CoIz90-JiSzSafVnT6U3t0z_W3hLTAX5ek4w0G_EIrNw

f:id:yhidetoshi:20220228150155p:plain

サイトに直接スクレイピングする場合

■ スクレイピングしているコードの一部抜粋

// kakaku.comが公開しているサイトへスクレイピング
function fetchInvestmentTrustPriceFromKakaku(isinCode) {
    let url = "https://kakaku.com/fund/detail.asp?si_isin=" + isinCode;
    let html = UrlFetchApp.fetch(url).getContentText();
    // Parser.data(‘抽出データ').from(‘開始文字列').to(‘終了文字列').build()
    let basePrice = Parser.data(html).from('<span class="price">').to('</span>').build();
    return basePrice
}
  • 以下のコードは金価格を取得するコードです。
// MyGoldPartnerから金価格をスクレイピング
function fetchGoldPrice() {
    const url = "https://gold.mmc.co.jp/market/gold-price/"
    let html = UrlFetchApp.fetch(url).getContentText();
    let goldPrices = Parser.data(html).from('<span class="c-table__text--xl">').to('</span>').iterate();
    return [goldPrices[4], goldPrices[5]];
}

GAEで実装したAPIをコールする場合

GAEで実装しているAPIはJSONで返すので jsonをパースする処理にしています。

/* レスポンス例
{ "time": "2022-02-16T21:44:10.569731882Z", "base_price": 10001, "day_before_price": 0 }
*/
function fetchInvestmentTrustPriceFromGAE(gaeEndpoint, requestPath, isinCode, urlOptions) {
    let url = gaeEndpoint + requestPath + isinCode
    let res = UrlFetchApp.fetch(url, urlOptions);
    let resJson = JSON.parse(res.getContentText());

    return resJson;
}

// 自前のGoogle App Engine (GAE) 金価格を取得
/* レスポンス例
{ "time": "2022-02-17T21:04:11.755729+09:00", "gold": 7656, "platinum": 4373 }
*/
function fetchGoldPriceFromGAE(gaeEndpoint, requestPath, urlOptions) {
    let url = gaeEndpoint + requestPath
    let res = UrlFetchApp.fetch(url, urlOptions);
    let resJson = JSON.parse(res.getContentText());
    //let goldPrice = resJson['gold']

    return resJson;
}

GASに機密情報を登録する

以前は管理画面にて登録できたようですが、今はコードから登録する必要があったので以下のコードから登録しました。 今回登録したのは、GAEをベーシック認証をかけているので "ENDPOINT" と "ID" と "PW" を登録しました。

  • プロパティに登録するコード
function setProperty() {
    let prop = PropertiesService.getScriptProperties();
    /* ex
    let secrets = {
        "hoge": "abc",
        "fuga": "123"
    }
    */
    let secrets = {
        "ENDPOINT": "GAEのエンドポイントを設定",
        "ID": "xxx",
        "pass": "yyy"
    }
    prop.setProperties(secrets)
}
  • 登録したプロパティから値を参照するコード（例）
const gaeEndpoint = PropertiesService.getScriptProperties().getProperty("ENDPOINT") // GAEのエンドポイン
  • Basic認証をするために ( urlOptions として認証情報を渡す )
const gaeEndpoint = PropertiesService.getScriptProperties().getProperty("ENDPOINT") // GAEのエンドポイン
const basicAuthId = PropertiesService.getScriptProperties().getProperty("ID")       // Basic認証のID(セットプロパティから取得)
const basicAuthPass = PropertiesService.getScriptProperties().getProperty("PASS")   // Basic認証のPW(セットプロパティから取得)

// Basic認証
  let urlOptions = {
      method: "GET",
      headers: { "Authorization": "Basic " + Utilities.base64Encode(basicAuthId + ":" + basicAuthPass) }
  }

function fetchInvestmentTrustPriceFromGAE(gaeEndpoint, requestPath, isinCode, urlOptions) {
// 省略

シートを別のスプレッドシートにバックアップするコード

function execBackup() {
    // 保存元のシートID
    const originalSheetId = 'オリジナルシートID';
    // 保存先のシートID
    const backupSheetId = '保存先のシートID;
    // 保存元データのシート名（保存先データのシート名と同一）
    const sheets = ['sheet_A', 'sheet_B', 'sheet_C'] // 対象のシート名をセット

    // シート毎に処理
    for (let i = 0; i < sheets.length; i++) {
        // オリジナルデータの設定
        let originalSheet = SpreadsheetApp.openById(originalSheetId);
        let originalSheetName = originalSheet.getSheetByName(sheets[i]);
        // バックアップ先の設定
        let backupSheet = SpreadsheetApp.openById(backupSheetId);
        let backupSheetName = backupSheet.getSheetByName(sheets[i]);

        // 保存先のデータを削除
        backupSheetName.clear();

        let lastRow = originalSheetName.getLastRow();
        let lastColumn = originalSheetName.getLastColumn();
        let copyData = originalSheetName.getRange(1, 1, lastRow, lastColumn).getValues();

        // データの書き込み
        backupSheetName.getRange(1, 1, lastRow, lastColumn).setValues(copyData);
    }
}

GASのスクリプトを定期実行する

function execBackup() {} を作成したのでバックアップを実行するときは execBackup を選択。

  1. トリガーを選択
  2. トリガーを追加
  3. 実行する関数名をプルダウンメニューから選択

f:id:yhidetoshi:20220228153827p:plain