#4 【完全無料】AppGyverとGoogleスプレッドシートを繋げるプログラミングコードの紹介

くすりばこ

2024年6月6日 13:00

・前回のおさらい

前回（#3）では、AppGyverの登録の仕方から作成したアプリの動きを実際にWeb上で確認する方法を紹介しました。

今回は、自分で作成したGoogleスプレッドシートとAppGyverを接続する方法を教えます。

誰でも使えるように、私のコードに関しては【完全無料】で紹介したいと思いますので、ぜひご覧になってください。

・必要な手順

必要になる手順は、以下の3つです。

Googleスプレッドシートで一覧表の作成
Google Apps Script（GAS）でコードの編集
AppGyverでコードのURLの連携

1. Googleスプレッドシートで一覧表の作成

今回はサンプルとして、ChatGPTに作ってもらった、以下のような47都道府県の人口の表を使って、説明しようと思います。

【注意！！】A列は小文字のidにして、1から順にしてください。（IDとかはNG）

エクセルをご用意したので、以下の表をダウンロードして使っていただいても構いません。

まず、自分のスプレッドシートの共有を「リンクを知っている全員」に変更
（閲覧者で構いません）

2. Google Apps Script（GAS）でコードの編集

次に、Googleスプレッドシートの拡張機能であるGoogle Apps Script（通称：GAS）を開きます。

以下のような画面になるので、自由に名称を変更する（「人口統計」になっているところ）

「コード.gs」の部分が開いているため、右のコードの作成欄に以下のコードをコピペする。

1ヶ月費やし、
100回以上 ChatGPTに質問を投げ、
50回以上トライ&エラーしたコードが以下

// スプレッドシートのURLからワークブックを開く
var wbook = SpreadsheetApp.openByUrl('あなたのスプレッドシートのURL');
var wsheet = wbook.getSheetByName('タブの名称');

// キャッシュの有効期限（秒）
var CACHE_EXPIRATION = 60; // 1分

// キャッシュに保存するデータの最大サイズ（バイト）
var MAX_CACHE_SIZE = 1000000; // 1MB
var MAX_CACHE_ENTRY_SIZE = 100000; // Individual cache entry size limit (adjust as needed)

function doGet(e) {
  var op = e.parameter.action;

  if (op === 'get_record') {
    var id = e.parameter.id;
    return getRecord(id);
  }

  if (op === 'get_collection') {
    var query = e.parameter.query;
    if (!query) {
      // クエリが提供されていない場合、空のJSONを返す
      return ContentService.createTextOutput(JSON.stringify([])).setMimeType(ContentService.MimeType.JSON);
    }
    return getCollection(query);
  }

  return ContentService.createTextOutput(JSON.stringify({ "error": "Invalid action" })).setMimeType(ContentService.MimeType.JSON);
}

function getRecord(id) {
  var cache = CacheService.getScriptCache();
  var cachedData = cache.get(id);

  if (cachedData != null) {
    return ContentService.createTextOutput(cachedData).setMimeType(ContentService.MimeType.JSON);
  }

  var record = binarySearchRecordById(wsheet, parseInt(id));

  if (!record) {
    return ContentService.createTextOutput(JSON.stringify({ "result": "id not found" })).setMimeType(ContentService.MimeType.JSON);
  } else {
    var jsonRecord = JSON.stringify(record);
    if (jsonRecord.length <= MAX_CACHE_SIZE) {
      cache.put(id, jsonRecord, CACHE_EXPIRATION);
    } else {
      console.error('Data size exceeds the maximum cache size.');
    }
    return ContentService.createTextOutput(jsonRecord).setMimeType(ContentService.MimeType.JSON);
  }
}

function getCollection(query) {
  var cache = CacheService.getScriptCache();
  var cacheKey = 'collection_' + query;
  var cachedData = getCachedDataInChunks(cache, cacheKey);

  if (cachedData != null) {
    return ContentService.createTextOutput(cachedData).setMimeType(ContentService.MimeType.JSON);
  }

  var dataRange = wsheet.getDataRange();
  var values = dataRange.getValues();
  var headers = values[0];
  var records = [];

  for (var i = 1; i < values.length; i++) {
    var record = {};
    var includeRecord = false;

    for (var j = 0; j < headers.length; j++) {
      record[headers[j]] = values[i][j];
      if (j !== 0 && values[i][j].toString().toLowerCase().includes(query.toLowerCase())) {
        includeRecord = true;
      }
    }

    if (includeRecord) {
      records.push(record);
    }
  }

  var jsonCollection = JSON.stringify(records);
  if (jsonCollection.length <= MAX_CACHE_SIZE) {
    cacheDataInChunks(cache, cacheKey, jsonCollection);
  } else {
    console.error('Data size exceeds the maximum cache size.');
  }
  return ContentService.createTextOutput(jsonCollection).setMimeType(ContentService.MimeType.JSON);
}

function binarySearchRecordById(sheet, id) {
  var dataRange = sheet.getDataRange();
  var values = dataRange.getValues();
  let low = 1; // Assuming data starts from row 2 (row 1 is headers)
  let high = values.length - 1;

  while (low <= high) {
    let mid = Math.floor((low + high) / 2);
    let midId = values[mid][0]; // 1st column is ID

    if (midId < id) {
      low = mid + 1;
    } else if (midId > id) {
      high = mid - 1;
    } else {
      var record = {};
      values[0].forEach(function(header, index) {
        record[header] = values[mid][index];
      });
      return record;
    }
  }
  return null; // ID not found
}

function cacheDataInChunks(cache, key, data) {
  var dataSize = data.length;
  var chunkCount = Math.ceil(dataSize / MAX_CACHE_ENTRY_SIZE);
  for (var i = 0; i < chunkCount; i++) {
    var chunkKey = key + "_chunk_" + i;
    var chunkData = data.slice(i * MAX_CACHE_ENTRY_SIZE, (i + 1) * MAX_CACHE_ENTRY_SIZE);
    cache.put(chunkKey, chunkData, CACHE_EXPIRATION);
  }
  cache.put(key + "_chunk_count", chunkCount.toString(), CACHE_EXPIRATION);
}

function getCachedDataInChunks(cache, key) {
  var chunkCount = parseInt(cache.get(key + "_chunk_count"));
  if (isNaN(chunkCount)) {
    return null;
  }
  var data = '';
  for (var i = 0; i < chunkCount; i++) {
    var chunkKey = key + "_chunk_" + i;
    var chunkData = cache.get(chunkKey);
    if (chunkData != null) {
      data += chunkData;
    } else {
      return null;
    }
  }
  return data;
}