Google Apps Script試行錯誤 Blog: 2025

2025年6月12日木曜日

スプレッドシートの空白セルを直前の値で埋めたい

A列の空白セルに直前の値を入れたくて書いたコードです

スプレッドシートに以下のようなBeforeの表があるとき

（A列に空白セルがある）

Before

1	A	B
2	エリア	都市
3	東京	新宿
4		渋谷
5		池袋
6	神奈川	横浜
7		川崎
8		相模原
9	千葉	千葉
10		船橋
11		松戸

空白セルの直前に入っている値で埋めて以下Afterのようにしたい

After

1	A	B
2	エリア	都市
3	東京	新宿
4	東京	渋谷
5	東京	池袋
6	神奈川	横浜
7	神奈川	川崎
8	神奈川	相模原
9	千葉	千葉
10	千葉	船橋
11	千葉	松戸

Code.gs

function fillEmptyAreaCells() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getRange("A2:A" + sheet.getLastRow()); // A2から最終行まで
  const values = range.getValues();

  let lastValue = "";

  for (let i = 0; i < values.length; i++) {
    if (values[i][0] !== "") {
      lastValue = values[i][0]; // 新しい値を記憶
    } else {
      values[i][0] = lastValue; // 空欄を直前の値で埋める
    }
  }

  range.setValues(values); // 上書き保存
}

Tips

スプレッドシートのURLを指定して実行したい場合

getSheetById(id) という待望のメソッドが追加されていたのでそれを使う

function fillEmptyAreaCellsBySheetUrl() {
  const sheetUrl = "SPREADSHEET_URL";

  // URLからgidを文字列として正確に取得（指数表記防止）
  const gid = (sheetUrl.match(/gid=(\d+)/) || [])[1];
  Logger.log("gid: " + gid);

  const ss = SpreadsheetApp.openByUrl(sheetUrl); // ここでURLのスプレッドシートを明示的に開く
  const sheet = ss.getSheetById(gid); // このSpreadsheetの中からgidを探す

  if (!sheet) {
    throw new Error("❌ 指定された gid のシートが見つかりません: " + gid);
  }

  const range = sheet.getRange("A2:A" + sheet.getLastRow());
  const values = range.getValues();

  let lastValue = "";

  for (let i = 0; i < values.length; i++) {
    if (values[i][0] !== "") {
      lastValue = values[i][0];
    } else {
      values[i][0] = lastValue;
    }
  }

  range.setValues(values);
}

今回シートのIDは const gid = (sheetUrl.match(/gid=(\d+)/) || [])[1]; で取得しています

const gid = sheetUrl.split("gid=")[1] ではエラーになりました

スプレッドシートのURLをよく見ると

シートIDが末尾に2つある

https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit?gid=127068302#gid=127068302

しかも#でつながっているので

const gid = sheetUrl.split("gid=")[1] とかやると

127068302#

末尾に#がついてきてエラーになる

Error: ❌ 指定された gid のシートが見つかりません: 1621369581#

Reference

getSheetById(id)

https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getsheetbyidid

2025年6月8日日曜日

複数のGoogleスライドのページを一つのスライドにまとめたい

複数のスライドに含まれるページたちを

一つのスライドに集約したくて書いたコードです

HTML ServiceでフロントのUIを作って

コピー元スライドのページたちを

基盤スライドの末尾に追加していきます

Gemini や ChatGPT と対話しながら SlidesApp で実装しました

処理はあまり速くないので

スライドの数が多い場合は6分の実行時間制限にぶつかりますが

複数回に分けて実行することで回避可能

Code.gs

function doGet() {
  return HtmlService.createHtmlOutputFromFile('index');
}

// URLからスライドID抽出（例：open?id=xxxxx or d/xxxxx/）
function extractId(url) {
  const match = url.match(/[-\w]{25,}/);
  if (!match) throw new Error("URLからIDを抽出できません: " + url);
  return match[0];
}

// フロントから呼ばれる統合処理
function mergePresentations(baseSlideUrl, urlText) {
  const basePresentationId = extractId(baseSlideUrl);
  const slideUrls = urlText.split(/\r?\n/).map(s => s.trim()).filter(Boolean);
  const basePresentation = SlidesApp.openById(basePresentationId);
  const baseSlideCount = basePresentation.getSlides().length;

  slideUrls.forEach((url, i) => {
    const sourcePresentationId = extractId(url);

    // 各元スライドから全ページ取得し、baseへ追加
    const srcPresentation = SlidesApp.openById(sourcePresentationId);
    const srcSlides = srcPresentation.getSlides();
    srcSlides.forEach(srcSlide => {
      basePresentation.appendSlide(srcSlide);
    });
  });

  return 'https://docs.google.com/presentation/d/' + basePresentationId + '/edit';
}

index.html

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <style>
    body { font-family: sans-serif; }
    label { font-weight: bold; }
    textarea, input[type="text"] { width: 98%; }
    button { margin-top: 10px; padding: 6px 20px; }
  </style>
</head>
<body>
  <h3>スライド統合アプリ</h3>
  <label>基盤スライドのURL：</label><br>
  <input type="text" id="baseSlideUrl"><br><br>
  <label>コピー元スライドのURL一覧（1行1URL）：</label><br>
  <textarea id="slideUrls" rows="6" placeholder="https://docs.google.com/presentation/d/～"></textarea><br>
  <button onclick="submitMerge()">実行</button>
  <div id="result" style="margin-top:15px;"></div>
  <script>
    function submitMerge() {
      const baseUrl = document.getElementById('baseSlideUrl').value.trim();
      const urlText = document.getElementById('slideUrls').value.trim();
      if (!baseUrl || !urlText) {
        document.getElementById('result').innerHTML = "両方入力してください。";
        return;
      }
      document.getElementById('result').innerHTML = '統合中...しばらくお待ちください。';
      google.script.run
        .withSuccessHandler(function(url) {
          document.getElementById('result').innerHTML =
            '統合完了！<a href="' + url + '" target="_blank">統合スライドを開く</a>';
        })
        .withFailureHandler(function(e) {
          document.getElementById('result').innerHTML =
            'エラー：' + (e.message || e);
        })
        .mergePresentations(baseUrl, urlText);
    }
  </script>
</body>
</html>

Tips

UIを作らずにコードだけで実行する場合

統合したい SLIDE_ID を配列に入れて

mergePresentations() を実行すると

統合用のスライドが自動作成されます

function mergePresentations() {
  // 統合したいGoogleスライドのIDリスト
  const presentationIds = [
    'SLIDE_ID', // スライド1のID
    'SLIDE_ID', // スライド2のID
    'SLIDE_ID' // スライド3のID
  ];

  // 新しいプレゼンテーションを作成
  const mergedPresentation = SlidesApp.create('統合スライド');
  const mergedSlides = mergedPresentation.getSlides();

  // 最初の空スライドを削除
  if (mergedSlides.length > 0) {
    mergedSlides[0].remove();
  }

  // 各プレゼンテーションの全スライドを追加
  presentationIds.forEach(id => {
    const srcPresentation = SlidesApp.openById(id);
    const srcSlides = srcPresentation.getSlides();
    srcSlides.forEach(srcSlide => {
      mergedPresentation.appendSlide(srcSlide);
    });
  });

  Logger.log('統合完了！URL: https://docs.google.com/presentation/d/' + mergedPresentation.getId() + '/edit');
}

Googleドライブの複数ファイルのショートカットを指定フォルダに移動したい

ファイルそのものを移動するのではなく

ファイルのショートカットを作りたくて試しました

Code.gs

function doGet() {
  return HtmlService.createHtmlOutputFromFile('index');
}

/**
 * 指定されたフォルダにファイルのショートカットを作成します。
 * @param {string[]} fileIds - ショートカットを作成するファイルのIDの配列。
 * @param {string} folderId - ショートカットを作成する先のフォルダのID。
 * @return {string} 処理結果のメッセージ。
 */
function createShortcuts(fileIds, folderId) {
  const destinationFolder = DriveApp.getFolderById(folderId);
  fileIds.forEach(fileId => {
    // ショートカットを作成
    destinationFolder.createShortcut(fileId);
  });
  return "すべてのファイルのショートカットを作成しました！";
}

index.html

<!DOCTYPE html>
<html>
  <head>
    <meta charset="UTF-8">
    <title>ショートカット作成ツール</title>
  </head>
  <body>
    <h2>Googleドライブのファイルショートカット作成</h2>

    <div>
      <label for="fileUrls">ファイルURL（1行に1つずつ）:</label><br>
      <textarea id="fileUrls" rows="8" cols="80" placeholder="https://..."></textarea>
    </div>

    <div>
      <label for="folderUrl">フォルダURL:</label><br>
      <input type="text" id="folderUrl" style="width: 500px;" placeholder="https://...">
    </div>

    <br>
    <button id="submit">ショートカット作成</button>

    <script>
      function extractId(url) {
        const match = url.match(/[-\w]{25,}/);
        return match ? match[0] : null;
      }

      document.getElementById("submit").onclick = function () {
        const fileUrls = document.getElementById("fileUrls").value.trim();
        const folderUrl = document.getElementById("folderUrl").value.trim();

        const fileIds = fileUrls
          .split('\n')
          .map(line => extractId(line))
          .filter(id => id !== null);

        const folderId = extractId(folderUrl);

        if (fileIds.length === 0 || !folderId) {
          alert("ファイルURLとフォルダURLを正しく入力してください。");
          return;
        }

        google.script.run
          .withSuccessHandler(onSuccess)
          .withFailureHandler(onFailure)
          .createShortcuts(fileIds, folderId);
      };

      function onSuccess(message) {
        alert(message);
      }

      function onFailure(error) {
        alert("エラーが発生しました: " + error.message);
      }
    </script>
  </body>
</html>

Reference

createShortcut(targetId)

http://developers.google.com/apps-script/reference/drive/drive-app#createShortcut(String)

Googleドライブのファイルを指定フォルダに移動したい - Move a file to a specific folder in Google Drive via Apps Script

Googleドライブの複数ファイルを指定フォルダに移動したい

複数ファイルを一気に移動したくて作ったアプリです

移動するファイルを1行ずつ「File URLs」テキストエリアに入れて

移動先のフォルダのURLを「Folder URL」に入れて

Submitをクリックする

Code.gs

function doGet() {
  return HtmlService.createHtmlOutputFromFile('index');
}

function moveFiles(fileIds, folderId) {
  const destinationFolder = DriveApp.getFolderById(folderId);
  fileIds.forEach(fileId => {
    const file = DriveApp.getFileById(fileId);
    file.moveTo(destinationFolder);
  });
  return "All files moved successfully!";
}

index.html

<!DOCTYPE html>
<html>
  <body>
    <a target="_blank" href="https://script.google.com/home/projects/SCRIPT_ID/edit">SCRIPT</a>
    <div>
      <label for="fileUrls">File URLs (one per line):</label>
      <br>
      <textarea id="fileUrls" rows="5" cols="50"></textarea>
    </div>
    <div>
      <label for="folderUrl">Folder URL:</label>
      <br>
      <input type="text" id="folderUrl">
    </div>
    <button id="submit">Submit</button>

    <script>
      document.getElementById("submit").onclick = function() {
        const fileUrls = document.getElementById("fileUrls").value;
        const folderUrl = document.getElementById("folderUrl").value;

        // 改行で区切ってfileIdsを取得
        const fileIds = fileUrls.split('\n').map(url => url.trim().split("/d/")[1].split("/")[0]);
        const folderId = folderUrl.split("/folders/")[1];

        google.script.run
          .withFailureHandler(onFailure)
          .withSuccessHandler(onSuccess)
          .moveFiles(fileIds, folderId);
      };

      function onSuccess(result) {
        alert(result);
      }

      function onFailure(e) {
        alert([e.message, e.stack]);
      }
    </script>
  </body>
</html>

Tips

アプリ内からスクリプトエディタを開けるようにAppsScriptプロジェクトのリンクを配置しています

<a target="_blank" href="https://script.google.com/home/projects/SCRIPT_ID/edit">SCRIPT</a>

Googleドライブのファイルを指定フォルダに移動したい - Move a file to a specific folder in Google Drive via Apps Script

2025年5月20日火曜日

Google Formsでスプレッドシートに用意した質問を読み込んでみる（クイズ対応）

今回はGoogleフォームでスプレッドシートからデータを読み込んでクイズを作ります

事前に「質問・選択肢・フィードバック」をスプレッドシートに入力して

それを読み込んでクイズモードのフォームを作ってみます

事前に用意するスプレッドシートの構造

A列に質問
B列に選択肢

先頭が正解

C列に正解のフィードバック
D列に不正解のフィードバック

フォームの回答画面

Code.gs

function createQuizzesFromSheet() {
  const spreadsheetUrl = 'https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit#gid=0';
  const formName = 'Simple Quiz from Sheet';
  const description = 'Pleas answer the following question';

  // スプレッドシートを読み込む
  const sheet = SpreadsheetApp.openByUrl(spreadsheetUrl).getSheets()[0];
  const [headers, ...rows] = sheet.getDataRange().getValues();

  // 列インデックス取得
  const colIndex = {
    question: headers.indexOf('Question'),
    options: headers.indexOf('Options'),
    correctFb: headers.indexOf('Correct Feedback'),
    incorrectFb: headers.indexOf('Incorrect Feedback')
  };

  // 新しいフォームを作成
  const form = FormApp.create(formName);
  form.setDescription(description);
  form.setIsQuiz(true);

  // 各行のデータから問題を作成
  rows.forEach(row => {
    const question = row[colIndex.question];
    const optionsText = row[colIndex.options];
    const correctFb = row[colIndex.correctFb];
    const incorrectFb = row[colIndex.incorrectFb];

    const options = optionsText
      .split('\n')
      .map((text, index) => ({
        text: text.trim(),
        isCorrect: index === 0  // 最初の選択肢を正解に
      }));

    const item = form.addMultipleChoiceItem();
    item.setTitle(question)
        .setChoices(options.map(opt => item.createChoice(opt.text, opt.isCorrect)))
        .setRequired(true)
        .setPoints(10)
        .setFeedbackForCorrect(FormApp.createFeedback().setText(correctFb).build())
        .setFeedbackForIncorrect(FormApp.createFeedback().setText(incorrectFb).build());
  });

  form.setPublished(false);

  Logger.log('作成されたフォームのURL: ' + form.getEditUrl());
}

上記 Code.gs を実行すると

指定したスプレッドシートからデータを読み込んで

質問・選択肢・解答集に値が入ったフォームが作成されます

フォームの編集画面 > 解答集を作成

Tips 1: 選択肢をシャッフルしたい場合

選択肢もシャッフルしたい場合は Apps Script だけではできないので

事前に選択肢をシャッフルする設定にしたテンプレートのフォームを作って

そのテンプレートをコピーして質問や回答を上書きする

Google Formsで選択肢の順番を毎回シャッフルしたい（Apps Script）

その方法でCode.gsを書き直してみる

Code.gs

function createQuizzesFromSheetByTemplate() {
  const spreadsheetUrl = 'https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit#gid=0'; // スプレッドシートURL
  const templateFormUrl = 'https://docs.google.com/forms/d/FORM_ID/edit'; // テンプレートフォームURL
  const formName = 'Quiz from Sheet';
  const description = 'Pleas answer the following question';

  // --- スプレッドシートを読み込む ---
  const sheet = SpreadsheetApp.openByUrl(spreadsheetUrl).getSheets()[0];
  const [headers, ...rows] = sheet.getDataRange().getValues();

  // ヘッダーインデックス取得（柔軟に対応するため）
  const colIndex = {
    question: headers.indexOf('Question'),
    options: headers.indexOf('Options'),
    correctFb: headers.indexOf('Correct Feedback'),
    incorrectFb: headers.indexOf('Incorrect Feedback')
  };

  // --- フォームのテンプレートをコピーして新規作成 ---
  const templateFormId = FormApp.openByUrl(templateFormUrl).getId();
  const templateFile = DriveApp.getFileById(templateFormId);
  const copiedFile = templateFile.makeCopy(formName);
  const form = FormApp.openById(copiedFile.getId());

  form.setTitle(formName);
  form.setDescription(description);
  form.setIsQuiz(true);
  form.setPublished(false);

  // 最初の項目を複製して数を揃える
  const original = form.getItems(FormApp.ItemType.MULTIPLE_CHOICE)[0];
  for (let i = 1; i < rows.length; i++) {
    original.duplicate();
  }

  const mcItems = form.getItems(FormApp.ItemType.MULTIPLE_CHOICE);

  // --- 各行に基づいてフォーム項目を設定 ---
  rows.forEach((row, i) => {
    const question = row[colIndex.question];
    const optionsText = row[colIndex.options];
    const correctFb = row[colIndex.correctFb];
    const incorrectFb = row[colIndex.incorrectFb];

    const options = optionsText
      .split('\n')
      .map((text, index) => ({
        text: text.trim(),
        isCorrect: index === 0  // 先頭が正解
      }));

    const item = mcItems[i].asMultipleChoiceItem();
    item.setTitle(question)
        .setChoices(options.map(opt => item.createChoice(opt.text, opt.isCorrect)))
        .setRequired(true)
        .setPoints(10)
        .setFeedbackForCorrect(FormApp.createFeedback().setText(correctFb).build())
        .setFeedbackForIncorrect(FormApp.createFeedback().setText(incorrectFb).build());
  });

  form.setPublished(false);

  Logger.log('Created Form: ' + form.getEditUrl());
}

Tips 2: アンケートフォームの場合

質問も選択肢もシャッフルしない場合はもっと短く書けます

Code.gs

function createSimpleFormFromSheet() {
  const spreadsheetUrl = 'https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit#gid=0';
  const formName = 'Simple Survey from Sheet';
  const description = 'Pleas answer the following question';

  // スプレッドシートのデータ取得
  const sheet = SpreadsheetApp.openByUrl(spreadsheetUrl).getSheets()[0];
  const [headers, ...rows] = sheet.getDataRange().getValues();

  const colIndex = {
    question: headers.indexOf('Question'),
    options: headers.indexOf('Options'),
  };

  // 新しい通常フォームを作成（クイズではない）
  const form = FormApp.create(formName);
  form.setDescription(description);

  // 各行の質問をフォームに追加
  rows.forEach(row => {
    const question = row[colIndex.question];
    const optionsText = row[colIndex.options];

    const options = optionsText
      .split('\n')
      .map(text => text.trim())
      .filter(text => text); // 空行除去

    const item = form.addMultipleChoiceItem();
    item.setTitle(question)
        .setChoiceValues(options)
        .setRequired(true);
  });

  Logger.log('作成されたフォームの編集URL: ' + form.getEditUrl());
}