以前 Google SpreadSheet: ハイパーリンクを一括抽出するという記事を書いたのですが、これではうまくいかないケースがあったので、ちょっと調べて別の手段を講じましたので、紹介しておきます。

SpreadSheet でセル全体に対してリンクを設定すると、そのセルには1つのリッチテキストが存在する事となって従来の手法でも取得できたのですが、セルの一部の文字列に対してリンクを設定すると、そのセルには複数のリッチテキストが存在する事になるらしく、従来の手法ではURLを抽出できませんでした。

図にしてみるとA列のような場合、従来手法のD列はA4しか抽出できませんでしたが、今回の手法であるBC列はA2, A3も抽出できています。

今回の手法で作成した関数

今回は getLinkUrlAndText という関数を作成しました。引数に対象の1列を表す範囲を文字列として与えると、URLとそれを設定された文字列を取得して返却します。

function getLinkUrlAndText(rangeText) {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sheet = ss.getActiveSheet()
  const range = sheet.getRange(rangeText)
  const vals = range.getRichTextValues()
  const row = [];

  if(vals[0].length > 1){
    Browser.msgBox("1列だけ指定してください。例) A2:A10")
    return
  }

  for( let i=0; i<vals.length; i++ ){
    const cell = vals[i][0]
    const richTxtArr = cell.getRuns()
    for( let j=0; j<richTxtArr.length; j++ ){
      const richText = richTxtArr[j]
      const url = richText.getLinkUrl()
      if(url){
        row.push([richText.getText(), url])
        break
      }
    }
  }

  return row
}