GoogleAPIs spreadsheetAPI 置換API

ぱっと調べてやってる人がいなかったので、備忘録として載せておきます。

fields	meaning
find	検索単語
replacement	置き換え単語
matchCase	検索で大文字と小文字が区別される場合はTrue
matchEntireCell	検索値がセル全体と一致する必要がある場合はTrue
searchByRegex	正規表現にする場合はtrue 正規表現ルールはこちら
includeFormulas	検索に数式を含むセルを含める必要がある場合はTrue。数式を含むセルをスキップする場合はFalse。
sheetId	シートID
allSheets	すべてのシートに適応するか
range	範囲

JSON_representation.json

{
  "find": string,
  "replacement": string,
  "matchCase": boolean,
  "matchEntireCell": boolean,
  "searchByRegex": boolean,
  "includeFormulas": boolean,

  // Union field scope can be only one of the following:
  "range": {
    object (GridRange)
  },
  "sheetId": integer,
  "allSheets": boolean
  // End of list of possible types for union field scope.
}

置換APIサンプル

find_replace_example.ts

import { google, sheets_v4, Auth, Common } from 'googleapis'
async execAPI(spreadsheetId: string, key: string, value: string) {
    const auth = await this.authentication(SpreadSheetExchangeScope)
    const sheets = google.sheets({ version: 'v4', auth })

    /* batchUpdateのOption */
    const request = {
      spreadsheetId: spreadsheetId,
      resource: {
        requests: [
          {
            findReplace: {
              find: key,
              replacement: value,
              allSheets: true
            }
          }
        ]
      }
    }

    try {
      const response = (await sheets.spreadsheets.batchUpdate(request)).data
      // TODO: Change code below to process the `response` object:
      console.log('Exchange Success')
    } catch (err) {
      console.log('err:', err)
    }
  }

execAPI(spreadsheetId,"りんご","なし")

正規表現による置換サンプル

regex.ts

import { google, sheets_v4, Auth, Common } from 'googleapis'
  /**
   * Keyで検索し、valueに置き換える
   * @param spreadsheetId 
   * @param key 
   * @param value 
   */
  async regexExecAPI(spreadsheetId: string, key: string, value: string) {

    const auth = await this.authentication(SpreadSheetExchangeScope)
    const sheets = google.sheets({ version: 'v4', auth });

    /* batchUpdateのOption */
    const request = {
      spreadsheetId: spreadsheetId,
      resource: {
        requests: [{
          findReplace: {
            find: "^\\$\\{.*\\}$",
            replacement: value,
            allSheets: true,
            searchByRegex: true
          },
        }],
      },
    }

    try {
      const response = (await sheets.spreadsheets.batchUpdate(request)).data;
      // TODO: Change code below to process the `response` object:
      console.log("exchange regex Success")
    } catch (err) {
      console.log("err:", err)
    }
  }

詳しくはこちらのドキュメントを見てください
https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#FindReplaceRequest