象と散歩: .GAS

ラベル .GAS の投稿を表示しています。すべての投稿を表示

Post Date：2022年8月13日　

GASで複数のPDFを結合する

Google Apps Script で複数のPDFを結合できないかと探していたら、officeの杜 | PDFを結合するという記事がありました。

officeの杜で共有されているこちらのコードにある mergePdfs() を活用させていただきました。元ネタは、stack overflowにある Merge Multiple PDF's into one PDF です。

結合できるPDFファイル

stack overflowのコメントにPDFのバージョンが1.5以上である場合には大きな修正が必要（結合できない）と記載されていますが、Googleの機能（印刷）やChromeの印刷でPDFにしたPDFはマージ可能でした。

対象	PDFバージョン	マージ可否
Googleドキュメント	1.4	◯
Googleプレゼンテーション	1.7	◯
Googleスプレットシート	1.7	◯
Google Chrome	1.4	◯
Office 365	1.7	✕

PDFのバージョンはAdobe Acrobat Readerで確認できます。

下記は、Googleドキュメントの印刷からPDFを保存した場合です。バージョンは1.4となっています。

Googleスプレッドシート、プレゼンテーションは、バージョンが1.7と表示されますが、

前述したように問題なく結合することができます。

Office 365でPDFに変換したファイルは結合できませんでしたが、Googleを中心としたサービスを利用しているのであれば問題なさそうです。

mergePdfs()関数を使う

mergePdfs()関数の説明と引数をみると

mergePdfs(directory, name, pdf1, pdf2, pdf3, ....)

drirectory	PDFが格納されているディレクトリID
name	結合したPDFのファイル名
pdf1, ...	結合するPDFファイル

結合するPDFファイルは2つ以上複数指定できますが、引数でファイル名をひとつづつ指定するのは煩わしい。

ということで、フォルダーにあるPDFをリスト（配列に格納）して引数として渡せるように変更してみました。

またPDFファイルの結合順番はファイル名で昇順にソートした順番にします。ファイル名の先頭に数字を入れておけば番号順で結合されます。

例えば、ファイル名を

0.test.pdf
1.test.pdf
2.test.pdf

としておけば、0.test、1.test、2.testの順番で結合されます。

mergePDFs()関数の修正

先ずは、office杜のこちらのリンクからmergePdfs()のプログラムを取得してください。

修正箇所は3箇所です。

12行目

関数の引数をファイル名から、配列を1つ渡すように変更します。

修正前：
function mergePdfs(directory, name, pdf1, pdf2, opt_pdf3) {

修正後：
function mergePdfs(directory, name, pdfList {

21行目

関数の引数ループで取得しているところを、引数に格納されているファイル数分（配列の長さ分）で処理するように変更します。

修正前：
for (var argumentIndex = 2; argumentIndex < arguments.length; argumentIndex++) {

修正後：
for (var i=0; i<pdfList.length; i++) {

23行目

マージするPDFのファイルサイズ取得を引数の配列からに変更します。

修正前：
var bytes = arguments[argumentIndex].getBlob().getBytes()

修正後：
var bytes = pdfList[i].getBlob().getBytes()

また、475行目移行はPDFを分割する関数のコードになっていますので削除してしまっても問題ありません。

mergePDFs()関数を呼び出す

次は、修正したmergePDFs()関数を呼び出すプログラムになります。指定したフォルダIDにあるPDFファイルを配列(pdfList)に格納して、ファイル名順にソートしてから mergePDFsに引き渡します。

<フォルダID>にはGoogleドライブのフォルダIDを、<ファイル名>には結合して作成するファイル名にしてください。

フォルダIDは、フォルダを開いたときのURL、https://drive.google.com/drive/folders/XXXXX のXXXX部分になります。

function merge(srcFolderId, fileName){
  var srcFolderId = '<フォルダID>' //フォルダ ID
  var fileName = '<ファイル名>' //結合後のファイル名

  //フォルダ内のファイルを取得
  var srcFolder = DriveApp.getFolderById(srcFolderId)
  var srcFiles = srcFolder.getFiles()

  //PDFファイルだけを配列に格納
  var pdfList = []
  while(srcFiles.hasNext()) {
    var srcFile = srcFiles.next()
    if (srcFile.getMimeType()==='application/pdf') {
      pdfList.push(srcFile);
    }
  }
  pdfList.sort()  //照準でソート
  mergePdfs(srcFolder, fileName, pdfList)
}

13行目のgetMimeType()でPDFファイルだけを対象にしています。また17行目のsort()で配列に格納した各ファイルをファイル名でソートしています。

スプレッドシートのUI機能で汎用的にする

もう少し汎用的に使えるように、スプレッドシートのUI機能を使って、フォルダIDと結合して作成するファイル名をUI上で入力するようにします。

またスプレッドシートのメニューからGASの実行を可能にします。

スプレッドシートの機能を使うので、Googleスプレッドシートの拡張機能から Apps Script を作成する必要があります。

スプレッドシートにメニューに追加する

スプレッドシートが開かれたときにメニューを追加します。こんな感じです。

「mergePDFs」 > 「PDFを結合」で diaLog関数を実行します。

function onOpen(){ 
  SpreadsheetApp
    .getActiveSpreadsheet()
    .addMenu('mergePDFs', [
      {name: 'PDFを結合', functionName: 'diaLog'}
    ])
}

ダイアログ

詳細の説明は省きますが、「フォルダーID」と「ファイル名」の入力の後に確認画面を出してが簡単なチェックと確認を出力して merge()関数にフォルダIDとファイル名を引き渡します。

またPDFの結合でエラーとなった場合にもエラー出力をするようにしています。

下記がサンプルコードとなります。

function diaLog() {
/********************
PDFがあるフォルダを指定 
********************/
  var srcFolderId = Browser.inputBox("結合したいPDFがあるフォルダを指定してください（FolderID）",Browser.Buttons.OK_CANCEL)
  //キャンセルが押下されたら終了
  if (srcFolderId == 'cancel')  {
    return
  }
  try {
    var srcFolder = DriveApp.getFolderById(srcFolderId)
  }
  catch(e) {
    Browser.msgBox('ERROR', srcFolderId + ' is not available', Browser.Buttons.OK)
    return
  }
/********************
ファイル名の指定 
********************/
  var fileName = Browser.inputBox("結合後のファイル名を指定してください（FolderID）",Browser.Buttons.OK_CANCEL)
  //キャンセルが押下されたら終了
  if (fileName == 'cancel')  {
    return
  } else if (fileName == '') {
    Browser.msgBox('ERROR', 'File name is not defined', Browser.Buttons.OK)
    return
  }
/********************
開始の確認 
********************/
  var srcFolderName = srcFolder.getName()

  var result = Browser.msgBox('Confirm', '「' + srcFolderName + '」にあるすべてのPDFを\\n「' + fileName + '」として結合します', Browser.Buttons.OK_CANCEL)
  if (result == 'cancel')  {
    return
  }

  try {
    merge(srcFolderId, fileName)  //PDF格納フォルダ，ファイル名
  }
  catch(e) {
    Browser.msgBox('ERROR', 'Error Occurred during merging', Browser.Buttons.OK)
    return
  }
  
  Browser.msgBox('Complete', 'PDFの結合が完了しました', Browser.Buttons.OK)
}

これで完成です。

スプレッドシートのメニューから dialog()関数を呼び出し、merge()関数でフォルダ内のPDFをリスト化して、mergePDFs()関数でPDFを結合します。

先人の知恵と努力に感謝です。

GASの基礎を学べる参考図書

ある程度プログラミンがわかっていれば、WEBやYoutubeでも十分に調べられると思いますが、初歩的なところからであれば参考図書は有効な学習手段です。

「詳解! Google Apps Script完全入門 [第3版]」は、プラグラミング初心者にわかりやすく説明されています。GASを最初に学ぶ一冊として良書です。

詳解! Google Apps Script完全入門 [第3版]

高橋宣成 (著)

楽天ブックスで購入す

Amazonで購入

Udemy オススメ講座

【新IDE対応】Google Apps Script（GAS）の基礎を完全習得

講師：事務職たらこ

印象に残りやすい手書き風スライドを用いGASの基本的なプログラミングを気軽に学ぶことができる。本講座でGASを活用した自動化ができるレベルにはなれないが、基礎としては十分。

Post Date：2022年6月17日　

gasの変数について考えてみる

Google Apps Scrit (GAS) で変数を定義するキーワードには、var, let, const の3種類があります。変数と定数の違いは明確ですが、var と let の違いはどのようなところにあるのかをサンプルプログラムを実行しながら確認してみました。

変数と定数

var, let は、再代入（値を入れ替えること）ができる変数で、const は定義した値を変更できない定数です。

再代入とは、下記の例のように、最初に x に 1を代入して、次に x に x+1(=2) を代入するということです。

function argument() {
  let x = 1
  x = x + 1
}

定数の場合には、x に 1を代入したあとに変更ができません。下記を実行するとエラーとなります。

function argument() {
  const x = 1
  x = x + 1
}

変数の利用可能範囲（スコープ）

では、var と let の違いは何でしょうか？

GoogleのV8ランタイムの説明を読むと

let および const キーワードは、ブロックスコープのローカル変数/定数を定義することができます。（和訳）

【引用】 let and const - Google Apps Script

と記載されています。

ブロックとは、{}で括られた処理のことで、例えば、if分も{}で括られた処理です。ローカル変数というのは、そのブロック内でしか使えない変数/定数ということです。

let, const は、同じ関数内であってもif文内で宣言した関数は使えないということになります。ちなみに var の場合は、同じ関数内であれば if文内で宣言した変数を利用することができます。

let and const - Google Apps Script にあるサンプルプログラムに const と var を追加してスコープについて確認してみます。

function myFunction() {
  let x = 'hello'
  const y = 'hello'
  var z = 'hello'
  if (x === 'hello') {
    let x = 'World'
    const y = 'World'
    var z = 'World'
    console.log('let x=', x)      // Prints 'world'
    console.log('const y=', y) // Prints 'world'
    console.log('var z=', z)     // Prints 'world'
  }
  console.log('let x=', x)      // Prints 'hello'
  console.log('const y=', y) // Prints 'hello'
  console.log('var z=', z)     // Prints 'world'
}

let, const の有効範囲はブロック内です。5行目からのif文のブロックの中では、6,7行目で変数の宣言と代入がされている 'world' が出力されます。

しかし、13,14行目のログ出力では、myFunction() として、2,3行目で変数の宣言と代入がされている 'hello' が有効な変数となります。

一方、var で定義した変数 xは、15行目のログ出力で 'world' と出力されます。これは var 変数は同一関数内で有効であり、かつ次に説明する再宣言ができるので、console.log が実行される直前（11行目）に宣言と代入がされた変数の値が出力されます。

関数で定義した変数はif, for のブロック内でも使える

上位のブロックで let, const で宣言された変数は、下位のブロックでも参照できます。

下記の例で説明します。

function checkScope() {
  let x = 0
  if (x==0)  {
    let y = 0
    for (let i=0; i<10; i++) {
      x = x + 1
      y = y + 1
    }
    console.log('y=', y)
  }
  console.log('x=', x)
}

変数 x は、checkScope() 関数で定義しているので、下位ブロックのif文、さらにその下位にあたる forループでも参照することができます。なので、x=x+1 が10回実行され、forループ、if文を抜けたあとのログ出力で x=10 と出力することができます。

変数 y は、if文の中で宣言されているので、下位ブロックのforループで参照することができます。forループでの処理で再代入された値が、if文内のログ出力で可能です。

しかし、if文を出た後にログ出力をしようとすると y は定義されていないとエラーとなります。

function checkScope() {
  let x = 0
  if (x==0)  {
    let y = 0
    for (let i=0; i<10; i++) {
      x = x + 1
      y = y + 1
    }
    console.log('y=', y)
  }
  console.log('x=', x)
  console.log('y=', y)
}

また forループで使っている変数 i も let で定義しているので forループ内だけで有効です。これを for (var i=0; ....) とすると、変数 i は、関数内で参照できる変数となります。

また変数 i を宣言するときに、let, var のキーワードを付けないと（忘れると）、変数 i は、グローバル変数となり、変数が宣言された以降に実行されるどの関数でもアクセスできるようになってしまいます。

function checkScope() {
  let x = 0
  if (x==0)  {
    let y = 0
    for (i=0; i<10; i++) {
      x = x + 1
      y = y + 1
    }
    console.log('y=', y)
  }
  console.log('x=', x)
  print()
}
function print()	{
  console.log('i=', i)
}

再宣言

変数のスコープで var の再宣言についてふれましたが、もう少し簡単な例で説明します。下記のように var は、同じ関数内で何度でも宣言できます。

function redeclaration() {
  var x = 1
  x = x + 1
  console.log('x=', x)
  var x = 'Hello'
  console.log('x=', x)
}

let, const は同一ブロック内では1回しか宣言できません。下記のプログラムは構文エラーとなり保存できません。

function test() {
  let x = 1
  x = x + 1
  console.log('x=', x)
  let x = 'Hello'
  console.log('x=', x)
}

var, let, constの違い（まとめ）

var, int, const の違いを表にまとめます。

キーワード	再代入	再宣言	スコープ
var	可能	可能	同一関数内
let	可能	不可	ブロック内
const	不可	不可	ブロック内

参照できる範囲が少なく、再宣言ができないということは、より明示的に変数を使うということになるので、「varではなくlet を利用しましょう」というのも納得できます。

関数間で値の引き渡し

関数の中で関数を呼び出す場合には、引数として関数に値を引き渡すことができます。また、関数での実行結果を戻す場合には、戻り値として結果を返却することができます。

function argument() {
  let x = 1
  let y = 2
  let z = cal(x, y)
  console.log('z=', z)
}
function cal(a, b)	{
  let c = a + b
  return c
}

cal() を呼び出すときに引数として x, y をセットしています。7行目の function cal(x, y)では、4行目で設定した引数 x, y をそれぞれ a, b にセットしています。この関数の中では、合算した値（1+2）を cに代入して、cを戻り値としています。

関数の呼び出し側は、戻り値を変数zに代入するために let z = cal(x, y) としています。

グローバル変数

最後にグローバル変数についてです。関数内ではなくプログラムのトップレベルで定義された変数/定数は、プログラ内のどこから（同じプロジェクト内のどの関数から）でもアクセスすることができます。

const x = 1
const y = 2
function main()	{
  sub1()
  sub2()
  console.log('x=', x)
  console.log('y=', y)
}
function sub1()	{
  console.log('x=', x)
}
function sub2()	{
  console.log('y=', y)
}

変数も奥が深いですね。

GASの基礎を学べる参考図書

「詳解! Google Apps Script完全入門 [第3版]」は、プラグラミング初心者にわかりやすく説明されています。GASを最初に学ぶ一冊として良書です。

詳解! Google Apps Script完全入門 [第3版]

高橋宣成 (著)

楽天ブックスで購入す

Amazonで購入

Udemy オススメ講座

【新IDE対応】Google Apps Script（GAS）の基礎を完全習得

講師：事務職たらこ

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 を使って簡単に作成できます。

GASの基礎を学べる参考図書

「詳解! Google Apps Script完全入門 [第3版]」は、プラグラミング初心者にわかりやすく説明されています。GASを最初に学ぶ一冊として良書です。

詳解! Google Apps Script完全入門 [第3版]

高橋宣成 (著)

楽天ブックスで購入す

Amazonで購入

Udemy オススメ講座

【新IDE対応】Google Apps Script（GAS）の基礎を完全習得

講師：事務職たらこ

Post Date：2022年3月6日　

GASでExcelをGoogleスプレッドシートに変換する

ExcelファイルのデータをGASで処理するには、GoogleDrive上でExcelファイルを開いてGoogleスプレッドシートで保存する必要があります。

小さなExcelファイルなら上記の方法でも構いませんが、大きなExcelファイルだと ”Googleスプレッドシートとして保存” で時間がかかりすぎて保存できない、、、。

また複数ファイルがあるときは一括変換できれば楽ちんです。

ということで、GASでExcelファイルをGoogleスプレッドシートに変換する２つの方法について説明します。

Excelファイルを指定してスプレッドシートに変換する
フォルダにあるExcelファイルを一括変換する

Excel to Google Sheets には Drive APIが必要

Google Apps Script でExcelファイルを開く場合には、Googleドライブのファイルやフォルダを操作できる DriveAppクラスを使います。

但し、そのまま、DriveAppのコードを書いて実行しても

エラー ReferenceError: Drive is not defined

と、実行できません。

Drive APIサービスを追加する

DriveAppクラスを使うためには、Apps Scriptエディターの左側のメニューにある「サービス」からDrive APIを追加する必要があります。

① サービスの右側にある＋をクリック

② 一覧から Drive API を選択して追加

Drive APIが追加されると左メニューのサービスの下にDriveが表示されます。

これで DriveAppクラスが利用できます。

Excelファイルを指定してスプレッドシートに変換する

Excelファイルを指定して、指定したフォルダにGoogleスプレッドシートを出力するサンプルコードとなります。

Excelファイルと出力先のフォルダはIDで指定します。

ExcelファイルとフォルダのID

GoogleドライブからExcelファイルを開いたURLのXXXXXの部分がIDとなります。

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

ExcelのファイルID

GoogleドライブのフォルダIDは、XXXXXの部分がIDになります。

https://docs.google.com/drive/u/0/folder/XXXXX

フォルダID

サンプルコード（Excelファイル指定）

上記で説明したExcelファイルと出力先のフォルダのIDを指定する、簡単なサンプルコードです。

12行目で取得したExcelファイル名を13行目でログに出力していますが、変換したGoogleスプレッドシート名も同じファイル名にしています。

function convertExcel()  {
  //変換するExcelファイル
  let excelFileId = 'XXXXX'

  //スプレッドシート出力先フォルダー
  let destFolderId = 'XXXXX'

  //ExcelファイルをファイルIDで取得
  var excelFile = DriveApp.getFileById(excelFileId)

  //Excelファイル名取得
  var fileName = excelFile.getName()
  Logger.log(fileName)

  var option =  {
    mimeType:MimeType.GOOGLE_SHEETS,   //Google sheets
    parents:[{id:destFolderId}],                       //出力先フォルダー
    title:fileName                                          //出力先ファイル名
  }
  Drive.Files.insert(option,excelFile)
}

20行目の

Drive.Files.insert(option,excelFile)

で、変換を実施していますが、15〜19行目のオプションで、

MIMEタイプ
出力先フォルダー
出力先ファイル名

を指定しています。

スプレッドシートへの変換なのでMIMEタイプをGOOGLE_SHEETSとしていますが、Enum MimeType | Apps Script | Google Developers に全てのMIMEタイプが記載されています。

変換後に処理を続ける

Googleスプレッドシートに変換してから、そのままシートをGAS処理する場合には、20行目からを下記のように変更してください。

取得したシート名をログに出力する簡単なサンプルです。

function convertExcel()  {
  //変換するExcelファイル
  let excelFileId = 'XXXXX'

  //スプレッドシート出力先フォルダー
  let destFolderId = 'XXXXX'

  //ExcelファイルをファイルIDで取得
  var excelFile = DriveApp.getFileById(excelFileId)

  //Excelファイル名取得
  var fileName = excelFile.getName()
  Logger.log(fileName)

  var option =  {
    mimeType:MimeType.GOOGLE_SHEETS,   //Google sheets
    parents:[{id:destFolderId}],                       //出力先フォルダー
    title:fileName                                          //出力先ファイル名
  }
  var sheetObj = Drive.Files.insert(option,excelFile)

  //スプレッドシートIDを使って、スプレッドシートオブジェクトを取得
  var sheet = SpreadsheetApp.openById(sheetObj.id).getActiveSheet()
  Logger.log(sheet.getName())  
}

変換したスプレッドシートにシートが複数ある場合には、getSheetByName() でシート名を指定します。

フォルダにあるExcelファイルを一括変換する

次は、フォルダにある複数のExcelファイルを一括でGoogleスプレッドシートに変換する方法です。

フォルダにExcelファイル以外がある場合には処理されないようにしています。

function convertExcels() {
  //Excelがあるフォルダ
  let sourceFolderId = 'XXXXX'
  //スプレッドシート出力先フォルダー
  let destFolderId = 'XXXXX'

  // フォルダ配下のファイルを取得
  var sourceFolder = DriveApp.getFolderById(sourceFolderId)
  var folderFiles  = sourceFolder.getFiles()

  // Excelファイルをイテレートして順にスプレッドシートに変換
  while(folderFiles.hasNext()) {
    var file = folderFiles.next()
    //Excelファイルの判断   
    if (file.getMimeType() == 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')  {
      var fileName  = file.getName()
      Logger.log(fileName)
      Logger.log(file.getMimeType())
      var option =  {
        mimeType:MimeType.GOOGLE_SHEETS,  //Google sheets
        parents:[{id:destFolderId}],      //出力先フォルダーを指定
        title:fileName                    //出力先ファイル名
      }
      Drive.Files.insert(option,file)
    }
  }
}

フォルダにあるファイル一覧を取得しているのが、8〜9行目です。

そして、12行目の while文のhasNext() で13行目の next()で取得したファイル一覧から取り出せるファイルが残っているかを調べて、順番にファイルを処理していきます。

Excelファイルだけを変換するために、取り出したファイルがEXCELファイルかを15行目で調べています。

18行目でgetMimeType()でファイルのMIMEタイプを出力していますが、application/vnd.openxmlformats-officedocument.spreadsheetml.sheet が、拡張子xlsのExcelファイルとなります。

GASの基礎を学べる参考図書

「詳解! Google Apps Script完全入門 [第3版]」は、プラグラミング初心者にわかりやすく説明されています。GASを最初に学ぶ一冊として良書です。

詳解! Google Apps Script完全入門 [第3版]

高橋宣成 (著)

楽天ブックスで購入す

Amazonで購入

Udemy オススメ講座

【新IDE対応】Google Apps Script（GAS）の基礎を完全習得

講師：事務職たらこ

Post Date：2022年1月3日　

GASでシートを処理するなら配列を使え！

GoogleスプレッドシートをGAS（Google Apps Script）で処理するのであれば配列を覚えましょう。配列を使ってシートの読み書きを一括で行うと処理が格段に速くなります。

なぜ配列を使うのか

GASには、

Google Sheets のアクセスに時間がかかる
6分で処理が強制停止される

という制約があります。

Googleスプレッドシートを操作するときに

getRange()
getValue()/getValues()
setValue()/setValues()
getLastRow()/getLastColmun()

などのメソッドを使いますが、これらの呼び出しには時間がかかります。

行数が多いシートでは、1行づつシートを読み書きするのと、配列で一括で読み書きするのでは分単位で処理時間が違ってきます。

二次元配列とは

「でも、配列って難しそう、、、」

と、思われがちですが、二次元配列はシートと同じと考えれば簡単です。下記のように行と列で表記されます。

values[行][列]

valuesは変数名です。後ろに続く最初の[]が行で、次の[]が列です。シートの"A1"という表記はA列1行目と列行の順番ですが、配列は行列の順番です。

注意点としては配列のインデックスは0から始まるので、 A1と同じ1行1列目のデータは、values[0][0]となります。

二次元配列はシートと同じ行と列
表記の順番は[行][列]
インデックスは0から始まる

これだけ理解できれば配列を自在に扱うことができます。

では、下記がシートで考えた場合にどのデータを指しているか考えてみてください。

values[1][2]
values[2][0]

答えは、

2行3列目のデータで C2
3行1列目のデータで A3

です。

また、values[0]と記載した場合は1行目のデータとなり、values と[]を付けなければ配列全体となります。

サンプルデータで配列の中身を確認する

実際に、4行3列のサンプルデータで二次元配列を確認していきます。

下記のデータをGoogleスプレッドシートにコピーしてください。

グループ	男性	女性
A	10	20
B	20	10
C	15	15

シートをシートIDとシート名から取得していますので、下記のコードの sheetId と sheetName の値を変更してください。

シートIDはURLの赤字の部分となります。

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

シート名は、シートに付けている名前です。

下記のコードを実行します。

function myFunction() {
  //シートオブジェクトの取得
  var sheetId = 'abcd1234'
  var sheetName = 'シート1'
  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName)

  //配列へシート全体を読み込む
  var values = sheet.getDataRange().getValues()
	
  Logger.log(values[1][2])
  Logger.log(values[2][0])
  Logger.log(values[0])
  Logger.log(values)
}

実行結果は下記の通りです。

20.0
B
[グループ,男性,女性]
[[グループ,男性,女性],[A,10.0,20.0],[B,20.0,10.0],[C,15.0,15.0]]

最後の配列全体の出力結果はカンマ区切りでデータが続いているので一見難しそうです。しかし、各要素を見ると[]が各行のデータで、その中に各列のデータがカンマで区切られていることがわかります。

シートのデータを配列に読み込む

上記サンプルコードの8行目でシート全体（データ範囲）を読み込んでいます。

var values = sheet.getDataRange().getValues()

getRange()で、下記のように範囲を指定してシートを読み込むこともできます。

getRange（開始行、開始列, データを読み込む行数, データを読み込む列数）

データを読み込む行数と列数はgetLastRow()、getLastCloumn()でそれぞれ取得できます。

//範囲を指定して読み込む
var lastRow	= sheet.getLastRow()	//最終行の取得
var lastColumn	= sheet.getLastColumn()	//最終列の取得
var values = sheet.getRange(1, 1, lastRow, lastColumn).getValues()

しかし、シート全体を読み込むのであれば、getDataRange() で範囲を指定しない方が簡単です。

ヘッダー行の削除 shift()メソッド

計算処理などをする場合にはヘッダー行は不要です。

範囲指定で2行目から最終行を読み込むこともできますが、シート全体を配列に読み込んでから1行目を削除した方が簡単です。

function myFunction() {
  //シートオブジェクトの取得
  var sheetId = 'abcd1234'
  var sheetName = 'シート1'
  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName)

  //配列へシート全体を読み込む
  var values = sheet.getDataRange().getValues()
	
  //ヘッダー行の削除
  values.shift()

  Logger.log(values)
}

shift()メソッドは、二次元配列先頭の一次元配列を削除します、、、。簡単にいえば、1行目のデータの削除です。

配列の全行処理は for ... in が便利

配列を全行処理するには、for...in 文が便利です。

下記の11-13行目が for...in の記載となります。これで、in の後ろに記載した配列（values）を1行づつ処理することができます。変数 i には現在処理をしている行数が格納されます。

function myFunction() {
  //シートオブジェクトの取得
  var sheetId = 'abcd1234'
  var sheetName = 'シート1'
  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName)

  //配列へシート全体を読み込む
  var values = sheet.getDataRange().getValues()
  
  //各行の出力
  for (var i in values) {
    Logger.log(values[i])
  }
}

前述したように配列のインデックスは０から始まりますので、変数 i の出力結果は、0, 1, 2 となります。

普通にforループで記載するならlengthプロパティで配列の要素数（行数）を求めてループさせます。

下例では要素数（length）は３です。0行目、1行目、2行目と3回ループを回すので配列の要素数未満という条件でループさせます。

function myFunction() {
  //シートオブジェクトの取得
  var sheetId = 'abcd1234'
  var sheetName = 'シート1'
  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName)

  //配列へシート全体を読み込む
  var values = sheet.getDataRange().getValues()
  
  //各行の出力
  numOfRow = values.length
  for (var i=0; i < numOfRow; i++) {
    Logger.log(values[i])
  }
}

for ... in文で記載した方が簡単です。

末尾に列を追加する push()メソッド

次に男性と女性の数を合算して列の最後（D列）に追加します。

繰り返しとなりますが、配列のインデックスは0から始まりますので、男性は values[i][1] で女性は values[i][2]です。

行の最後に列を追加するには、push()メソッドを使います。

function myFunction() {
  //シートオブジェクトの取得
  var sheetId = 'abcd1234'
  var sheetName = 'シート1'
  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName)

  //配列へシート全体を読み込む
  var values = sheet.getDataRange().getValues()
  
  //各行の出力
  for (var i in values) {
    values[i].push(values[i][1] + values[i][2])
  }
  Logger.log(values)
}

i行目の最後に男性と女性の合算値を追加して、最後に配列全体を出力しています。

ヘッダー行の追加 unshift()メソッド

合算値を追加した新しいヘッダー行を unshift()メソッドで先頭行に追加します。

function myFunction() {
  //シートオブジェクトの取得
  var sheetId = 'abcd1234'
  var sheetName = 'シート1'
  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName)

  //配列へシート全体を読み込む
  var values = sheet.getDataRange().getValues()
  
  //男性と女性を合算して行の末尾に列を追加
  for (var i in values) {
    values[i].push(values[i][1] + values[i][2])
  }

  //ヘッダー行の追加
  values.unshift('グループ','男性','女性','合計')

  Logger.log(values)
}

配列のデータをシートに書き戻す

最後にシートに書き戻します。シートにデータを書き込むには、書き込む範囲を getRange() で指定する必要があります。

getRange(開始行, 開始列, 行数, 列数)

開始行と開始列は、それぞれ1行目と1列目です。

行数は、values.length です。列数は配列の最初の行の長さ values[0].length で求まります。

指定した範囲に配列 values　を書き込みます。

function myFunction() {
  //シートオブジェクトの取得
  var sheetId = 'abcd1234'
  var sheetName = 'シート1'
  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName)

  //配列へシート全体を読み込む
  var values = sheet.getDataRange().getValues()
  
  //男性と女性を合算して行の末尾に列を追加
  for (var i in values) {
    values[i].push(values[i][1] + values[i][2])
  }

  //ヘッダー行の追加
  values.unshift('グループ','男性','女性','合計')

  //配列をシートに書き込む
  var values = sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}

シートを確認すると、配列で計算した結果が書き込まれているのがわかります。

以上が、

シートから配列に読み込む
配列で処理をする
配列をシートに書き込む

という、一連の処理の簡単な説明となります。

GASの基礎を学べる参考図書

「詳解! Google Apps Script完全入門 [第3版]」は、プラグラミング初心者にわかりやすく説明されています。GASを最初に学ぶ一冊として良書です。

詳解! Google Apps Script完全入門 [第3版]

高橋宣成 (著)

楽天ブックスで購入す

Amazonで購入

Udemy オススメ講座

【新IDE対応】Google Apps Script（GAS）の基礎を完全習得

講師：事務職たらこ

Post Date：2021年11月9日　

Google Apps Script でWebスクレイピング

GAS(Google Apps Script)でWebサイトをスクレイピングする方法の説明です。

matchメソッドで正規表現を駆使してもスクレイピングをできなくはないですが、Parserライブラリを使った方が100倍簡単です。

GASでスクレイピングすれば、毎日何時にとか何時間毎にといったようにWebサイトから定期的に情報収集することができます。

GASライブラリとは

簡単にいってしまえば、他の人が作った関数を利用できる機能がライブラリです。面倒臭い作業も賢い人が作ったライブラリを使えば自分でプログラミングする必要はなくなります。

Parserライブラリを導入する

Parserは、スクレイピングをするときにとても便利なライブラリで、下記で公開されています。

https://script.google.com/home/projects/1Mc8BthYthXx6CoIz90-JiSzSafVnT6U3t0z_W3hLTAX5ek4w0G_EIrNw/edit

ライブラリを使用するときには、スクリプトIDが必要となりますので、

1Mc8BthYthXx6CoIz90-JiSzSafVnT6U3t0z_W3hLTAX5ek4w0G_EIrNw

の部分をコピーしてください。

ライブラリの追加方法

スクリプトエディタを開いてライブラリの「＋」をクリックします。

ライブラリ追加画面となりますので、スクリプトIDをペーストして、検索をクリックして追加すればParserライブラリが利用可能となります。

ParserのスクリプトIDを入力
検索をクリック
追加をクリック

HTMLデータを取得する

毎日データが変わる 12星座運勢ランキング - Yahoo!占いを使って説明していきます。

先ずは、HTMLデータを取り込みます。

HTMLを取り込むときの注意点は文字コードとして何が使われているかです。文字コードを指定しないと文字化けしてしまいます。

Yahoo!占いで使われている文字コードは "euc-jp” です。<head>から</head>に charsetの記載がありますので探してみてください。

ちなみに、このブログである「象と散歩」の文字コードは "UTF-8” です。

GASでHTMLデータを取得するには UrlFetchAppクラスを使いますが、文字コードはgetContentTextで指定します。

function myFunction() {
  var url = "https://fortune.yahoo.co.jp/12astro/ranking.html" // Yahoo!占い
  var html = UrlFetchApp.fetch(url).getContentText('euc-jp')

  Logger.log(html)
}

これでhtmlに格納されたHTMLデータが出力されます。

Parserライブラリの使い方

Parserの書き方は簡単で、抽出したいデータの中にある開始文字列と終了文字列に挟まれている文字列を抽出できます。

Parser.data(‘抽出データ').from(‘開始文字列').to(‘終了文字列').build()

開始と終了に指定する文字列はhtmlタグである必要はありません。

また最後のbuild()関数は、最初に見つけたひとつだけを抽出する場合で、複数のデータを抽出するのであれば、iterate()を使います。

Yahoo!占いのタイトルと更新日を抽出する

では、早速 Yahoo!占い 12星座運勢ランキングからタイトルと更新日を取得してみます。「12星座運勢ランキング」というのは他のところからも取得できますが、画像で指定されているところから抽出してみます。

HTMLソースでは下記の部分となります。

「12星座運勢ランキング」を取得するために、直前の文字列 alt=" と直後の文字列 "> を指定します。

更新日には、開始と終了に <p class="txt"> と </p>を指定します。

function myFunction() {
  var url = "https://fortune.yahoo.co.jp/12astro/ranking.html" // Yahoo!占い
  var html = UrlFetchApp.fetch(url).getContentText('euc-jp')

  var title = Parser.data(html).from('alt="').to('">').build()
  var lastUpdated = Parser.data(html).from('<p class="txt">').to('</p>').build()

  Logger.log('タイトル= ' + title)
  Logger.log('更新日　= ' + lastUpdated)
}

実行結果をみるとタイトルが正しく抽出されていません。

タイトル= Yahoo!占い" width="177" height="34

更新日　= 2021年11月6日（土）

前述したように build() は、最初に見つけたものとなるので、

が、先にマッチしてこの部分が抽出されたようです。

もう少し、開始文字列を多くして、他で一致しないようにします。

function myFunction() {
  var url = "https://fortune.yahoo.co.jp/12astro/ranking.html" // Yahoo!占い
  var html = UrlFetchApp.fetch(url).getContentText('euc-jp')

  var title = Parser.data(html).from('txt08.gif" alt="').to('">').build()
  var lastUpdated = Parser.data(html).from('<p class="txt">').to('</p>').build()

  Logger.log('タイトル= ' + title)
  Logger.log('更新日　= ' + lastUpdated)
}

今度は正しく取得できています。

タイトル= 12星座運勢ランキング

更新日　= 2021年11月6日（土）

複数の値を取得する

Yahoo!１２星座運勢ランキングのソースをみると、1位から12位まで表形式（tableタグ）で組み立てられています。

1行毎に薄い色と濃い色とで変えているので、簡単に記載するとこんな形です。

<table>

ランキング１位の項目

</tr>

ランキング2位の項目

</tr>

ランキング３位の項目

</tr>

</table>

<tr class="st01">で始まっているのが奇数の順位のもので、<tr class="st02">が偶数順位ですが、iterate()を使うと一気に配列として取得できます。

list_odd[5]でランク11位、list_even[5]でランク12位の情報が取得できます。

function myFunction() {
  var url = "https://fortune.yahoo.co.jp/12astro/ranking.html" // Yahoo!占い
  var html = UrlFetchApp.fetch(url).getContentText('euc-jp')

  //奇数順位の星座
  var list_odd = Parser.data(html).from('<tr class="st01">').to('</tr>').iterate()
  //偶数順位の星座
  var list_even = Parser.data(html).from('<tr class="st02">').to('</tr>').iterate()

  Logger.log(list_odd[5])
  Logger.log(list_even[5])
}

Parserを複数回利用して値を取得する

Parserを繰り返し使用することで目的の値を簡単に取得することができます。

上で取得した各星座のデータからランク、星座名、コメントを取得します。

スクレイピングのプログラミングのコツは、共通点を見つけるということです。共通のロジックでデータが取得できるかという観点でHTMLデータを俯瞰的に見てください。

順位の取得

詳細項目の取得方法について順位を使って説明します。

<td class="st01"> ... </td> の中にランキング情報があり、alt=" ... "> 中に順位がありますので、Paserを2回使って順位を取得します。以下がGASのコードとなります。

function myFunction() {
  var url = "https://fortune.yahoo.co.jp/12astro/ranking.html" // Yahoo!占い
  var html = UrlFetchApp.fetch(url).getContentText('euc-jp')

  //奇数順位の星座
  var list_odd = Parser.data(html).from('<tr class="st01">').to('</tr>').iterate()

  //ランクを取得
  var rank = Parser.data(contents).from('<td class="st01">').to('</td>').build()
  rank = Parser.data(rank).from('alt="').to('">').build()

  Logger.log(rank)
}

上記で「1位」と表示されます。

順位、星座名、コメントを取得する

下記が、Yahoo! 12星座運勢ランキングから、順位、星座名、コメントを取得するGASのコードとなりますので参考にしてみてください。

function myFunction() {
  var url = "https://fortune.yahoo.co.jp/12astro/ranking.html" // Yahoo!占い
  var html = UrlFetchApp.fetch(url).getContentText('euc-jp')

  //奇数順位の星座
  var list_odd = Parser.data(html).from('<tr class="st01">').to('</tr>').iterate()
  //偶数順位の星座
  var list_even = Parser.data(html).from('<tr class="st02">').to('</tr>').iterate()

  for(var i=0; i<list_odd.length; i++) {
    //奇数順位の星座
    results_odd = parse(list_odd[i])
    Logger.log(results_odd)

    //偶数順位の星座
    results_even = parse(list_even[i])
    Logger.log(results_even)
  }
}

function parse(contents) {
  //ランクを取得
  var rank = Parser.data(contents).from('<td class="st01">').to('</td>').build()
  rank = Parser.data(rank).from('alt="').to('">').build()

  //星座名を取得
  var seiza = Parser.data(contents).from('<p class="seiza">').to('</p>').build()
  seiza = Parser.data(seiza).from('alt="').to('">').build()

  //コメントを取得
  var text = Parser.data(contents).from('<p class="ft01">').to('</p>').build()
  text = Parser.data(text).from('">').to('</a>').build();

  var results =[rank, seiza, text]
  return results
}

GASの基礎を学べる参考図書

「詳解! Google Apps Script完全入門 [第3版]」は、プラグラミング初心者にわかりやすく説明されています。GASを最初に学ぶ一冊として良書です。

詳解! Google Apps Script完全入門 [第3版]

高橋宣成 (著)

楽天ブックスで購入す

Amazonで購入

Udemy オススメ講座

【新IDE対応】Google Apps Script（GAS）の基礎を完全習得

講師：事務職たらこ

Post Date：2021年5月20日　

Googleドキュメントを使ってGASで差し込みメールを出す（HTMLメールも可）

GASでメールを送信をするのは簡単ですが、メール本文を整形するのが面倒です。

しかし、Googleドキュメントでメールのテンプレートを作れば簡単です。またメール本文の一部を変数として、送付する人の名前などを書き換える差し込み機能を使うこともできます。

テキストメールだけでなくHTMLメールでも同じようにGoogleドキュメントでテンプレートを作成して差し込みメールを出すことも可能です。

今回説明するのは、

Googleドキュメントでメールのテンプレートを作る
メール本文の一部を送信する人によって書き換える
HTMLメールの送信

についてです。

実際に送付されるHTMLメールはこんな感じです。赤字にしている部分が差し込みです。画像も添付しています。

テキストメールのサンプルは下記となります。テキストメールなので画像はなく、リンクも使えないのでURLの記載となります。

メールのテンプレート作成する

先ずは、Googleドキュメントでメールのテンプレートを作成します。

テキストメールのテンプレート

テキストメールは、見たまんまなのでとても簡単です。

赤字が差し込み用の変数です。

面談日のお知らせ

[value1]さん、面談の日程が確定しました。

日付　：[value2]

時間　：[value3]

詳細については、下記URLからご確認ください。

https://walking-elephant.blogspot.com/2021/05/shower-head.html

差し込みとするのが赤字の部分です。プログラムで判断できる文字列であれば何でも構いませんが、

[value1]　・・・　氏名
[value2]　・・・　日付
[value3]　・・・　時間

としています。

HTMLメールのテンプレート

続いて、HTMLメールのテンプレートとなります。橙色がHTMLタグです。

<h2>面談日のお知らせ<h2>

<p><strong>[value1]</strong>さん、面談の日程が確定しました。</p>

<ui>

<li>日付　：<span style="color: red">[value2]</span></li>

<li>時間　：<span style="color: red">[value3]</span></li>

</ui>

<p>詳細については、<a href="https://walking-elephant.blogspot.com/2021/05/shower-head.html">こちら</a> からご確認ください。</p>

GASでメールを送信する

サンプルプログラムは、下記の３パートから構成されています。

Googleドキュメントを読み込む
読み込んだコンテンツから特定文字列を置換する
HTMLメールを送信する

HTMLメールで送信しますが、HTMLメールが受信できない環境であればテキストメールが表示されます。

サンプルコードは下記になります。

function testMail() {
/***************************************************
* Googleドキュメントを読み込む
***************************************************/
  //GoogleドキュメントのID
  var textDocId = '...1234567890...'
  var htmlDocId = '...abcdefghij...'

  //テキストメール テンプレートの取得
  var txtDoc = DocumentApp.openById(textDocId)
  var txtTemplate = txtDoc.getBody().getText()

  //HTMLメール テンプレートの取得  
  var htmlDoc = DocumentApp.openById(htmlDocId)
  var htmlTemplate = htmlDoc.getBody().getText()

/***************************************************
* メール本文の特定文字列を置換する
***************************************************/
  //変数の設定
  var var1 = 'タカハシ'
  var var2 = '2012年5月14日（金）'
  var var3 = '13:00'

  //プレーンテキスト内の変数を置換
  var bodyText = txtTemplate.replace('[value1]',var1).replace('[value2]',var2).replace('[value3]',var3)

  //HTML内の変数を置換
  var bodyHtml = htmlTemplate.replace('[value1]',var1).replace('[value2]',var2).replace('[value3]',var3)
  
/***************************************************
* HTMLメールを送信する
***************************************************/
  var mailTo = 'sample_mail@gmail.com'
  var subject = 'テストメール'

  GmailApp.sendEmail(
    mailTo,
    subject,
    bodyText,
    {
      htmlBody:bodyHtml
    }
  )
}

Googleドキュメントを読み込む

Googleドキュメントで作成したテンプレートをIDを指定して読み込みますが、６行目がテキストメール、７行目がHTMLメールのGoogleドキュメントのIDとなります。

5. //GoogleドキュメントのID
6. var textDocId = '...1234567890...'
7. var htmlDocId = '...abcdefghij...'

Googleドキュメントの編集URLが下記の場合、赤字の部分がIDです。

https://docs.google.com/document/d/...1234567890.../edit

Googleドキュメントで作成したテンプレートの値に変更してください。

10行目でGoogleドキュメントで作成したテキストメールのテンプレートを上記のIDを指定して開いて、11行目で内容をテキストとして変数 txtTemplate に代入しています。

9. //テキストメールテンプレートの取得
10. var txtDoc = DocumentApp.openById(textDocId)
11. var txtTemplate = txtDoc.getBody().getText()

HTMLメールのテンプレートも同じように14-15行目で変数 htmlTemplate に代入します。

これで、Googleドキュメントの読み込みは完了です。

メール本文の特定文字列を置換する

差し込み部分の説明です。

21-23行目でメールに差し込む値を定義しています。

20. //変数の設定
21. var var1 = 'タカハシ'
22. var var2 = '2012年5月14日（金）'
23. var var3 = '13:00'

26行目がテキストメール・テンプレートから読み込んだ値の置換、29行目がHTMLメール・テンプレートから読み込んだ値の置換です。

25. //プレーンテキスト内の変数を置換
26. var bodyText = txtTemplate.replace('[value1]',var1),.....省略.....
27.
28. //HTML内の変数を置換
29. var bodyHtml = htmlTemplate.replace('[value2]',var1)......省略.....

replaceを使って文字列を置換しています。

Strings.replace('置換前の文字列','置換後の文字列')

変数をひとつにして説明すると、下記の例では、txtTemplate にある [value1] を探して細野に置換した内容を bodyText に代入しています。

var bodyText = txtTemplate.replace('[value1]','細野')

差し込みが多い場合には下記のように分割することもできます。

var bodyText = txtTemplate.replace('[value1]',var1)
var bodyText = bodyText.replace('[value2]',var2)
var bodyText = bodyText.replace('[value3]',var3)

HTMLメールを送信する

メールを送信するのには GmailApp.sendEmail を使います。

GmailApp.sendEmail(送付先メアド, メールタイトル, 本文, {オプション})

34-35行目で mailTo に送信先アドレスを、subject にメールタイトルを代入しています。

自分のメールアドレスに変更してください。

34. var mailTo = 'sample_mail@gmail.com'
35. var subject = 'テストメール'

37-44行目がメール送信部分です。

HTMLメールを送信するので、オプション部に htmlBody:'HTMLメール本文' を指定し、本文にはHTMLが表示できない場合のテキスト文を指定しています。

こうしておけば、HTMLメールを表示できない環境の場合には、テキストメールが表示されます。

37. GmailApp.sendEmail(
38. mailTo,
39. subject,
40. bodyText,
41. {
42. htmlBody:bodyHtml
43. }
44. )

テキストメールを送信する

テキストメールとして送信する場合は、サンプルプログラムのオプション部を削除して下記のように変更します。

GmailApp.sendEmail(
  mailTo,
  subject,
  bodyText
)

シートの文字装飾された文字列をHTMLメールで送信する

コメントに質問を受けたのでスプレッドシートから値と文字装飾（色、太文字）を読み込んでHTMLメールを送信するサンプルプログラムを作成しました。

読み込むシートには、A1セルに赤字太文字で"象と散歩"と入力しています。

プログラムでHTMLタグを生成する

セルの文字色は、.getFontColor()で取得できます。太字かどうかは、.getFontWeight()でわかります。戻り値が、boldなら太字、太文字でなければnormalです。

次にHTMLの生成ですが、セルの文字が太文字かを判断して、太文字であれば<strong>タグで囲みます。文字色は必ずセットされているので、<p style='color:#カラーコード>で括ります。

文字色は必ずセットされるので、装飾文字のパターンは太字の有無の2パターンですが、文字色が「黒」か「他の色」かの4パターンで生成されるHTMLを下表に示します。

字幅	太字	文字色	HTML
象と散歩	なし	黒	<p style='color:#000000'>象と散歩</p>
象と散歩	なし	赤	<p style='color:#ff0000'>象と散歩</p>
象と散歩	太字	黒	<p style='color:#000000'><strong>象と散歩</strong></p>
象と散歩	太字	青	<p style='color:#0000ff'><strong>象と散歩</strong></p>

下記がサンプルコードとなります。

function sendFormattingEmail() {
  // シート名を指定
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート1")
  // セルの内容を取得
  let cellValue = sheet.getRange("A1").getValue()
  // セルの文字色を取得
  let cellFontColor = sheet.getRange("A1").getFontColor()
  // セルの文字が太字かどうかを判定
  let cellIsBold = sheet.getRange("A1").getFontWeight() === "bold"

  Logger.log(cellValue)
  Logger.log(cellFontColor)
  Logger.log(cellIsBold)

  // 宛先メールアドレス
  let mailTo  = "sample_email@gmail.com"
  // メールの件名
  let subject = "文字色と太文字を指定したメール"

  // セルの値を取得
  let body = cellValue
  // 太字の場合はstrongタグで囲む
  if (cellIsBold) {
    body = "<strong>" + body + "</strong>"
  }
  // 文字色をstyle属性で指定
  body = "<p style='color:" + cellFontColor + "'>" + body + "</p>"

  Logger.log(body)

  // HTML形式のメールを送信
  GmailApp.sendEmail(mailTo,subject,'',{htmlBody:body})
}

上記を実行すると、セルに設定された文字装飾（文字色と太字有無）で、HTMLメールを送信することができます。（2023.7.23追記）

GASの基礎を学べる参考図書

「詳解! Google Apps Script完全入門 [第3版]」は、プラグラミング初心者にわかりやすく説明されています。GASを最初に学ぶ一冊として良書です。

詳解! Google Apps Script完全入門 [第3版]

高橋宣成 (著)

楽天ブックスで購入す

Amazonで購入

Udemy オススメ講座

【新IDE対応】Google Apps Script（GAS）の基礎を完全習得

講師：事務職たらこ

Post Date：2021年5月4日　

Googleスプレッドシートの更新された値を知る

Googleスプレッドシートの特定セルの値が更新されたらかを知る方法についての説明です。

スプレッドシートでのプロジェクト計画管理については、スプレッドシートでプロジェクト計画を作成する方法 - Google Workspace ラーニングセンターに説明があります。またこの中で更新を通知する方法として、通知を追加するでスプレッドシートの通知設定についての説明もあります。

これだけでも便利なのですが、ステータスが更新されたときなど特定のセルが更新されたときだけ通知をするなどは GoogleAppsScript での作成が必要となります。

onEdit()

GoogleAppsScriptではトリガーの設定をしなくても、特定のイベントに応じて動くシンプルトリガーという関数があります。詳細の説明は、Simple Triggers | Apps Script | Google Developers にありますが、スプレッドシートでは下記の３つの関数が使えます。

関数名	イベント
onOpen(e)	スプレッドシートを開いたとき
onEdit(e)	セルの値を変更したとき
onSelectionChange(e)	範囲選択をしたとき

onEdit を使えば、シートの値が変更されたときに実行されて、引数として変更された内容を取得することができます。慣習としてイベント時の引数として e を使っていますが、onEdit(parm) とすれば、parm を引数として使うことができます。引数で何が取得できるかの詳細は、Event Objects の Editに記載があります。

単一セルの変更なら、

e.oldValue
e.value

で、変更前の値、変更後の値も取得できますが、コピペで複数セルの値が変更された場合には使えません（残念）。但し、e.range で変更された場所を確認することができるので、行と列を指定して変更後の値であれば取得することができます。

変更されたシート名と変更内容を取得する

下記は、変更されたシート名と変更された範囲、変更内容を表示する簡単な例です。

sourceはスプレッドシートオブジェクトで、e.source.getActiveSheet() で変更イベントが発生したシートを取得できます。

function onEdit(e) {
  var sheet = e.source.getActiveSheet()
  console.log('シート名　 =', sheet.getName())
  console.log('変更範囲　 =', e.range)
  console.log('変更前の値 =', e.oldValue)
  console.log('変更後の値 =', e.value)
}

シート1のA1セル（空白セル）にaaaと入力してログを確認すると、下記のようにログが出力されます。

シート名 = シート1
変更範囲 = {columnEnd:1, columnStart:1, rowEnd:1, rowStart:1}
変更前の値 = undefined
変更後の値 = aaa

e.range の中身は、

columnEnd（終了列）
columnStart（開始列）
rowEnd（終了行）
rowStart（開始行）

となっています。順番が不思議な感じがしますが、全て1なので1行目1列の値が更新されたことを示しています。

※rangeを出力すると、"toString:" から "rowStart:" までの長い出力となります。次例のように必要な項目だけを指定すると出力内容を特定できます。(2022/05/05 追記)

function onEdit(e) {
  var sheet = e.source.getActiveSheet()
  console.log('シート名　 =', sheet.getName())
  console.log('変更範囲(開始行)　 =', e.range.rowStart)
  console.log('変更範囲(終了行)　 =', e.range.rowEnd)
  console.log('変更範囲(開始列)　 =', e.range.columnStart)
  console.log('変更範囲(終了列)　 =', e.range.columnEnd)
  console.log('変更前の値 =', e.oldValue)
  console.log('変更後の値 =', e.value)
}

変更前の値はないので、undefined となって、変更後の値は、入力した aaa がセットされています。

次にA1セルの値を aaa から bbb に変更すると、ログは下記のように出力されて aaa が bbb に変更されたことがわかります。

シート名　 = シート1
変更範囲　 = {columnEnd:1, columnStart:1, rowEnd:1, rowStart:1}
変更前の値 = aaa
変更後の値 = bbb

では次に B1に AAA, B2に BBB がある状態で、この値をA1-A2にコピペします。

複数セルが更新されたので、更新前も更新後の値も取得できません。変更の範囲は rowEnd が2になったので、1から2行目の1列目の値（A1,A2）が更新されたことがわかります。

シート名　 = シート1
変更範囲　 = {columnEnd:1, columnStart:1, rowEnd:2, rowStart:1}
変更前の値 = undefined
変更後の値 = undefined

【注意】シンプルトリガーは、スクリプトエディターから手動でも実行できますが、手動で実行した場合には当然ですが、引数には何も入っていません。動作を確認する場合にはシートの値を変更して確認してください。

複数範囲の変更された値を取得する

前のサンプルコードで rangeでどの範囲が変更されたかは行と列の開始と終了でわかるのでこの値を使って変更内容を表示させる例となります。

変更行と変更列で二重ループにして個々の値を取得します。

3-6行目は、変更開始/終了行、変更開始/終了列をそれぞれ取得しています。

また10行目の sheet.getRange(row, col).getValue() で指定した行と列のセルの値を取得しています。

function onEdit(e) {
  var sheet = e.source.getActiveSheet()
  var rowStart = e.range.rowStart //変更開始行
  var rowEnd = e.range.rowEnd //変更終了行
  var columnStart = e.range.columnStart //変更開始列
  var columnEnd = e.range.columnEnd //変更終了列

  //変更行の確認
  for (var row = rowStart; row <= rowEnd; row++) {
    //変更列の確認
    for (var col = columnStart; col <= columnEnd; col++) {
      console.log(row + '行' + col + '列目 =', sheet.getRange(row, col).getValue())
    }
  }
}

上の例と同じく B1に AAA, B2に BBB がある状態で、この値をA1-A2にコピペします。ログを確認すると、

1行1列目= AAA
2行1列目= BBB

A1セル（1行1列目）に AAA が、A2セル（2行1列目）がセットされたことがわかります。

onEditの限界

とってな便利なonEditですが、シンプルエティターとしての限界があります。例えば、特定のセルが更新された場合にメールを送信するなどをしようとすると、

Exception: スクリプトにはその操作を行う権限がありません。

と、メール送信の権限がないと言われてエラーとなってしまいます。

メールやチャットルールに投稿するなどをする場合には、onEdit関数ではなく、トリガーを設定する必要があります。

シート変更時に関数を実行する

トリガーを選択すると、スプレッドシートの起動時、変更時などに特定の関数を実行することができます。スプレッドシートで使えるイベントは下記の４種類です。

イベントの種類	関数が実行される条件
起動時	スプレッドシートを起動したとき
編集時	スプレッドシートを編集したとき
変更時	スプレッドシートを変更したとき
フォーム送信時	フォームを送信したとき

編集時と変更時の違い

「編集時」では、行の追加・削除、列の追加・削除を含めてスプレッドシートに何らかの変更があった場合に起動されますが、「変更時」では、セルの値が変更された場合だけ起動されます。

注意事項は、トリガーで起動した場合にパラメータで値が取得できるのは「編集時」だけです。インベントの種類を「変更時」にすると、下記のようにエラーになってしまいますので、

TypeError: Cannot read property 'rowStart' of undefined

イベントの種類は「編集時」を選択してください。

下記の例は、変更行と変更列の二重ループではなく、getRangeで変更された値を配列として取得する方法です。

function modifiedSells(e) {
  var sheet = e.source.getActiveSheet()

  //変更行数
  var numberOfRows = e.range.rowEnd - e.range.rowStart +1
  //変更列数
  var numberOfClums = e.range.columnEnd - e.range.columnStart+1
  //変更された値を取得
  var values = sheet.getRange(e.range.rowStart, e.range.columnStart, numberOfRows, numberOfClums).getValues()
  
  console.log(values);
}

getRangeは、

Sheetオブジェクト.getRange（開始行, 開始列, 行数, 列数）

で範囲を指定して複数の値を取得できます。

9行目で変更された行数を11行目で変更された列数を求めて、変更された値を Values に格納しています。

例えば、コピペによる複数行（２行）、複数列（2列）の値が更新された場合は、下記のように表示されます。

[ [ 'AAA', 'BBB' ], [ 'CCC', 'DDD' ] ]

最初の行の変更値は、values[0][0]、values[0][1]で（AAAとBBB）　で、

次の行の変更値が、values[0][0]、values[0][1]で（CCCとDDD）となります。

Post Date：2021年3月6日　

Googleフォームの選択肢をシートのデータで更新する

GAS（Google Apps Script）で Googleフォームのプルダウン、チェックボックス、ラジオボタンの選択肢をスプレッドシートの値で更新する方法についての説明です。

同じフォームを長く使っていて、時々フォーム上の選択肢を更新するような場合には、シート上で選択肢の値が管理できるようになるので便利です。

このサンプルコードでできることは、

シート上の値で選択肢の内容を更新
プルダウン、チェックボックス、ラジオボタンに対応
更新できるの質問は1個（質問番号を指定）

です。

フォームとシートの作成（準備）

プルダウン、チェックボックス、ラジオボタンの選択肢の更新をするので、下記の３つの質問を作成します。選択肢はスプレッドシートから更新するので設定不要です。

商品名（プルダウン）
商品名（チェックボックス）
商品名（ラジオボタン）

次に選択肢をシートで管理するために、シートのA列1行目にタイトルを入れて、2行目からデータを入れます。

これで準備は完了です。

次からがGoogle Apps Scriptの例になります。

プルダウンの選択肢を更新する

質問番号を指定して質問の選択肢を更新するという簡単なサンプルです。このサンプルコードはプルダウンの質問項目の選択肢用なので、指定した質問番号がチェックボックス、ラジオボタンの場合にはエラーとなります。

上記フォーム例では、プルダウンは1番目の質問です。

下記サンプルコードをスクリプトエディタのコードに貼り付けてください。

function updateFormList() {
  //フォームの質問番号
  var questionNo = 1
  //フォームID
  var formId  = '1234567890abcdefghijklmnopqrstuvwxyz'
  //シートID
  var sheetId = 'zyxwvutsrqponmlkjihgfedcba0987654321'
  //シート名
  var sheetName = 'List'
  //データ配置列名
  var colName = 'A'
  //データ開始行
  var rowNum = 2
 
  //フォームの取得
  var form = FormApp.openById(formId)
  //全質問項目を取得
  var items = form.getItems()
  //questionNoで指定した質問項目を指定（配列は0から始まるので-1）
  var item = items[questionNo-1]

  //スプレッドシートを取得
  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName)
  //シートの最終行を取得
  var lastRow = sheet.getLastRow()

  //リスト内容の取得
  var arrList = sheet.getRange(colName + rowNum + ':' + colName + lastRow).getValues()

  //選択肢をフォームに反映
  item.asListItem().setChoiceValues(arrList)
}

フォームの質問番号

更新する質問番号を3行目の変数（questionNo）に代入します。上記のGoogleフォームの例ではプルダウンの質問は1番目なので1を指定します。

2. //更新するフォームの質問番号
3. var questionNo = 1

実際には配列として取得されるので1番目の質問項目は、配列の0番目となるので、16行目で questionNo-1 としています。

19. //questionNoで指定した質問項目を指定（配列は0から始まるので-1）
20. var item = items[questionNo-1]

フォームIDとシートID

GoogleフォームとGoogleスプレッドシートは、IDで取得しています。

フォームIDについては、象と散歩: GASでGoogleフォームの値を取得する（フォームを指定）でも説明していますが、再掲となります。

Googleフォームの編集URLが下記の場合、フォームIDは、赤字の部分です。

https://docs.google.com/forms/d/1234567890abcdefghijklmnopqrstuvwxyz/edit

シートIDも同様でに赤字の部分となります。

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

5行目にシートIDを7行目にシートIDを指定していますが、作成したフォームとスプレッドシートのURLを確認して書き換えてください。

4. //フォームID
5. var formId = ‘1234567890abcdefghijklmnopqrstuvwxyz'
6. //シートID
7. var sheetId = 'zyxwvutsrqponmlkjihgfedcba0987654321'

シート名

9行目にスプレッドシートのシート名を指定します。今回のサンプルではシート名をListとしています。

8. // シート名
9. var sheetName = 'List'

データ開始位置

10-13行目でシートのデータ開始を指定します。例ではA列2行目からが選択肢のデータとなりますので、11行目の colName にAを、13行目の rowNum に2を指定します。

10. //データ配置列名
11. var colName = 'A'
12. //データ開始行
13. var rowNum = 2

プルダウン選択肢の更新

28行目で、指定したデータ開始位置（A列2行目）からデータがある最終行までを arrList に格納しています。

シート上の商品A, 商品B, 商品C が、

arrList[0] 商品A

arrList[1] 商品B

arrList[2] 商品C

として格納されます。

そして、31行目で、この配列の値でフォームの質問番号に該当する質問の選択肢（プルダウン）を更新しています。

30. //選択肢を更新する
31. item.asListItem().setChoiceValues(arrList)

asListItem() はプルダウン項目用なので questionNo で指定した質問がプルダウンでない場合にはエラーとなるので注意してください。item.getType() で質問のタイプを確認することができます。プルダウンの質問のタイプは LIST です。

updateFormList を実行します。

ログには実行完了とだけ表示されますが、フォームを参照すると、

シート上のデータでプルダウンの選択肢が更新されています。

チェックボックスの選択肢を更新する

スプレッドシート上にある選択肢を配列に入れて、フォームの選択肢を更新する処理は基本的には同じです。プラダウンのサンプルを実行されたのであれば変更箇所は２箇所だけです。

フォームの質問番号

更新する質問番号を3行目の変数（questionNo）に代入しますが、上記のGoogleフォームの例ではチェックボッククスの質問は2番目なので2を指定します。

2. //更新するフォームの質問番号
3. var questionNo = 2

チェックボックス選択肢の更新

31行目をチェックボックス項目用の asCheckboxItem() に変更します。またチェックボックスは、選択肢に「その他」を設定できますが、showOtherOption() でその他の有無を指定します。

showOtherOption(true) その他あり
showOtherOption(false) その他なし

その他の選択肢を追加する場合の例となります。

30. //選択肢を更新する
31. item.asCheckboxItem( ).setChoiceValues(arrList).showOtherOption(true)

updateFormList を実行すると、商品A ,B,C とその他の選択肢が追加されます。

チェックボックスを更新するコード全体はこちらです。

function updateFormList() {
  //フォームの質問番号
  var questionNo = 2
  //フォームID
  var formId  = '1234567890abcdefghijklmnopqrstuvwxyz'
  //シートID
  var sheetId = 'zyxwvutsrqponmlkjihgfedcba0987654321'
  //シート名
  var sheetName = 'List'
  //データ配置列名
  var colName = 'A'
  //データ開始行
  var rowNum = 2
 
  //フォームの取得
  var form = FormApp.openById(formId)
  //全質問項目を取得
  var items = form.getItems()
  //questionNoで指定した質問項目を指定（配列は0から始まるので-1）
  var item = items[questionNo-1]

  //スプレッドシートを取得
  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName);
  //シートの最終行を取得
  var lastRow = sheet.getLastRow()

  //リスト内容の取得
  var arrList = sheet.getRange(colName + rowNum + ':' + colName + lastRow).getValues()

  //選択肢をフォームに反映
  item.asCheckboxItem( ).setChoiceValues(arrList).showOtherOption(true)
}

ラジオボックスの選択肢を更新する

続いて、ラジオボックの場合となります。こちらも変更箇所は２箇所だけです。

フォームの質問番号

ラジオボックスの質問は3番目なので questionNo に 3 を代入します。

2. //更新するフォームの質問番号
3. var questionNo = 3

ラジオボックス選択肢の更新

35行目をラジオボックス用の asMultipleChoiceItem() に変更します。今回は、「その他」の選択肢を付けない showOtherOption(false) とします。

30. //選択肢を更新する
31. item.asMultipleChoiceItem().setChoiceValues(arrList).showOtherOption(false)

updateFormList を実行すると、商品A ,B,C がラジオボックの選択肢として追加されます。

ラジオボックスを更新するコード全体はこちらです。

function updateFormList() {
  //フォームの質問番号
  var questionNo = 3
  //フォームID
  var formId  = '1234567890abcdefghijklmnopqrstuvwxyz'
  //シートID
  var sheetId = 'zyxwvutsrqponmlkjihgfedcba0987654321'
  //シート名
  var sheetName = 'List'
  //データ配置列名
  var colName = 'A'
  //データ開始行
  var rowNum = 2
 
  //フォームの取得
  var form = FormApp.openById(formId)
  //全質問項目を取得
  var items = form.getItems()
  //questionNoで指定した質問項目を指定（配列は0から始まるので-1）
  var item = items[questionNo-1]

  //スプレッドシートを取得
  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName)
  //シートの最終行を取得
  var lastRow = sheet.getLastRow()

  //リスト内容の取得
  var arrList = sheet.getRange(colName + rowNum + ':' + colName + lastRow).getValues()

  //選択肢を更新する
  item.asMultipleChoiceItem().setChoiceValues(arrList).showOtherOption(false)
}

更新する質問のタイプを確認しよう（22.2.11追記）

何件かご質問もいただきましたが、上記のサンプルで実行エラーとなる場合の多くは、質問番号で指定した番号の質問タイプと利用するメソッドが違う場合です。

下記のエラーが出力される場合が、

エラー Exception: Invalid conversion for item type: XXXXX

更新しようとする質問のタイプとメソッドが一致していない場合に出力されるエラーで、type:XXXX には、指定した質問のタイプが表示されます。

Gooleフォームで設問に指定できる項目とタイプについて下表にまとめています。

質問項目	タイプ	更新に利用するメソッド
プルダウン	LIST	asListItem()
チェックボックス	CHECKBOX	asCheckboxItem()
ラジオボタン	MULTIPLE_CHOICE	asListItem()
記述式	TEXT	-
段落	PARAGRAPH_TEXT	-
ファイルのアップロード	FILE_UPLOAD	-
均等メモリ	SCALE	-
選択式（グリッド）	GRID	-
チェックボックス（グリッド）	CHECKBOX_GRID	-
日付	DATE	-
時刻	TIME	-
セクション	PAGE_BREAK	-
タイトル	SECTION_HEADER	-
画像	IMAGE	-
動画	VIDEO	-

セクション、写真、動画なども質問となるので指定する番号に注意してください。

質問タイプを確認するコード

Loggerで指定した質問番号のタイプをログに出力するコードを追加してみてください。

19. //questionNoで指定した質問項目を指定（配列は0から始まるので-1）
20. var item = items[questionNo-1]

下記のコードを21行目以降に追加します。

  Logger.log('質問総数：' + items.length)
  Logger.log('質問番号：' + questionNo)
  Logger.log('タイトル：' + item.getTitle())
  Logger.log('タイプ：' + item.getType())

下記のように、質問総数、指定した質問番号（questionNo）、質問のタイトル、タイプが出力されます。

情報質問総数：3
情報質問番号：1
情報タイトル：商品名（プルダウン）
情報タイプ：LIST

下記は、更新はせずに、指定した質問番号のタイプを確認するサンプルコードです。

function updateFormList() {
  //フォームの質問番号
  var questionNo = 1
  //フォームID
  var formId  = '1234567890abcdefghijklmnopqrstuvwxyz'
 
  //フォームの取得
  var form = FormApp.openById(formId)
  //全質問項目を取得
  var items = form.getItems()
  //questionNoで指定した質問項目を指定（配列は0から始まるので-1）
  var item = items[questionNo-1]

  Logger.log('質問総数：' + items.length)
  Logger.log('質問番号：' + questionNo)
  Logger.log('タイトル：' + item.getTitle())
  Logger.log('タイプ：' + item.getType())
}

スプレッドシートのメニューから実行する

下記のコードを追加すると、スプレッドシートを開いたときに、メニューに "Form選択肢更新" が追加されます。

”商品名更新” を選択すると updateFormList() がスクリプトエディタを開かなくても実行できます。

function onOpen(){ 
  SpreadsheetApp
    .getActiveSpreadsheet()
    .addMenu('Form選択肢更新', [
      {name: '商品名更新', functionName: 'updateFormList'},
    ]);
}

GASの基礎を学べる参考図書

「詳解! Google Apps Script完全入門 [第3版]」は、プラグラミング初心者にわかりやすく説明されています。GASを最初に学ぶ一冊として良書です。

詳解! Google Apps Script完全入門 [第3版]

高橋宣成 (著)

楽天ブックスで購入す

Amazonで購入

Udemy オススメ講座

【新IDE対応】Google Apps Script（GAS）の基礎を完全習得

講師：事務職たらこ

登録: 投稿 (Atom)

Translate

結合できるPDFファイル

mergePdfs()関数を使う

mergePDFs()関数の修正

12行目

21行目

23行目

mergePDFs()関数を呼び出す

スプレッドシートのUI機能で汎用的にする

スプレッドシートにメニューに追加する

ダイアログ

GASの基礎を学べる参考図書

変数と定数

変数の利用可能範囲（スコープ）

関数で定義した変数はif, for のブロック内でも使える

再宣言

var, let, constの違い（まとめ）

関数間で値の引き渡し

グローバル変数

GASの基礎を学べる参考図書

URLの取得

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

セル範囲はRANGEで指定する

A1形式で範囲を取得する

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

変更範囲の確認

メールを送信する

GASの基礎を学べる参考図書

Excel to Google Sheets には Drive APIが必要

Drive APIサービスを追加する

Excelファイルを指定してスプレッドシートに変換する

ExcelファイルとフォルダのID

サンプルコード（Excelファイル指定）

変換後に処理を続ける

フォルダにあるExcelファイルを一括変換する

GASの基礎を学べる参考図書

なぜ配列を使うのか

二次元配列とは

サンプルデータで配列の中身を確認する

シートのデータを配列に読み込む

ヘッダー行の削除 shift()メソッド

配列の全行処理は for ... in が便利

末尾に列を追加する push()メソッド

ヘッダー行の追加 unshift()メソッド

配列のデータをシートに書き戻す

GASの基礎を学べる参考図書

GASライブラリとは

Parserライブラリを導入する

ライブラリの追加方法

HTMLデータを取得する

Parserライブラリの使い方

複数の値を取得する

Parserを複数回利用して値を取得する

順位の取得

順位、星座名、コメントを取得する

GASの基礎を学べる参考図書

メールのテンプレート作成する

テキストメールのテンプレート

HTMLメールのテンプレート

GASでメールを送信する

Googleドキュメントを読み込む

メール本文の特定文字列を置換する

HTMLメールを送信する

テキストメールを送信する

シートの文字装飾された文字列をHTMLメールで送信する

プログラムでHTMLタグを生成する

GASの基礎を学べる参考図書

onEdit()

変更されたシート名と変更内容を取得する

複数範囲の変更された値を取得する

onEditの限界

シート変更時に関数を実行する

編集時と変更時の違い

フォームとシートの作成（準備）

プルダウンの選択肢を更新する

フォームの質問番号

フォームIDとシートID

シート名

データ開始位置

プルダウン選択肢の更新