Google Apps Scriptで選択範囲の空白セルを文字列で埋めるスクリプト

選択範囲の空白セルを文字列で埋める

Googleスプレッドシートを使用していると空白セルを文字列で埋めたいことがある。

例えばA1:A5の選択範囲にfoo, bar, 空白セル, baz, の値がある場合、空白セルには指定した文字列を追加するなどだ。

空白セルが1つであればコピー＆ペーストでも良いが、複数あったり、繰り返して行う必要があったりすると手動でやるのはかなり面倒だし間違える可能性も高くなる。

そのため、このような作業はGoogle Apps Script (以下GAS) で処理したほうが良い。

GASで選択範囲の空白セルを埋める

GASで選択範囲の空白セルを詰めるスクリプトを作成する前に以下のスクリプトでメニューに「拡張機能」 => 「選択範囲の空白セルを埋める」が表示されるようにする。

function onOpen() {
  const ui = SpreadsheetApp.getUi()
  const menu = ui.createMenu('拡張機能')
  menu.addItem('選択範囲の空白セルを埋める', 'fillEmptyCells')
  menu.addToUi()
}

次に選択範囲の空白セルを詰めるスクリプトを以下のように追記する。

function fillEmptyCells() {
  const ss = SpreadsheetApp.getActiveSheet()
  const range = ss.getActiveRange()
  const values = range.getValues()
  const ui = SpreadsheetApp.getUi()
  const ret = ui.prompt('選択範囲の空白セルに入れるテキストを入力')
  values.forEach((v, i) => {
    v.forEach((w, j) => {
      if (w === '') {
        values[i][j] = ret.getResponseText()
      }
    });
  });
  range.setValues(values)
}

getActiveRange()で選択範囲を取得して、さらにgetValues()ですべての値を取得。

forEachで配列をすべて確認をして値が空であれば空白セルなのでprompt(入力ダイアログ)で入力したテキストを入れる処理を記述してsetValuesでセルに返せばすべての空白セルに指定したテキストが入る。