【解決】GASで別のスプレッドシートにあるスクリプトを呼び出そうとしたらエラーになった件について

こんにちは、おかちゃんせんせいです！

仕事で作業効率化を図っているときに、あるスプレッドシートから別スプレッドシートのスクリプトを実行しようとしたときにエラーになってしまい、苦戦してしまいました。

おそらく初心者的な内容だからかネット上にピンポイントな解決策がなく、あれこれ検証していく中で2時間くらいかかってようやく解決！

エラー内容と解決方法を含めて、備忘も兼ねて記事にまとめることにしました。

Googleアドセンス

◆お知らせ◆

単体でのスクリプトは実行できるように

まず、男データというスプレッドシートの[送信リスト]シートには、メールを送信するリストがあります。
このファイルでは、Gmailに下書き保存が終わったら、A列「送信日」に送信日時を転記するマクロが組んでありました。

この時点では実行しても、問題なく処理は完了します。

コピーしました！

GAS

function createSendEmail() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let shL = ss.getSheetByName("送信リスト");
  let shS = ss.getSheetByName("設定");
  let dataL = shL.getDataRange().getValues();

  // 送信日の入っていないデータが対象
  let targetdata = dataL.filter(x => x[0] === "");
  const indexMail = 5;
  let mailArr = dataL.map(elm => elm[indexMail]).flat();

  let subject = shS.getRange("B2").getValue();  // 件名
  let body = shS.getRange("B3").getValue();     // 本文
  
  // 対象のメールアドレスに対するメールを下書きで作成する
  for(let data of targetdata){
    let to = data[indexMail];
    GmailApp.createDraft(to, subject, body);

    let index = mailArr.indexOf(data[indexMail]);
    // 送信日時を記録する
    shL.getRange(index + 1,1).setValue(new Date());
    console.log(`下書き済みメールアドレス：${to}`);
  }
}

実行元スプレッドシートのスクリプトから実行先スプレッドシートのスクリプトを実行する

送信リストは今まで手動で更新していたのですが、この機会に顧客情報データ（実行元スプレッドシート）を蓄積していきながら、順番に男データ（実行先スプレッドシート）に自動転記しようと試みることに。

今回はせっかくなので、すでに男データの方にはメールを下書きするスクリプトがすでにあるため、それを活用することを考えました。

その場合、実行先スプレッドシート側でスクリプトIDを取得して、実行元スプレッドシートでライブラリとして追加することで、実行元側で実行先スプレッドシート側のスクリプトを使用できるようになります。

実行先スプレッドシートからスクリプトIDを取得する

実行先スプレッドシート（男データ）のApps Scriptを開き、[プロジェクト設定]からスクリプトIDをコピー

実行元スプレッドシートでライブラリを追加する

実行元スプレッドシートのApps Scriptを開き、[エディタ]からライブラリの＋をクリック。
STEP1でコピーしたスクリプトIDをペーストして、『検索』をクリック。

『検索』をクリックしたら、『追加』をすることでライブラリの追加ができる。

ライブラリ欄に実行先のプロジェクト名が表示されていることを確認する

別スプレッドシートのスクリプトを呼び出したらなぜかエラーに・・・

ライブラリを利用できるようにして、顧客情報データ（実行元スプレッドシート）側でスクリプトを作成し、すでに作成済みのスクリプト（createSendEmail）を実行してみることに。

すると、なぜかすぐにエラーになってしまいました。

コピーしました！

GAS

function setCustomerData() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sh = ss.getSheetByName("ログ");
  let data = sh.getDataRange().getValues();

  const indexNum = 0;
  const indexSex = 3;
  const indexFlg = 6;
  // 転記列が空欄、かつ、性別列が男で絞り込む
  let targetData = data.filter(x => x[indexFlg] === "" && x[indexSex] === "男");

  // 男データに転記するための配列を作成する
  let exportData = [];
  for(let values of targetData){
    values.splice(indexFlg,1);    // 転記列を削除
    values.splice(indexNum,1);           // 連番列を削除
    exportData.push(values);
  }

  // 男データのcreateSendEmailスクリプトを実行する
  campaign.createSendEmail(exportData);
}

問題になっているのは、
実行先スプレッドシートのsendEmail.gsの5行目で下記エラーになっている
ということ。

TypeError: Cannot read properties of null (reading ‘getDataRange’)

今まで実行先スプレッドシート側でスクリプトを実行したときにはエラーにならなかったのに、なぜ別スプレッドシートのスクリプトからライブラリとして呼び出したらエラーに。

エラーを見る限りでは、nullになっていてgetDataRangeメソッドが使えていないとしたら、変数shLがnullになっているということ。

そこで、実行元の方のcreateSendEmailスクリプトの方で、2行目にconsole.logを仕込んでどのファイルを取得しているかを確認してみることに。

コピーしました！

GAS

  let ss = SpreadsheetApp.getActiveSpreadsheet();
  console.log(ss.getName());
  let shL = ss.getSheetByName("送信リスト");
  let shS = ss.getSheetByName("設定");
  let dataL = shL.getDataRange().getValues();

実行してみて判明したのは、
実行先で取得した変数ssのファイル名は「顧客情報データ」だったこと。

つまり、実行先スプレッドシート側のシート情報を取得したかったのに、実行元スプレッドシートのシート情報を取得してしまっていたのです。

エラーの原因は実行先スプレッドシート側のgetActiveSpreadsheetメソッド

つまり、

let ss = SpreadsheetApp.getActiveSpreadsheet();

のgetActiveSpreadsheetメソッドでは、たとえ実行先スプレッドシート側のスクリプトで実行していたとしても、実行元のアクティブなスプレッドシートを取得するということがわかりました。

では、どのようにすればこの問題を解消できるのかというと、
実行先スクリプト側ではgetActiveSpreadsheetメソッドではなく、openByIdメソッドで情報を取得すればこの問題は解消できます。

createSendEmail関数を少しアレンジして、マクロを実行してみたところ今度こそ無事に処理が完了しました。

コピーしました！

GAS

function createSendEmail(importData) {
  let id = "（スプレッドシートID）";
  let ss = SpreadsheetApp.openById(id);
  let shL = ss.getSheetByName("送信リスト");
  let shS = ss.getSheetByName("設定");
  let lastRow = shL.getLastRow();
  console.log(importData);
  // 顧客情報データからデータを転記する
  if(importData){
    shL.getRange(lastRow + 1,2,importData.length,importData[0].length).setValues(importData);
  }
  let dataL = shL.getDataRange().getValues();

  // 送信日の入っていないデータが対象
  let targetdata = dataL.filter(x => x[0] === "");
  const indexMail = 5;
  let mailArr = dataL.map(elm => elm[indexMail]).flat();

  let subject = shS.getRange("B2").getValue();  // 件名
  let body = shS.getRange("B3").getValue();     // 本文
  
  // 対象のメールアドレスに対するメールを下書きで作成する
  for(let data of targetdata){
    let to = data[indexMail];
    GmailApp.createDraft(to, subject, body);

    let index = mailArr.indexOf(data[indexMail]);
    // 送信日時を記録する
    shL.getRange(index + 1,1).setValue(new Date());
    console.log(`下書き済みメールアドレス：${to}`);
  }
}