スプレッドシートのURLからGAS上のオブジェクトを取得したい

概要

毎回違うスプレッドシートからデータを取り込みたいから、URL指定で取り込み元を指定できるようにする。

導入

GASでスプレッドシートから他のスプレッドシートを参照したいときは、スプレッドシートのIDとシート名を指定して読み込みますよね。
こんな風に。

const spreadsheet = SpreadsheetApp.openById('スプレッドシートID');
const sheet = spreadsheet .getSheetByName('シート名');

事前にスプレッドシートIDとシート名がわかっていれば固定値でコードに埋め込んであげれば良いのですが、毎月シート名が変わる・スプレッドシート自体が変わるなどの場合に埋め込みだと都度変更が必要になります。
手間ですね、URLからスプレッドシートIDとシート名を取得しましょう。

コードと解説

function getSheetByUrl(url) {
  if(!url) {
    throw "input error"
  }

  // URLの3階層目からスプレッドシートID取得
  const regExpSpreadsheetId = new RegExp("https?://.*?/.*?/.*?/(.*?)(?=/)")
  const spreadsheetId = url.match(regExpSpreadsheetId)[1]

  // gidパラメータからシートID取得
  const regExpGid = new RegExp("gid=(.*?)(&|$)")
  const gid = url.match(regExpGid)[1]

  // 一致するシートオブジェクト取得
  const spreadsheet = SpreadsheetApp.openById(spreadsheetId)
  for(const sheet of spreadsheet.getSheets()) {
    if(sheet.getSheetId() === Number(gid)) return sheet
  }

  return null
}

(エラー処理と正規表現ちゃんとテストしてないのでミスがあるかもしれない)

取得できるか確認しましょう。
いつか見たようなこのスプレッドシートからA1のセルを取りましょう。

function test() {
  const sheet = getSheetByUrl("https://docs.google.com/spreadsheets/d/[スプレッドシートID]/edit#gid=[シートID]")
  console.log(sheet.getRange(1,1).getValue())
}

実行結果

無事データが取得できたようです。
あとは、URL引数をHTMLのフォームを作って入力してもらうもよし、入力用のセルを設けてそこに入れてもらうも良しでございます。

ちょっとした解説

この通り、URLの中にスプレッドシートIDとシートIDが含まれています。
となれば後は正規表現でうまいこと値を抜けばいいわけです。

https://docs.google.com/spreadsheets/d/[スプレッドシートID]/edit#gid=[シートID]

また、直接gidでシートを取得するメソッドが生えていないので、全シートのオブジェクトを取得して一致するIDを持つシートを結果として返却します。

// 一致するシートオブジェクト取得
const spreadsheet = SpreadsheetApp.openById(spreadsheetId)
for(const sheet of spreadsheet.getSheets()) {
  if(sheet.getSheetId() === Number(gid)) return sheet
}