menu

GASでスプレッドシートをPDFに無限に連続作成する方法

2023年3月3日

CodeGoogleAppsScriptSpreadsheet活用事例

GAS6分の壁を超え何件だろうスプレッドシートで作った請求書をpdfにするスクリプトです。

この投稿をInstagramで見る

Aji(@aji_loglogme)がシェアした投稿

(動画は編集で早くしてますが、実際には10秒に1件のペースでpdfを作成します)

GASのスクリプトの処理時間は6分以内という制限があり、6分以上経つと自動的に処理が中断されてしまいます。

今回、スプレッドシートで作成した請求書からPDFを連続生成するスクリプトを作ったのですが、件数が多いと途中でタイムアウトしてしまうので、それをさせずに無限にPDFを作成する方法の紹介です。

使用したスプレッドシートはこちら
なお、こちらのシートでよろしければGASのプログラムもまるごとコピーできるので、スプレッドシートとPDFの保存先IDを書き換えるだけですぐに使用することもできます。

セルの位置や行数、列数などいじるとプログラム動きませんが、幅調整などで上手に自分好みににデザイン変えれば便利ですよ〜。

使用したコード

今回使用したコードはこちらです。

//スプレッドシートを取得
const ss = SpreadsheetApp.openById("シートID");
const sheet = ss.getSheetByName('請求書/自動');
const nameList = ss.getSheetByName('メール');
const listRows = nameList.getRange('A3:A').getValues();//請求書をメールするリスト
const nameLength = listRows.filter(String).length;//空白の要素を除いた長さを取得
const listRange = nameList.getRange(3, 1, nameLength, 1);//取引先一覧の範囲を取得
const names = listRange.getValues();//取引先一覧を取得
const folder = DriveApp.getFolderById('フォルダID');//PDF保存フォルダ
const today = new Date();//作成日を取得
const thisMonth = Utilities.formatDate(today, "JST", "yyMM"); //日付を文字列に変更
const invoiceMonth = sheet.getRange('A8').getValue();//請求書yymmを取得
const invoiceyymm = Utilities.formatDate(invoiceMonth, "JST", "yyMM");

function doGet() {
  let template = HtmlService.createTemplateFromFile('index');
  template.listRows;
  return template.evaluate();
}

//PDF連続生成
function AutoPdf() {
  // 請求先リストから1件づつPDFを作成
  for (let i = 0; i < names.length; ++i) {
    processAutoPdf(i);
  }
}
//連続生成するループ処理
function processAutoPdf(i) {
  let name = names[i][0];
  sheet.getRange('A4').setValue(name);//リストの値を順に入力
  SpreadsheetApp.flush();//

  let key = ss.getId();
  let gid = sheet.getSheetId();
  let token = ScriptApp.getOAuthToken();
  //スプレッドシートのPDFURL
  let url = "https://docs.google.com/spreadsheets/d/"
    + key
    + "/export?gid="
    + gid
    + "&exportFormat=pdf&format=pdf";
  let options = {
    headers: {
      "Authorization": "Bearer " + token
    }
  }
  let response = UrlFetchApp.fetch(url, options);
  let file = response.getBlob();
  let price = sheet.getRange("B10").getValue();
  if (price == 0) {
    //エラーなどで請求書が0円の場合は作成しない
    console.log(`${name}様は0円のため作成なし`);
    Utilities.sleep(10000);//10秒待機
  } else {
    //請求書保存先フォルダ
    file.setName(invoiceyymm + "請求書_" + name + "様.pdf");
    folder.createFile(file);
    console.log(`「${invoiceyymm}請求書_${name}様.pdf」を生成しました。`);
    Utilities.sleep(10000);//10秒待機
  }
  let result;
  if (price == 0) {
    result = `${name}様は0円のため作成なし`;
  } else {
    result = `「${invoiceyymm}請求書_${name}様.pdf」を生成しました。`;
  }
  nameList.getRange(i+3, 12).setValue(result); //i+3はPDF作成ログ列の行番号を順に取得
  SpreadsheetApp.flush(); // Apply the changes to the spreadsheet.
}

スプレッドシートの注文履歴から売上のある会社の請求書を作成し、PDFに保存するプログラムです。

GASの6分の制限時間を超えて無限にPDFを生成する

urlfetchでPDFを連続生成すると、処理の多さで以下のようなエラーが出ます。

Exception: Request failed for https://docs.google.com returned code 429.

対策として、ループ中にUtilities.sleep(10000); で10秒待機させてから次のループ処理に進ませることにしたので、今回は1件のPDF作成に10秒ほどかかります。

結果的に1処理に10秒ちょいかかってるので32〜33回くらいでタイムアウトしてしまうのです。

そこでGASで用意されているHtmlServiceを使って、GoogleAppsScriptとしてではなく、JavaScriptとして実行することでリストが何件あろうとも無限にPDFをつくることができました。

10秒かかる processAutoPdf を何回も実行させているだけなので実行時間は10秒ということになります。

GASのHtmlServiceを使う

下記のコードをコピーして、<body> </body>の間に貼り付けます。


  <div class="container">
    <div class="ttl">
      <h1>定時に帰れるボタン</h1>
    </div>

    <div id="text-button" class="button">
      <button id="text" onClick="doAutoPdf()">Click!</button>
    </div>
  </div>

  <script>
    let i =0;
    let lastRow = <?!= nameLength ?> -1;
    function doAutoPdf(){
      if (i > lastRow) return;
       google.script.run.withSuccessHandler(doAutoPdf).processAutoPdf(i);
       i += 1;
      }
  </script>

  <script>
    document.getElementById("text-button").onclick = function() {
   document.getElementById("text").innerHTML = "PDFを作成します";
   };
  </script>

google.script.run はGASのコードを実行する関数です。

.withSuccessHandler() はGASのコードが正常に完了した際に()内の処理が実行されます。

processAutoPdf(i)関数が正常に処理したら、doAutoPdfを呼び出しています。

コード.gsに以下のコードを追加します。

コード.gsの13行目下に以下のコードを追加。

function doGet() {
  let template = HtmlService.createTemplateFromFile('index');
  template.listRows;
  return template.evaluate();
}

doGet関数は作成したWebアプリケーションのURLにアクセスがあったときに、自動でスクリプトを実行させる関数です。

template.listRows; と記載することでhtml側でlistRowsの変数を受け取れます。

<?!= 変数 ?>と記入することでGAS側で書いた変数をhtmlに持ってくることができます。

これで準備完了です。

index.htmlに戻り、右の上の「デプロイ」「新しいデプロイ」をクリック。

「種類の選択」から「ウェブアプリ」をクリック

説明文と、アクセスできるユーザーを選択したらデプロイボタンを押します。

WEBアプリのURLができるのでアクセスします。

Click!ボタンを押すと、スクリプトが実行されてPDFの生成が無限にできます。

この実行ボタンを押せば、スプレッドシートで作成した請求書を何件あろうとも1クリックでPDFにすることができます。

使い方

使い方はシンプルです。

①スプレッドシートの「請求書/自動」シートで作成したい月(請求書/自動シートのA8セル)を選ぶ

②Clikボタンを押す!

これで取引先一覧から、作成したい月に取引があった業者のみPDFを作成します。(請求額が0円の場合は作成しません)

Googleドライブにどんどん保存していきます。

おわりに

今回初めてGASを使って業務効率化の開発をしてみました。

業務でよくスプレッドシートは使うし、Google関連のサービスもよく使うので勉強して自動化処理を取り入れていこうと思います。

GASの勉強には事務職たらこ(@taraco1230)さんが出されているUdemyの教材がわかりやすくとても参考になりました。
Google Apps Script(GAS)の基礎を完全習得 -初心者歓迎-【爆速で習得しちゃおう】

6分の壁を超えて無限に処理する方法は以下の記事を参考にさせていただきました。
制限時間をこえて6分以上GASを実行する方法【Google Apps Script】

Follow Me!

Aji ろぐろぐみー筆者

フリーランスエンジニア。
詳しいプロフィールはこのページで色々書いてます。Twitterもやってます。コメント頂けると陸に上がった魚のようにピチピチ喜びます!

関連記事

この記事へのコメント

  • GI より:

    上記の記事を参考に請求書作成のGASとHTMLを作成しました。WEBアプリのURLにアクセスすると、「Exception: 「css」という HTML ファイルは見つかりませんでした。(行 6)」と表示され、実行することができない状況です。よろしければ解決方法を教えていただきたいです。

    • Aji より:

      失礼しました!
      この記事ではcssに触れておりませんので、index.htmlの部分を修正しました。

      headタグ内の記述が原因でしたので、削除すると動くと思います。
      ご指摘ありがとうございました。

      • GI より:

        無事に動いて請求書作成できるようになりました。ありがとうございます!

        • Aji より:

          よかったです!
          作成したPDFをさらにメールに添付して一斉送信するスクリプトも作ったので今度アップしますのでよかった使ってください^^

  • ぴぴ より:

    こんにちは。

    ”使用したスプレッドシートはこちら。”からスプレッドシートをコピーして、シートIDとグーグルドライブのフォルダIDを入力して、デプロイからURLにアクセルすると『TypeError: Cannot read properties of null (reading ‘getRange’)(行 5、ファイル「コード」)』と表示されます。コードGSというところから実行してもエラーになります。

    当記事に記載されてる操作以外にも何か入力等が必要であれば教えていただきたいです。
    宜しくお願い致します。

    • Aji より:

      ぴぴさんコメントありがとうございます。
      スプレッドシートいじった時のコードの反映がされてませんでした。
      修正しましたので、もういちどコピーしなおしてお試し頂けますか?

      もしまたエラーがでたらコメントくださいませ。

  • ぴぴ より:

    出来ました!
    ありがとうございます!

コメントはこちらからどうぞ

プライバシーポリシーはこちら
また、コメントはこちらで承認の作業を行うまでは表示されません。ご了承ください。

CAPTCHA