象と散歩: 2022/05

Post Date：2022年5月7日　

Googleスプレッドシートの更新された範囲をURLとして通知する

シートの更新通知を使うと確認が必要のない更新通知も送られてくるので、自分に必要な項目が更新されたときだけに通知が欲しい。

また、どこが変更されたのかが分かると、通知を受けた人に優しい仕様となります。

象と散歩: Googleスプレッドシートの更新された値を知るで、変更された箇所の行番号と列番号を取得する方法を紹介しましたが、変更範囲をセル範囲が指定されたリンクとして作成するGASプログラムを作ってみました。

URLの取得

onEdit()関数については、象と散歩: Googleスプレッドシートの更新された値を知るで説明していますが、スプレッドシートが更新されたときに実行される関数です。

onEdit(e)	{
  var ssUrl = e.source.getUrl()	//スプレッドシートのURL
  console.log(ssUrl)
}

セルの内容が更新されると、上記の関数が実行されます。マニュアルで実行するとエラーとなりますので、セルの値を更新してログを確認してください。

onEdit()関数の引数に e を指定していますが、e.source でスプレッドシートオブジェクトが取得でき、getUrl()でスプレッドシートのURLが取得できます。

シートはGIDパラメータで指定する

上記で取得したスプレッドシートのURLは、下記のような構成になっています。

https://docs.google.com/spreadsheets/d/xxxxx/edit

複数シートがある場合には、URLにGIDパラメータが付与されています。

https://docs.google.com/spreadsheets/d/xxxxx/edit#gid=749420395

最初のシート（シート 1）は、必ず gid=0 ですが、getSheetId() で GIDを取得することができます。

onEdit(e)	{
  var sheet = e.source.getActiveSheet()
  var sheetId = sheet.getSheetId()	//シートID
  console.log(sheetId)
}

セル範囲はRANGEで指定する

セルを右クリックして"このセルへのリンクを取得"をクリックすると

下記のようなURLが取得できます。

https://docs.google.com/spreadsheets/d/xxxxx/edit#gid=0&range=C2

また複数セルを選択した状態で右クリックして"この範囲のリンクを取得"をクリックすると下記のようなURLが取得できます。

https://docs.google.com/spreadsheets/d/xxxxx/edit#gid=0&range=A3:C3

"range=" で範囲を指定したURLを取得できます。

A1形式で範囲を取得する

onEdit()で実行されたときにrangeで変更範囲が取得できますが、A3:C3のようなA1形式で範囲を取得するには、range.getA1Notation() を使います。

更新範囲がひとつのセルならA3、範囲ならA3:C3のように取得することができます。

onEdit(e)	{
  var sheetRange = e.range.getA1Notation()	//変更範囲
  console.log(sheetRange)
}

シートの値を変更して、ログを確認してください。複数セルの範囲は、シート上で範囲選択をしてコピペをすることで確認できます。

シートと範囲を指定したURLを作成

シートID（GID）と範囲（RANGE）を指定したURLを作成します。

function onEdit(e) {
  var ssUrl = e.source.getUrl()	//スプレッドシートのURL
  var sheet = e.source.getActiveSheet()
  var sheetId = sheet.getSheetId()	//シートID
  var sheetRange = e.range.getA1Notation()	//変更範囲
  var url = ssUrl + '#gid=' + sheetId + '&range=' + sheetRange
  console.log(url)
}

ログに出力されたURLをコピペして、範囲選択がされた状態でスプレッドシートが開くかを確認してください。

変更範囲の確認

特定の範囲が更新されたときだけ通知があるように、更新されたセルが対象としたいセル範囲内にあるかを確認します。

下記のサンプルプログラムでは、targetStartColumn, targetEndColumn で対象列を、targetStartRow, targetEndRow で対象行を指定しています。

下例では、C列の2行目から1000行目が更新されたかを確認するサンプルコードとなります。

function onEdit(e)  {
  var targetStarColumn =3 //対象セル（開始列）
  var targetEndColumn = 3 //対象セル（終了列） 
  var targetStartRow = 2  //対象セル（開始列）
  var targetEndRow = 1000 //対象セル（終了列）

  var rowStart = e.range.rowStart //更新開始行の取得
  var rowEnd = e.range.rowStart   //更新終了行の取得
  var columnStart = e.range.columnStart //更新開始列の取得
  var columnEnd = e.range.columnEnd     //更新終了列の取得

  var ssUrl = e.source.getUrl() //スプレッドシートのURL
  var sheet = e.source.getActiveSheet()
  var sheetId = sheet.getSheetId()  //シートID
  var sheetRange = e.range.getA1Notation()  //変更範囲

  var text = '変更箇所の通知\n'
  if (targetStarColumn <= columnStart &&  columnEnd <= targetEndColumn
      &&  targetStartRow <= rowStart &&  rowEnd <= targetEndRow) {
    text = text + ssUrl + '#gid=' + sheetId + '&range=' + sheetRange
  } else  {
    text = '対象範囲外の更新'
  }
  console.log(text)
}

対象範囲が更新された場合に "更新されたシートとセル範囲を指定したURL" を作成します。シートを更新して実行結果をログで確認してください。

メールを送信する

シンプルトリガーのonEdit()ではメール送信ができないので、function名を変更して、トリガーを設定します。

トリガーの設定方法については、

を参考にしてください。

メール送信は GmailAppクラスのsendEmailメソッドを使います。

GmailApp.sendEmail(宛先, 件名, 本文, {オプション})

オプションは省略可能です。（詳細はClass GmailAppを参照してください）

mailTo で指定しているメールアドレスは、自分のメアドに変更してください。

function updateNotice(e) {
  var ssUrl = e.source.getUrl()	//スプレッドシートのURL
  var sheet = e.source.getActiveSheet()
  var sheetId = sheet.getSheetId()	//シートID
  var sheetRange = e.range.getA1Notation()	//変更範囲
  var url = ssUrl + '#gid=' + sheetId + '&range=' + sheetRange

  // メール送信
  var mailTo   = 'sample@gmail.com'
  var subject  = '更新通知'
  var bodyText = '変更箇所の通知\n'
  bodyText     = bodyText + url
	
  GmailApp.sendEmail(
    mailTo,
    subject,
    bodyText
  )
}

特定のセルが更新されたときにメールを出す場合には下例を参考にしてください。

function updateNotice(e) {
  var targetStarColumn = 3	//対象セル（開始列）
  var targetEndColumn = 3	//対象セル（終了列） 
  var targetStartRow = 2	//対象セル（開始列）
  var targetEndRow = 1000	//対象セル（終了列）

  var rowStart = e.range.rowStart //更新開始行の取得
  var rowEnd = e.range.rowStart   //更新終了行の取得
  var columnStart = e.range.columnStart //更新開始列の取得
  var columnEnd = e.range.columnEnd     //更新終了列の取得

  var ssUrl = e.source.getUrl() //スプレッドシートのURL
  var sheet = e.source.getActiveSheet()
  var sheetId = sheet.getSheetId()  //シートID
  var sheetRange = e.range.getA1Notation()  //変更範囲

  var text = '変更箇所の通知\n'
  if (targetStarColumn <= columnStart &&  columnEnd <= targetEndColumn
      &&  targetStartRow <= rowStart &&  rowEnd <= targetEndRow) {
    text = text + ssUrl + '#gid=' + sheetId + '&range=' + sheetRange
    sendMail(text)
  }
}
function sendMail(text)	{
  // メール送信
  var mailTo   = 'sample@gmail.com'
  var subject  = '更新通知'
  var bodyText = text

  GmailApp.sendEmail(
    mailTo,
    subject,
    bodyText
  )
}

上記例では更新されたセル範囲でURLを作成していますが、range=開始行番号:終了行番号とすると、更新された行をフォーカスしたURLが作成できます。

https://docs.google.com/spreadsheets/d/XXXXX/edit#gid=0&range=3:3

上記も、rowStart と rowEnd を使って簡単に作成できます。