プログラミング学習

GoogleスプレッドシートとGASの使い方(取得/書き込み)

プログラミング学習

覚書です。bot開発などに必要だったマメ知識です。

体験談:個人開発におすすめで安いVPSサーバー!コスパよしXserver VPS!

GoogleスプレッドシートとGASの使い方

スプレッドシートに値を書き込む

A列1行目にテストと書き込みます。

sheet.getRange(1, 1).setValue('テスト')

行と列のインデックスは0から

Google Apps Scriptでスプレッドシートのデータを扱うとき、行と列のインデックスは0から始まります。うっかりと数え間違いをするといけません。

getActiveSheet()は使わない!?

複数のシートがある場合、nullや最初のシートを取得してしまう場合があるよう。やはり明示的にシート名は指定した方がよい気がします。ただ、ユーザーにシート名を変更しないように促す注意喚起は必要そう。

const sheet =SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
↓
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName)

SpreadsheetApp.openByIdとSpreadsheetApp.getActiveSpreadsheet().getSheetByNameの違い

スクリプトがそのスプレッドシートに直接付随している場合はシート名を指定すればいいだけ。

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("schedule");

スクリプトが別のスプレッドシート（たとえば、異なるスプレッドシートのイベントトリガーなどから実行される）から呼び出される場合や、複数のスプレッドシートに対して動作させたい場合はスプレッドシートのURLにあるIDが必要。

const spreadsheet = SpreadsheetApp.openById("2qurRSayFASTkFedU8hfqRQpRgNB9AOk56v7kyD-MXdI");
const sheet = spreadsheet.getSheetByName("schedule");

gasからスプレッドシートのカレンダーの値を文字列で取得する

スプレットシートから日付のデータを取るのは、getDisplayValueがよさそうです。

function getDateFromSheet() {
  const displayValue = sheetName.getRange('C3').getDisplayValue();
  console.log("displayValue:", displayValue);
}
  • getValue()：データ型、1つのセル
  • getDisplayValue()：文字列、1つのセル
  • getValues():データ型、１つ以上のセル
  • getDisplayValues():文字列、1つ以上のセル

getDisplayValueは文字列なのでそのまんま取れます。

2023/4/29

getValueは次のようになってしまいます。

Sat Apr 29 2023 00:00:00 GMT+0900 (Japan Standard Time)

スプレッドシートは2次元配列

文字列の取得は1次元配列ですが、スプレッドシートは二次元配列の考え方が基本のようです。

1列取得した場合

const dateValues = sheet.getRange(2, 2, lastRow - 1, 1).getValues();

次のような参照になります。

dateValues[i][0]

こちらのツイート先がわかりやすかったです。

2次元配列を1次元配列に変換する

flatメソッドは結構便利な気がしました。

// const scheduledDates = sheet.getRange("B2:B").getDisplayValues().flat();
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
const startRow = 2; // データの開始行
const lastRow = sheet.getLastRow();
const lastRow = sheet.getLastRow();
const scheduledDateRange = sheet.getRange(`B${startRow}:B${lastRow}`);
const scheduledDates = scheduledDateRange.getDisplayValues().flat();


sheet.getRange("B2:B") はスプレッドシート上の範囲 B2 から B 列の最終行までを表します。getDisplayValues() メソッドはその範囲内のセルの値を取得し、二次元の配列として返します。

getDisplayValues() の返り値は二次元の配列なので、flat() メソッドを使って一次元の配列に変換しています。これにより、B2 から B 列の最終行までの実施日が一次元配列 scheduledDates に格納されます。

配列になるわけです。

[  "2023/06/05 16:00:00",  "2023/06/03 03:30:00",  "2023/06/03 03:50:00"]

ただ、空行も取得してしまうため、上記のように一工夫いります。

GASのonEdit(e)にご注意

まずパフォーマンスがよくないらしい。チェックの頻度が高いものはなるべく使いたくない。

結構、トラブルの口コミも多かったです。結局、使わず別の方法で実装しました。

GASから1つのセルに複数のハイパーリンクを張りこむ

このコードいいですね!

How to extract the link from a cell now that links are not reflected as HYPERLINK?
stackoverflow.com
スポンサーリンク

Googleスプレッドシートの使い方

スプレッドシートでカレンダーを設定する

  1. 列を選択
  2. データ > データ入力規則 > ルールの追加 > 有効な日付
  3. カレンダーがC列だった場合、1行目は項目名。C1からC2に変更する

「有効な日付」以外に「日付」があって違いは何なのか?となる。この翻訳が紛らわしいのだが、「日付」はtodayのようです。つまり、今日以外の日付を入力すると無効化されます。だから、基本的に「有効な日付」を使いましょう。

スプレッドシートでセレクトボックスを設定する

  1. 列を選択
  2. データ > データ入力規則 > ルールの追加 > プルダウン
  3. カレンダーがC列だった場合、1行目は項目名。C1からC2に変更する
  4. プルダウンにすると項目名がでるため設定する。色もつけられる。

デザインは好みかもしれませんが、次に調整する方がよさそうです！

詳細オプション > 表示スタイルの[矢印]

スプレッドシートでセレクトボックスの色がコピペすると反映されない

データ入力規則のデータがおかしいようです。

矢印 > 鉛筆アイコンの編集ボタン > 色を設定する > 完了

すべてに適用しますか？
同じデータのアルゴリズムが他に996個あります。それらすべてに変更を加えますか？

適用するとシート内にある同類のセレクトボックスすべてに反映されるようです。

スプレッドシートで緑の枠を消す

フィルターを削除するなんて解説されたサイトが多かったですが、共有、複数タブでも緑の枠になるようです。タブを閉じると消えます。

ARRAYFORMULAが便利!

ARRAYFORMULAは1つのセルに計算式を入力すると、指定範囲すべてに数式の結果を反映してくれます。コピペやドラッグ&ドロップしないためミスが減ります。行や列、任意のセル範囲も可能です。

なお、Googleスプレッドシートの関数です。

範囲内に余計なデータが入っていると、#REF!になるため注意が必要です。

スプレッドシートで上部を固定化する

固定は

  • 表示 > 固定 > 1行
  • 表示 > 固定 > 2行

任意は行を先に選ぶと任意の行までがでるよう

表示 > 固定 > n行まで

解除は

表示 > 固定 > 行なし

スプレッドシートで列を閉じる

閉じたい行を選択

表示 > グループ化 > 列nからnまでグループ化

わかりにくいけど、右クリックからもできる

右クリック > 列での他の操作項目を表示

閉開は前回の状態が自動保存されるようです。デフォルトは開いた状態にした方がよさそうです。UI的に気づかない人がいるため。

スポンサーリンク

Googleスプレッドシートのファイルを別のGoogleアカウントに移行にする

実際にやってみると、わかりやすく簡単です。

移行元のアカウント

スプレッドシートのファイルはGoogleドライブに入っています。
Googleドライブから操作すると、どのGoogleのサービスでも利用できるため、ファイルの移行はここから行うとよいでしょう。

右クリック > 共有 > 共有 > 移行したいメールアドレスを入れる > 送信

もう一度、共有をみると、移行先の人が編集者として追加されています。

移行先のアカウント

ログアウトして移行後のアカウントしてログインします。

ファイルがあることだけ確認しましょう。なお、Gmailにもファイル共有の情報が届いています。

この作業は確認だけなので、慣れてきたら飛ばしてもかまいません。

移行元のアカウント

オーナー権利を譲渡します。

右クリック > 共有 > 編集者 > オーナー権限の譲渡 > 招待メールを送信

移行先のアカウント

Gmailに「オーナーへの招待状:…」というメールが届いています。
[承認]をおします。

Googleドライブで確認すると、承認者と編集者の立場が逆転しています。

移行元のアカウントも編集者として利用できます。

なお、スプレッドシートに紐づいたスクリプトも権限は一緒に移動するようです。オーナーを変えても問題なく動作するはずですが、権限の承認やloginなどはGoogleのアカウントごとに管理されているせいか再度やりなおすことになります。

ファイルはオーナーしか削除できません。

コメント

タイトルとURLをコピーしました