LoginSignup
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@ShineYamamoto(M Yamamoto)

GAS勉強会5　～スプレットシートのデータを書き換える～

Posted at

1.はじめに

今回はスプレットシートに登録しているデータをブラウザから修正できる仕組みを学ぶ。
スプレットシートのデータは以下のようになっているとする。

id timestamp productName price 更新日時
5RAA9BPJ 2025-10-17T05:11:28.795Z バナナ 200
3F5G4ZHN 2025-10-17T05:12:07.963Z みかん 150
9UNCIYJY 2025-10-17T05:12:31.427Z パイナップル 3000
UC6HMXA0 2025-10-17T05:12:51.981Z アボカド 9999999

今回作成するプログラムではidからデータを検索したり編集をするようにする。

2.HTMLの骨格

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <base target="_top">
    <script>

    </script>
  </head>

  <body>
    <h2>商品検索</h2>
    <label for="search_id">商品ID:</label>
    <input id="search_id" value="">
    <button id="search-btn" onclick="searchData()">検索</button><br>

    <form hidden="hidden" id="edit_form">
      <label for="product_name">商品名:</label>
      <input id="product_name" value=""><br>
      <label for="price">価格</label>
      <input id="price" value="">
    </form>

    <button hidden="hidden" id="edit-btn" onclick="sendEditData()">編集</button>
  </body>
</html>

見た目上では以下の画像のようになっている。
スクリーンショット 2025-10-17 014605.png

商品IDを入力して検索ボタンを押したら該当する商品の編集フォームが作成される仕組みを作成する。

3.GASのベース

const FILE_ID = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
const ss = SpreadsheetApp.openById(FILE_ID);
const sheet = ss.getSheetByName('シート1');

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

function searchDataById(id) {
  //console.log(id);
  const targetRow = getIdRow(id, 'A:A');
  if (!targetRow) {return null};

  const lastCol = sheet.getLastColumn();
  const headers = sheet.getRange(1, 1, 1, lastCol).getValues()[0];
  const targetData = sheet.getRange(targetRow, 1, 1, lastCol).getValues()[0];

  const obj = {};
  headers.forEach((header, i) => {
    obj[header] = targetData[i]
  });

  return obj; 
}

// 商品情報をIDで検索して返す関数
function getIdRow(id, targetCol) {
  const finder = sheet.getRange(targetCol).createTextFinder(id).matchEntireCell(true).findNext();
  // 見つからなかった場合
  if (!finder) {
    console.log(`ID「${id}」が見つかりませんでした。`);
    return;
  }

  // 見つかった場合
  const targetRow = finder.getRow();
  return targetRow;
}

searchDataById(id)関数はブラウザから送られてきた商品IDがスプレットシートにある場合は商品情報をブラウザに返す仕組みになっている。
getIdRow(id, targetCol);は商品IDの商品情報がスプレットシートの何行目にあるのかを返す関数。

  const targetRow = getIdRow(id, 'A:A');
  if (!targetRow) {return null};

これでgetIdRowを実行し、もしデータがない場合はnullを返す。2つ目の引数はスプレットシートの検索列。
関数の中身の

const finder = sheet.getRange(targetCol)
                .createTextFinder(id)
                .matchEntireCell(true)
                .findNext();

が実際に検索をかけているメソッド。.matchEntireCell(true)にすることで完全一致にしている。

  if (!finder) {
    console.log(`ID「${id}」が見つかりませんでした。`);
    return;
  }

もし見つかっている場合は何らかのデータがfinderに格納されているが、見つからなければデータがfinderに格納されていない。その場合はデータを渡さずに終了する。

    // 見つかった場合
  const targetRow = finder.getRow();
  return targetRow;

もし見つかれば.getRow()で行数を特定して結果を返す。

if (!targetRow) {return null};

データがsearchDataById(id)に返されていなければnullをブラウザに返す。

  const lastCol = sheet.getLastColumn();
  const headers = sheet.getRange(1, 1, 1, lastCol).getValues()[0];
  const targetData = sheet.getRange(targetRow, 1, 1, lastCol).getValues()[0];
  const obj = {};
  headers.forEach((header, i) => {
    obj[header] = targetData[i]
  });

  return obj;

データが見つかっていればヘッダーの値と該当商品情報の値を変数に格納し、オブジェクトに変換してブラウザに返す。
次はブラウザの処理を考える。

4.HTMLの編集フォームを作成するプログラムを作る

HTMLの<script>タブの中に以下を記述する。

      // データの取得
      function searchData() {
        const searccId = document.getElementById("search_id").value;
        console.log("id: " + searccId + " で検索をしています。");

        google.script.run
          .withSuccessHandler(createEditForm)
          .withFailureHandler(function(err) {
            alert("エラーが起きました: " + err.message);
          })
          .searchDataById(searccId);
      }

      // 編集フォームを作成
      function createEditForm(data) {
        if (!data) {
          alert("検索した商品IDは存在しません");
          return;
        }

        console.log(data);
        document.getElementById("edit_form").hidden = "";
        document.getElementById("edit-btn").hidden = "";
        document.getElementById("product_name").value = data.商品名;
        document.getElementById("price").value = data.価格;

        document.getElementById("edit_form").setAttribute("data_id", data.ID);
        document.getElementById("edit_form").setAttribute("data_submit_date", new Date(data.登録日時).toLocaleString());
      }

検索ボタンがクリックされたらsearchData()関数が実行される。

        const searccId = document.getElementById("search_id").value;
        console.log("id: " + searccId + " で検索をしています。");

        google.script.run
          .withSuccessHandler(createEditForm)
          .withFailureHandler(function(err) {
            alert("エラーが起きました: " + err.message);
          })
          .searchDataById(searccId);

searccIdに検索する商品IDを格納する。
google.script.runでサーバー側の.searchDataById(searccId);を実行させ。成功すればcreateEditFormが実行される。

      function createEditForm(data) {
        if (!data) {
          alert("検索した商品IDは存在しません");
          return;
        }

        console.log(data);
        document.getElementById("edit_form").hidden = "";
        document.getElementById("edit-btn").hidden = "";
        document.getElementById("product_name").value = data.商品名;
        document.getElementById("price").value = data.価格;

        document.getElementById("edit_form").setAttribute("data_id", data.ID);
        document.getElementById("edit_form").setAttribute("data_submit_date", new Date(data.登録日時).toLocaleString());
      }

もしサーバーから何も返されなければ（!data）アラートを出して終了する。
検索している商品が見つかっていた場合はHTMLを編集して編集フォームを作る。

document.getElementById("edit_form").setAttribute("data_id", data.ID);

でフォームタブにdata_idとして商品IDを登録しておく。この後登録ボタンを押した後の処理はこの値を参照することになる。
作成されたーフォームは以下のように表示される。
スクリーンショット 2025-10-17 033136.png

5.編集データをスプレットシートに登録するプログラム

HTMLの<script>タブにsendEditData関数を作成する。

      function sendEditData() {

        const productId = document.getElementById("edit_form").getAttribute("data_id");
        const editedProductName = document.getElementById("product_name").value;
        const editedPrice = document.getElementById("price").value;

        console.log({ productId, editedProductName, editedPrice })
        
        google.script.run
          .withSuccessHandler((data) => {
            alert("登録が完了しました。");
            console.log(data);
          })
          .withFailureHandler((err) => {
            alert("登録に失敗しました: " + err.message);
          })
          .submitEditedData({ productId, editedProductName, editedPrice })
      }

入力されているデータと商品IDを取得し、google.script.runでサーバのsubmitEditedData()関数を実行させる。

6.スプレットシートに編集データを登録する。

GASファイルに次の関数を新たに作成する。

function getHeaderCol(header, targetRow) {
  const finder = sheet.getRange(targetRow).createTextFinder(header).matchEntireCell(true).findNext();
  // 見つからなかった場合
  if (!finder) {
    console.log(`Header:「${header}」が見つかりませんでした。`);
    return;
  }

  // 見つかった場合
  const targetCol = finder.getColumn();
  return targetCol;
}

function submitEditedData(data) {
  // 編集予定のセルを特定する
  const targetRow = getIdRow(data.productId, 'A:A');
  const productNameCol = getHeaderCol("商品名", '1:1');
  const priceCol = getHeaderCol("価格", '1:1');
  const updateDateCol = getHeaderCol("更新日時", '1:1');

  if (!targetRow || !productNameCol || !priceCol || !updateDateCol) {return null};

  // データを変更する
  sheet.getRange(targetRow, productNameCol).setValue(data.editedProductName);
  sheet.getRange(targetRow, priceCol).setValue(data.editedPrice);
  sheet.getRange(targetRow, updateDateCol).setValue(new Date().toISOString());

  return "Success"
}

getHeaderCol()関数はヘッダーの何列目に任意のタイトルがあるかを調べる関数。構造はgetIdRow関数と同じ。

submitEditedData()関数でスプレットシートに登録する。

  const targetRow = getIdRow(data.productId, 'A:A');
  const productNameCol = getHeaderCol("商品名", '1:1');
  const priceCol = getHeaderCol("価格", '1:1');
  const updateDateCol = getHeaderCol("更新日時", '1:1');

変更したいデータがスプレットシートのどこにあるのかを確認するため該当の行数・列数を取得する。

  sheet.getRange(targetRow, productNameCol).setValue(data.editedProductName);
  sheet.getRange(targetRow, priceCol).setValue(data.editedPrice);
  sheet.getRange(targetRow, updateDateCol).setValue(new Date().toISOString());

最後に.setValue()メソッドで変更用のデータを登録する。

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?