Google Apps Scriptでフォーム機能からメール送信でエラーが出る

Question

修正したいこと

エラーメールが来ないように修正したい。

現在の状態

下記のサイトなどを参考に、フォームを送信すると自動返信メールが送られる設定をしたところ、

http://creazy.net/2011/03/google_form_mailsend.html

送信者にも、フォーム管理側にも想定したメールは届くのですが、
もう１通、フォーム管理側にエラーメールが届くようになりました。
想定通りの動きをしているのに、エラーメールが来る原因がわかりません。

エラーメッセージ

件名：【失敗】Googleフォームからメール送信中にエラーが発生
本文：Cannot read property 'getLastRow' of null

エラーメッセージを送っていコード部分

catch(e){
MailApp.sendEmail(admin, "【失敗】Googleフォームからメール送信中にエラーが発生", e.message);
}

やってみたこと

もしかして、
var sheet = SpreadsheetApp.getActiveSheet();
と
var rows = sheet.getLastRow();
の間にgetSheetName("フォームのタイトル（日本語）")の処理が必要だったのかもしれないと思いいれて見ましたが、
別のエラー
"パラメータ（String）が SpreadsheetApp.Sheet.getSheetName のメソッドのシグネ
チャと一致しません。"
と出ました、、

また、よくわからなかったのが、シート名は回答フォームからスプレッドシートを開いた時のシートのタイトルだと思ったのですが、もしかして違うのでしょうか、この質問をみたのですが、
https://teratail.com/questions/189952
sheetNameが何を指すのかがわからず、、

色々理解しきれていませんが、アドバイスを頂けると助かります。

---

ソースコード全体

function sendMailGoogleForm() {
 Logger.log('sendMailGoogleForm() debug start');

 //------------------------------------------------------------
 // 設定エリアここから
 //------------------------------------------------------------

 // 件名、本文、フッター
 var subject = "[メッセージ]";
 var body
 = "メッセージ\n\n"
 + "------------------------------------------------------------\n";
 var footer
 = "------------------------------------------------------------\n\n"
 + "";

 // 入力カラム名の指定
 var NAME_COL_NAME = '名前';
 var MAIL_COL_NAME = 'メールアドレス';


 // メール送信先
 var admin = "メールアドレス"; // 管理者（必須）
 var sendername = "送信者名";//送信者名（必須）
 var cc = ""; // Cc:
 var bcc = admin; // Bcc:
 var reply = admin; // Reply-To:
 var to = ""; // To: （入力者のアドレスが自動で入ります）


 //------------------------------------------------------------
 // 設定エリアここまで
 //------------------------------------------------------------

 try{
   // スプレッドシートの操作
   var sheet = SpreadsheetApp.getActiveSheet();

   var rows = sheet.getLastRow();
   var cols = sheet.getLastColumn();
   var rg = sheet.getDataRange();
   Logger.log("rows="+rows+" cols="+cols);

   // メール件名・本文作成と送信先メールアドレス取得
   for (var i = 1; i <= cols; i++ ) {
   var col_name = rg.getCell(1, i).getValue(); // カラム名
   var col_value = rg.getCell(rows, i).getValue(); // 入力値
   body += "【"+col_name+"】\n";
   body += col_value + "\n\n";
   if ( col_name === NAME_COL_NAME ) {
   body = col_value+" 様\n\n"+body;
   }
   if ( col_name === MAIL_COL_NAME ) {
   to = col_value;
   }
   }
   body += footer;

   // 送信先オプション
   var options = {name: sendername};
   if ( cc ) options.cc = cc;
   if ( bcc ) options.bcc = bcc;
   if ( reply ) options.replyTo = reply;

   // メール送信
     if ( to ) {
       MailApp.sendEmail(to, subject, body, options);
     }else{
       MailApp.sendEmail(admin, "【失敗】Googleフォームにメールアドレスが指定されていません", body);
     }

 }catch(e){
   MailApp.sendEmail(admin, "【失敗】Googleフォームからメール送信中にエラーが発生", e.message);
 }
}

修正後（現状）

//sendMailGoogleForm04


function sendMailGoogleForm() {
 Logger.log('sendMailGoogleForm() debug start');

 //------------------------------------------------------------
 // 設定エリアここから
 //------------------------------------------------------------

 // 件名、本文、フッター
 var subject = "[件名]";
 var body
 = "本文\n\n"
 + "------------------------------------------------------------\n";
 var footer
 = "------------------------------------------------------------\n\n"
 + "フッター";

 // 入力カラム名の指定
 var NAME_COL_NAME = '名前';
 var MAIL_COL_NAME = 'メールアドレス';
 var SUBJ_COL_NAME = '件名';
 //メール除外カラム
 var EXCLUDE_COLS = ['ステータス','対応日時'];


 // メール送信先
 var admin = "メールアドレスm"; // 管理者（必須）

 // スプレッドシートID
// URLが「https://docs.google.com/spreadsheets/d/abc1234567/edit#gid=0」だったら「abc1234567」がID
var spread_sheet_id = 'ID';



 //------------------------------------------------------------
 // 設定エリアここまで
 //------------------------------------------------------------

 try{

   // スプレッドシートの操作

    var ss = SpreadsheetApp.openById(spread_sheet_id);
    var sh = ss.getSheetByName('form_ans');//シート名
    console.log("ファイル名",sh.getName());

    var rows = sh.getLastRow();
    console.log("rows:",rows);

    var cols = sh.getLastColumn();
    console.log("cols:",cols);

    var rg = sh.getDataRange();
    Logger.log("rows="+rows+"cols="+cols);

    //メール件名・本文作成と送信先メールアドレス取得
    var to = ""; //To:（入力者のアドレスが自動で入ります）
    for(var j = 1;j<=cols;j++){
      var col_name = rg.getCell(1,j).getValue(); //カラム名
      var col_value = rg.getCell(rows,j).getValue(); //入力値
      //メール用変換
      if(col_name === NAME_COL_NAME){
        body = col_value + "様\n\n"+body;
      }
      if(col_name === MAIL_COL_NAME){
        to = col_value;
      }
      if(col_name === SUBJ_COL_NAME){
        subject += col_value;
      }
      //日付フォーマットの変換
      //他にも変換したいカラムがある場合はこのif文をコピーしてカラム名・日付フォーマットを変更する
      if(col_name === 'タイムスタンプ'){
        col_value = Utilities.formatDate(col_value,"JST","yyyy-MM-dd HH:mm:ss");
      }
      //メール送信除外カラム
      if(EXCLUDE_COLS.length > 0){
        is_exclude = false;
        for(var k = 0;k<EXCLUDE_COLS.length;k++){
          if(col_name === EXCLUDE_COLS[k]){
            is_exclude = true;
            break;
          }
        }
        if(is_exclude){
          //除外カラムなのでスキップ
          continue;
        }
      }
      //メール本文に追加
      body += "["+col_name+"]\n";
      body += col_value + "\n\n";
    }

    body += footer;
    /*ユーザー宛送信*/
    //送信オプション
    var options = {};
    if(admin)options.replyTo = admin;
    //メール送信
    if(to){
      MailApp.sendEmail(to,subject,body,options);
    }else{
      MailApp.sendEmail(admin,"[失敗]Googleフォームにメールアドレスが指定されていません",body);
    }

    /*管理者宛送信*/
    //送信先オプション
    var options = {};
    if(to)options.replyTo = to;
    //連続で送るとエラーになるので1秒スリープ
    Utilities.sleep(1000);
    MailApp.sendEmail(admin,subject,body,options);


  }catch(e){
    MailApp.sendEmail(admin,"[失敗]Googleフォームからメール送信中にエラーが発生",e.message);
  }
}

Answer 1

var sheet = SpreadsheetApp.getActiveSheet();でアクティブなシートを取得していますが、これに失敗すると(アクティブなシートが見つからない場合)、sheetの値がnullになり、続く処理のvar rows = sheet.getLastRow();で提示されたエラーが発生します。

解決策は、スプレッドシートのオブジェクトをID指定で取得して、シートをシート名指定で取得すること、でしょうか。

var sheet = SpreadsheetApp.openById('スプレッドシートの実際のURLに書いてあるIDで置換').getSheetByName('実際のシート名で置換'); 

Comments

[Daregada]

後から気がついたんですが、質問者が参考にしているサイトも「この情報古いので新しい記事を書きました」という旨のことが書いてあり、新しい記事ではIDでスプレッドシートを取得(ただし、シートはgetActiveSheet()で取得)するようにコードが訂正されていました。ネットの情報を基に作業するときは、できるだけ新しい情報を参考にしてください、ということで。

[oyatsu8]

回答ありがとうございます。
今から試してみます。

[oyatsu8]

＞「この情報古いので新しい記事を書きました」という旨のことが書いてあり、新しい記事ではIDでスプレッドシートを取得

この記事自体が新しくしたものだと勘違いしていました。今から読んでみます。ありがとうございます。

[oyatsu8]

こちらの新しい方（http://creazy.net/2014/12/google_apps_script_form_mailsend_2014.html　）でやってみたのですが、やはり同じく　Cannot read property 'getLastRow' of null　というエラーが出ました。申し少し調べてみようと思います

[Daregada]

最初のコメントに書いたように、新しいほうもgetActiveSheetを使っているんですよ。私の回答のようにシート名でシートを取得するように修正してみてください。

[oyatsu8]

すみません、どうにもわからず、下記のように書き換えましたが、今度も同じ
Cannot read property 'getLastRow' of null　が出ました。今度はエラーだけでなく、送信もできませんでした。

// スプレッドシートの操作

var ss = SpreadsheetApp.openById(spread_sheet_id);
//var sh = ss.getActiveSheet(); //元のコード
var sh = ss.getSheetByName('フォームの名前'); //書き換えたコード

[MasakiTM]

横からすいませんが、フォームの名前じゃなくてシートの名前じゃないんですか。

[Daregada]

スプレッドシートを表示したときのURLから、d/ と /edit の間の部分を抜き出したのがIDで、スプレッドシートの左下に出っ張って表示されている「シート1」などの文字列がシート名です。

[oyatsu8]

ありがとうございます、書き間違えていました。シートのタイトルのことですよね。色々混乱しています。。

[oyatsu8]

夜分にありがとうございます。やはりメールの送受信はできるのですが、相変わらずエラーが出ており、　ログを出して見たところ、ちゃんと帰ってきていました。。　Cannot read property 'getLastRow' of null

var ss = SpreadsheetApp.openById(spread_sheet_id);
var sh = ss.getSheetByName('シート名');
var rows = sh.getLastRow();
console.log("rows:",rows);
var cols = sh.getLastColumn();
console.log("cols:",cols);
var rg = sh.getDataRange();
Logger.log("rows="+rows+"cols="+cols);

帰ってきたログ
020/06/28 1:37:24 デバッグ rows: 38
2020/06/28 1:37:24 デバッグ cols: 20
2020/06/28 1:37:24 情報 rows=38cols=20

[Daregada]

もう質問に載っているソースとは別のものを使ってますよね?
spread_sheet_id とか質問のソースには含まれていませんし。
そうすると、こちらでは確かめようがないのです。

[Daregada]

そのログは、メールを出せた時のログですね。nullのエラーはもう少し前で起きていますからログに記録されていません。コメントで部分的に見ている範囲でいうと、ssやshを、getLastRow() を動かすより前に、ログに出して確認してください。

[oyatsu8]

getLastRowより前でログを出して見ました。

// スプレッドシートの操作

var ss = SpreadsheetApp.openById(spread_sheet_id);
var sh = ss.getSheetByName('form_ans');//シート名

console.log("ss:",ss.getName());
console.log("sh:",sh.getName());

ログ：
Stackdriver のログ
2020/06/28 2:13:05 情報 sendMailGoogleForm() debug start
2020/06/28 2:13:05 デバッグ ss: ファイル名
2020/06/28 2:13:05 デバッグ sh: シート名
2020/06/28 2:13:05 デバッグ rows: 41
2020/06/28 2:13:05 デバッグ cols: 20
2020/06/28 2:13:05 情報 rows=41cols=20

[oyatsu8]

ほんとに夜分にありがとうございます。また、今のコードを本文に追記しました。修正後（現状）の部分です。2時を超えてしまったため、一旦寝てから考えます。大変ありがとうございました。

Answer 2

深く解析してないので申し訳ないですが
とりあえず else if(to=="")、else　略　エラーが発生～
みたいにしてみては？

Comments

[oyatsu8]

回答をありがとうございます。
今から試してみます。

[oyatsu8]

深夜にコメントをありがとうございました。

Answer 3

解決しました。回答をくださったDaregadaさんと、MasakiTMさんのおかげでコードはなんとかなったのですが、送信元（google suite）とメール送信先（gmail）を変えていたため（個人的な理由で）、メール送信先に必ずエラーが送られていたように思えます。そのため送信元とメール送信先を同じ（google suite）にしたところ、エラーは送信されなくなりました。えらく時間がかかってしまいました。なんども返事をしていただき、大変ありがとうございます。