More than 1 year has passed since last update.

ノンプロ研 GAS初級講座21期補講 #6-7

Last updated at 2023-02-26Posted at 2023-02-23

概要

ノンプログラマーのためのスキルアップ研究会 GoogleAppsScript初級講座
第6回「スプレッドシートの操作（実践編）」
第7回「HTTP通信・API」
についての非公式の補講記事です。
21期講師のTaitoが執筆しています。

第6回スプレッドシートの操作（実践編）

ここだけおぼえるポイント

構造化データを配列で操作しましょう
構造化データのポイント
- 1シートに1テーブル
- A1セルからレコード×フィールド
- 空行や空列は入れない
- フィールドの見出しは１行に
- セルの結合を使わない
配列操作の頻出メソッド
- push
  - 末尾に追加
- shift
  - 先頭を削除
- indexOf
  - 配列内の位置を検索して番号を返す
- includes
  - 配列内に含まれるか検索してTRUE FALSEを返す

構造化データをgetDataRange().getValues()で2次元配列化して
配列操作メソッドで適宜整形し
setValues()で貼り付ける
のが基本的な流れとなります。

配列の操作(基本編)

たとえば以下のテーブルから

男性のレコード
名前のフィールド
を取り出して、新しいシートに貼り付ける流れを見てみましょう。

const values = SpreadsheetApp.getActiveSheet().getDataRange().getValues();

// 1行目 を取り出してheaderに代入
const header = values.shift();

// 名前 フィールドの列番号を取得
const index = header.indexOf('名前');

// 貼付け用の新配列を宣言、ヘッダーを追加
const newValues = [];
newValues.push(['名前', '性別']);

// 各レコードについて「男性」が含まれていれば、新配列に追加する
for(const record of values) {
  if (record.includes('男性')) {
    newValues.push([record[index], '男性']);
  }
}

// 新しいシートを作成し、新配列を貼り付ける
const newSheet = SpreadsheetApp.getActive().insertSheet('男性');
newSheet.getRange(1, 1, newValues.length, newValues[0].length).setValues(newValues);

結果

この基本の流れを踏まえて、さまざまな操作を実現しましょう。

配列の複製(発展)

破壊的メソッドは元の配列を変更してしまいます。
コード上で意図しない挙動をすることがあるので、出来ればコピーを使いましょう。

とお伝えしましたが、コピーを作るのはちょっと難しいです。
「破壊的メソッドの影響を受けたくない！」と思う時がきたら、見てください。

  const array = [['name', 'age'], ['Bob', 25]];

  // スプレッド構文 シャロー(浅い)コピー 値の再代入の影響を受ける
  const shallowCopy = [...array];

  // スプレッド構文 ディープ(深い)コピー
  const deepCopy = [];
  for (const record of array) {
    deepCopy.push([...record]);
  }

  // JSON化してから元に戻しても、ディープコピーになる
  const deepCopy2 = JSON.parse(JSON.stringify(array));

  array.shift(); // 破壊的メソッド
  array[0][1] = 30; // 値の再代入

  console.log(array);       // [ [ 'Bob', 30 ] ] 元の配列
  console.log(shallowCopy); // [ [ 'name', 'age' ], [ 'Bob', 30 ] ] シャローコピー
  console.log(deepCopy);    // [ [ 'name', 'age' ], [ 'Bob', 25 ] ] ディープコピー
  console.log(deepCopy2);   // [ [ 'name', 'age' ], [ 'Bob', 25 ] ] ディープコピー

構造化データ(発展)

講座内では
「非構造化データはプログラムで解決しづらいので使わないように！」
とお伝えしましたが、
「でもやるとしたら？」のコードを書いてみます。

// まずはデータを取得
const values = SpreadsheetApp.getActiveSheet().getDataRange().getValues();

// 構造化したデータを追加する為の配列を宣言
const newValues = [];

// 空白行を無視する
for (const record of values) {
  if (record.join('') !== '') newValues.push(record);
}

// 最初の列は空白なので削除
for (const record of newValues) {
  record.shift();
}

// team名が空白(結合セルのデータが入っていない部分)であれば、一つ上の行のチーム名を追加する
for (const [row, record] of Object.entries(newValues)) {
  if (record[0] === '') record[0] = newValues[row - 1][0];
}

// 2つ目のヘッダーを削除
newValues.splice(3, 1);

// 結果
[ [ 'team', 'name', 'age', 'favorite' ],
  [ 'A', 'Bob', 25, 'apple' ],
  [ 'A', 'Tom', 32, 'orange' ],
  [ 'B', 'Jay', 28, 'grape' ],
  [ 'B', 'Ivy', 24, 'banana' ] ]

ということで、
講座で教えていないメソッドや構文も使って、
これだけ長々と書いて、なんとか整形できました。
データが構造化されていれば必要ない手続きです。
構造化の大事さが伝わればと思います✨

第7回 HTTP通信・API

ここだけおぼえるポイント

Hyper Text Transfer Protocol はインターネット通信の基本となる規格
- UrlFetchApp.fetch(URL, params) でリクエストを行う
- リクエストは主にGETとPOST
- Webサーバーからのレスポンス(JSON文字列)をオブジェクトに変換して処理する
Application Programming Interface はアプリケーションをプログラムで操作する為の入口
- APIを用いて通信することを「APIを叩く」と言います
- ログインが必要なアプリの場合は APIのキーやトークンが必要

APIについては、
実際に自分が操作したいアプリ/サービスで試していただくのが一番勉強になるので
Slack、Notion、Twitter、LINEなど
GASで動かしたいものがあったら、都度ご相談ください！

カスタム関数(余談)

GASのコードエディタに記述した関数は、スプレッドシートから呼び出すことができます。
たとえばひらがなをカタカナに変換する機能はこちらです。
※文字列のreplaceメソッド、正規表現、アロー関数、文字コード変換を使っています

/**
 * ひらがなを引数に取り、カタカナを返すカスタム関数
 * @param {string} hiragana - ひらがな
 * @return {string} カタカナ
 * @customfunction
 */
function katakana(hiragana) {
  return hiragana.replace(/[あ-ん]/g, kana => String.fromCharCode(kana.charCodeAt(0) + 0x60));
}

結果

よく使う処理をカスタム関数化しておくと、数式の見通しがよくなります。

スプレッドシートの名前付き関数という基本機能でカバーできることもありますが、
GASの処理を挟む必要があるものは、カスタム関数で実装しましょう。

OpenAI API(余談)

最後に、最近流行りのOpenAIのAPIを叩くためのコードを共有します。
これまでの講座の知識で理解できる内容です。
ぜひぜひ遊んでみてくださいね。

以下のサイトから会員登録をします

設定画面からAPIキーを取得しましょう
18ドル分の無料クレジットが付与されます
無料分を超過したら使えなくなります。自動で課金されることはありません。

/**
 * OpenAIの回答を返すカスタム関数
 * @param {string} prompt - 質問文
 * @return {string} AIの回答
 * @customfunction
 */
function chatAI(prompt) {

  /** エンドポイントとAPIキー */
  const url = "https://api.openai.com/v1/completions";
  const apiKey = "APIキー";

  /** OpenAI設定 */
  const payload = {
    model: "text-davinci-003", // AIモデル
    prompt: prompt, // 質問文
    max_tokens: 256, // 応答トークン数
    temperature: 0.8, // 回答の自由度
    // その他パラメータあり
  };

  /** UrlFetchApp.fetchに渡すパラメータ */
  const params = {
    contentType: "application/json",
    headers: { Authorization: "Bearer " + apiKey }, // 認証情報
    payload: JSON.stringify(payload), // 送信データ
  };

  /** HTTP通信はtry~catch文で実行 */
  try {
    const response = UrlFetchApp.fetch(url, params); // fetchを実行
    const contentText = response.getContentText(); // HTTPResponseからJSON文字列を取得
    const obj = JSON.parse(contentText); // JSON文字列をオブジェクトに変換
    return obj.choices[0].text; // AIの回答
  }
  catch (e) {
    return e.message;
  }
}

結果

100文字以内にはなっていないですが。。。

この記事はざっと流し読みしてもらって
皆さんは卒業LTに取り組んでいただけたらと思います！
発表楽しみにしております✨✨

参考書籍

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

ノンプロ研 GAS初級講座21期 補講 #6-7

概要

第6回 スプレッドシートの操作（実践編）

ここだけおぼえるポイント

配列の操作(基本編)

配列の複製(発展)

構造化データ(発展)

第7回 HTTP通信・API

ここだけおぼえるポイント

カスタム関数(余談)

OpenAI API(余談)

参考書籍

ノンプロ研 GAS初級講座21期補講 #6-7

第6回スプレッドシートの操作（実践編）