Goole App Script ( GAS ) の基本的な操作方法。13.スプレッドシートのセルの基本操作

前回の続き

前回までで、GASでコードを書いて実行する方法がわかった。

今回はセル操作

今回は、Spreadsheetの基本的な使い方を見ておく。

セルの取得＝範囲の取得

EXCELでも同じ。知っていると思うけど知らないと混乱するので注意。

セルを取得するとは、範囲＝１の選択範囲を作るということ。

範囲を選択する

let sps = SpreadsheetApp.getActiveSpreadsheet();
let sht = sps.getSheets()[0];
let rg  = sht.getRange("B5:B6");
g.activate();

範囲を選択するのに１つだけ選択したら、セルを選択。

let sps = SpreadsheetApp.getActiveSpreadsheet();
let sht = sps.getSheets()[0];
let rg  = sht.getRange("B6");
g.activate();

セルを取得

let sps = SpreadsheetApp.getActiveSpreadsheet();
let sht = sps.getSheets()[0];
let rg  = sht.getRange("B5");
rg.activate();

セルの値を取得

let sps = SpreadsheetApp.getActiveSpreadsheet();
let sht = sps.getSheets()[0];
let rg  = sht.getRange("B5");
rg.activate();
let val = rg.getValue();
console.log(val)  

セルの書式(表示形式)を取得

数値は、表示形式が重要なので、数値の表示形式を見ておく。

let fmt = rg.getNumberFormat();
console.log(fmt)

日付形式 yyyy/MM/dd 日付と時刻 yyyy/MM/dd H:mm:ss 時刻 HH:mm:ss

セルの値を更新

セルの値を更新して、数字の表示形式を入れていく。

  rg.setValue('2021年6月4日');
  rg.setNumberFormat('yyyy/MM/dd');

セルに背景を設定

  let sps = SpreadsheetApp.getActiveSpreadsheet();
  let sht = sps.getSheets()[0];
  let rg  = sht.getRange("B5");
  rg.setBackgroundRGB( 250,120,120 );

セルの背景色を解除

背景色を解除するのには、setBackgroundで null を設定する。 クリアでは、値ごとすべて解除される。

let sps = SpreadsheetApp.getActiveSpreadsheet();
let sht = sps.getSheets()[0];
let rg  = sht.getRange("B5");

rg.setBackground(null);

セルの枠線・ボーダーを設定

ボーダーは、範囲に対して設定する引数の順番に注意、

上下左右を設定する

let rg  = sht.getRange("B5");
rg.setBorder(true, true, true, true, false, false);

内側も含めてすべて設定する。

すべてを true にすると、全部設定できる。

rg.setBorder(true, true, true, true, true, true);

セルの枠線（ボーダー）をすべて解除

すべてを解除するには、false を設定する。

  rg.setBorder(false,false,false,false,false,false);

変更しない＝null を設定する。

下だけ変更したいときのように、他を変更したくないときは、 null を渡す。

  rg.setBorder(null,null,true,null,null,null);

引数がどうしても多くなるので使うときに不便ではあるが、覚えておく必要がある。

セルのボーダーの枠線スタイルを決める。

SpreadsheetApp.BorderStyle.SOLID_THICK のように、名前空間の中に入れる。

• DOTTED / 点線
• DASHED / 破線
• SOLID　(* デフォルト) / 線
• SOLID_MEDIUM / 太線
• SOLID_THICK / 極太
• DOUBLE / 二重線

引数は、８番目。

range.setBorder(top, left, bottom, right, vertical, horizontal, color, style)

極太線を設定する。

rg.setBorder(true, true, true, true,
    true, true, 
    null,
    SpreadsheetApp.BorderStyle.SOLID_THICK
    );

枠線の色を決める。

色をは HTML と同じ色指定。256色なRGBを #FFFFFF と同じく入れる。

rg.setBorder(true, true, true, true, true, true, 
    "#88AA00",
    SpreadsheetApp.BorderStyle.SOLID_THICK
    );

次回へ続く

長いので分割しました→次回

Goole App Script ( GAS ) の基本的な操作方法。12. キーボード・ショートカット

スクリプト・エディタのキーボードショートカット

実行 / Ctrl-R

• 実行 / Ctrl -R / ⌘ -R
• ログ切り替え / Ctrl - Enter / ⌘ - Enter

実行は、選択中の関数が実行される。

選択中の関数とはこれ

選択中の関数とは、このメニューで選択している関数である。

カーソル位置の関数を実行してくれると楽なんだけど、毎回選択し直す必要があるみたい。

編集のショートカット

• 補完 ctrl-space / ⌘ -Space
• 単語補完 alt- / (スラ) / ⌘- /
• コメント・トグル ctrl - / (スラ)
• 行削除 ctrl -d / ⌘ - D
• インデント TAB
• アンインデント Shift-TAB
• コードの整形 / Alt-Shift-F

補完中の操作

補完中の操作は、通常のIDEと同じ

• 候補移動 矢印 ↑ ↓
• 選択 TAB / Enter

タブで補完決定できるのはちょっと好き。

コードの整形もできる

選択範囲のコードの整形ができます。

保存

• カレントファイル保存 Ctrl - S / ⌘ - S
• すべて保存 Ctrl-Shift-S / ⌘- Shift - S

キーボード・ショートカットの調べ方

エディタにフォーカスが存在するときに、F1を押せば、コマンドパレットが表示され、キーボードショートカットを見ることができる。

右クリックでメニューを表示

メニューを見れば、キーボードショートカットもわかる。

コマンド・パレットでショートカットを見る。

右クリックから、コマンド・パレットを参照すれば、さらに機能を見ることができる。

コマンドパレットを参照すれば、以外に多機能で、こんなこともできるんだ！っていう発見が多いので、一度は見ておくこと。

次回へ続く

長いので分割しました→ 次回

Goole App Script ( GAS ) の基本的な操作方法。11. ボタンに登録して実行

前回まで

前回までで、コードをスッキリさせて、好きなタイミング実行できるようにした。

今回は、ボタンで実行

今回は、さらにわかりやすくするために、ボタンに登録して実行することにする。

ボタンで実行を作る。

ボタンそのものは存在しないので、図形オブジェクトとして追加する。

手順

以下の手順で作成します。

• オブジェクトを追加（図形描画）
• ボタンを作成
• ボタンをのメニューをクリック
• スクリプトの割当

オブジェクトの追加

図形描画で図形を作ります。

ボタンを図形描画で作る。

同じ図形を２つ重ねて、枠線の太さと色を変えてボタンっぽくします。

出来ました。

ボタンっぽい画像を作りました。

クリックしてメニューを表示

メニューは、図形を選択すると現れます。

スクリプトの割当。

関数名を指定します。

図形をクリック

図形をクリックすると、割り当てた関数名が実行されます。

スクリプト割当後の注意。

いちど割り当てると、クリックで起動する。

マウスクリックが不能になる。移動・変更は右クリックでやる。

続く

長いので分割しました。

次回に続く→12

Goole App Script ( GAS ) の基本的な操作方法。10.メニューに登録して実行

前回の続き

前回までで、一通り実行ができるようになったので、他人に使ってもらうためにメニューに追加して実行する方法を見ておく。

今回はメニュー

今回は、メニューから好きなタイミングで任意に、スクリプトを実行します。

メニューで実行

メニューに登録して、任意のタイミングで実行。

function addCustomMenu(){
 //メニューを追加
  let my_menu = new Array();
  my_menu.push({name:"ダミー", functionName: "menu_dummy"});
  let s = SpreadsheetApp.getActiveSpreadsheet().addMenu("!!サンプル", my_menu);
}

メニューになる。

この関数を実行すると、メニューが生成され、いつでもスクリプトを実行できる。

起動後にメニューを生成

ドキュメントを開いたあとに、メニューを自動生成することもできる。

onOpen関数を定義すると、ドキュメントを開いた瞬間に実行される。onOpenは存在するだけで自動実行される。

function onOpen(){
    addCustomMenu();
}

メニューを追加（別の方法）

先の方法は、ショートカット的な方法で、メニューを作るのは、こっちがGAS的に正統方法だと思う。

function onOpen(){
  SpreadsheetApp.getUi().createMenu("!サンプル")
  .addItem('アイテム１', "func_name")
  .addItem('アイテム２', "func_name")
  .addSeparator()
  .addItem('アイテム３', "func_name")
  .addItem('アイテム４', "func_name")
  .addItem('アイテム５', "func_name")
  .addSeparator()
  .addSubMenu(
    SpreadsheetApp.getUi()
    .createMenu("アイテム6")
    .addItem('アイテム6-1', "func_name")
    .addItem('アイテム6-2', "func_name")
    .addItem('アイテム6-3', "func_name")
  )
  .addSeparator()
  .addItem('アイテム７', "func_name")
  .addToUi()

}

メニューの上書き

同じ名前で登録すると上書きされるので注意。

続く

長いので分割しました。

次回に続く→11

Goole App Script ( GAS ) の基本的な操作方法。09. 起動時に実行

前回まで

前回までで、ソースの管理、クラス、デバッグを見てきた。

そろそろ十分に準備が整ってきた。

今回はスクリプトの実行タイミング

ファイルを開いたとに、スクリプトを起動する方法を見る。

ドキュメントが開いたときにスクリプト起動

ドキュメントが開いときにスクリプトを起動する onOpen

onOpen 関数を定義しておけば、ファイルを開いときに自動的に実行してくれる。

ドキュメントを開いたとき実行は、少し遅い

onOpenの実行タイミングは、思った以上に遅いので注意する。

一見すると、ファイルが開いてるように見えてもすべての準備が整うまで実行されない。

ドキュメントのローディングバーが進む

メニューの下のローディングバーが進んで消える。

そのあと、最終更新時刻などが取得される。

その後に、ファイルがロードされてonOpenが実行される。（数秒は掛かる。）

シートが複雑だと、そこそこ時間がかかるので辛抱強く待つ必要がある。

複数ファイルにまたがった場合の onOpen

opOpen関数は、どこに書いてもいい。

スクリプトを複数のファイルに分割して書いたとしても、全部ロードしてから onOpenが探される。そのためonOpenをうっかり複数書くと後からロードされたものが優先される。

onOpen 関数はシンプルイベントハンドラなので、いつでも使える。

その他の実行方法

スクリプト・エディタで実行できるのだが、ドキュメント／スプレッド・シートからスクリプトを実行されないと使いにくい。

ファイルが開いたときに実行される onOpen があり、その他にもボタン（図形オブジェクト）にイベントハンドラを貼り付けて使うことができる。

続く

長いので分割しました。

次回に続く→10

Goole App Script ( GAS ) の基本的な操作方法。08.デバッガとグローバル・オブジェクト

前回まで

前回までで、スクリプトを作って、ファイルに分割してクラスに分けて管理するまで見た。

そろそろまともなコーディングができそうな気がする

今回は

今回はデバッグを使う。デバッグツールを使いGlobalオブジェクトを確認する。

デバッグで実行

行番号をクリックしてブレークポイントを設置できる。

ブレークポイントで止めておけば、グローバルオブジェクトをすべて参照できるので便利。

グローバルオブジェクトの一覧

デバッガを起動しブレークポイントで止めてけば、グローバルオブジェクトの一覧を見ることができる。

global な object 一覧は一度見ておくと、スッキリする。

意外なオブジェクトがあったりする。あと名前のルールが分かっていいと思う。

Utilities クラスとかあったりする。

続く

長いので分割しました。

次回に続く→09

Goole App Script ( GAS ) の基本的な操作方法。07.クラス作成でコード管理。

前回の続き

前回までで、スクリプトを実行し、関数に分割して管理、ファイルに分割して管理、ネットアクセスまでみた。

今回はクラス

今回は、ファイルに分割してするだけじゃなく、クラスにまとめるほう法をみて、さらにコードをスッキリさせる方法を見ておく。

classを使う。

class も完全な互換性があるわけでないが、ある程度使える。

ちょっと古い書式のJavaScriptのclassなので、たまにイライラするかもしれない。

クラスのサンプル

次のようなクラスをGASに作って実行してみます。

class MyClass{

  constructor() {
    this.name = "taro";
  }
  say(){
    console.log( ` My Name is ${this.name} ` )
  }
}
class Dog extends MyClass{

}

function main(){

  let obj = new Dog();
  obj.say()

}

クラスを使った実行

クラスを使ってコードを書いたら、そのあとの関数で実行ができる。

実行結果は次のようになる。

続く

長いので分割しました。

次回に続く→08

Goole App Script ( GAS ) の基本的な操作方法。06. HTTPアクセスでJSON取得

前回の続き

前回までで、ハローワールドを書いて、ファイル分割して管理できるまでみた。

今回は、ネットアクセス

今回は、URLへリクエストを投げて、Google外部のAPIを使ってデータ連携する方法を見ておく。

ネットからデータを取得

URLを指定して、HTTPアクセスでデータを取得したり、データを送信できる。外部のAPIと連携して通知を送ったりできる。

URLへHTTP GET

HTTPでGET もできる。UrlFetchAppオブジェクトがグローバル空間に存在する。UrlFetchAppを使えばオッケ

function  getCurrentIp(){
  let url =  'https://api.ipify.org?format=json'
  let json = UrlFetchApp.fetch(url);
  let obj = JSON.parse(json);
  console.log(obj)
}

HTTPアクセスの結果例

ちなみに、IPアドレスを取得すればわかるんですが、スクリプトは自分のPCで動いてるんすね。 てっきりリモートで動いていると思ってました。

URLにPOSTしてデータを取得 / HTTP POST

fetch の第２引数を指定すれば、POSTを使える。method にPOSTを入れるのですが、POST・GET以外は使えないようですね。

function  getCurrentIp(){
  let url =  'https://api.ipify.org'
  let options = {
  'method' : 'post',
  headers: { "Content-Type": 'application/json' },
  'payload' : 'format=json'
  };
  let json = UrlFetchApp.fetch(url,options);
  let obj = JSON.parse(json);
  console.log(obj)
}

続く

長いので分割しました。

次回に続く→07

Goole App Script ( GAS ) の基本的な操作方法。05. 複数ファイルに分割

前回まで

前回までで、スクリプトを使ってスプレッドシート側に操作ができるようになった。

今回は、ファイル分割。

単純な作業であれば、１つのファイルに全部書いていけばいいのでしょう。 しかし、まともにスクリプトを書くなら、ファイルに分割して書きたくなります。

今回は、ファイル分割をしてスクリプトを使う方法を見ていきます。

関数ファイルを追加する。

最初に用意されるファイルはコード.gs ( 英語版は CODE.gs )がファイル名のデフォルトになっている。

好きなファイル名で追加することができる。

メニューからファイルを追加を選んで押す。

スクリプトを選ぶ

好きな名前でファイルを追加できる。拡張子は入力不要

関数はグローバル空間に追加される。

作成したファイルは、起動前にすべてロードされる。そしてファイル中のグローバルな空間に関数として追加される。関数を追加するといつでも使えると思っていい。

関数以外のスクリプト

関数じゃなく、次のようにベタ書きしたスクリプトは、スクリプト起動前に実行される。

これは、関数を実行する前に実行されます。タイミングはロード時です。関数実行前に、全ファイルをロードします。関数外のスクリプトは、このロード時に実行されるので注意。

ロード順＝ファイル名の順

ロードの順序はファイルの順序（名前順）になる。ロード時に実行される関数を作るのであれば、ロード順にも配慮しないとだめ。

たとえば、同名の関数があれば後から読まれた関数で上書きされる。

ファイル名の順番でロードが後になるファイルで先のファイルに書かれた関数が、上書きされる。そのため後ファイルに置いた関数が優先される。

デフォルトの myFunction は、名前被りが多いです。ロード順に注意しないと、関数が実行されない。と悩む原因になります。

続く

長いので分割しました、次回に続く→06

takuya-1st.hatenablog.jp

Goole App Script ( GAS ) の基本的な操作方法。04.セルにハローワールド

前回の続き

前回までで、ハローワールド、関数を指定した実行、スプレッドシート側にハローワールドまでやった。

今回はドキュメント側に書き込む。

スプレッドシートにハローワールドを書き込んでみる。

スプレッドシートにハローワールドを書き込む。

スプレッド・シートのセルにハローワールドを書き込む。

function HelloworldToCell() {

  let app = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = app.getActiveSheet();
  let range = sheet.getRange('A1');

  range.setValue("Hello World.");
}

書き込んだら、実行します。

権限が付与されたら、セルにハローワールドが入力される。

続く

長いので分割しました。次に続く→05

Goole App Script ( GAS ) の基本的な操作方法。03.ダイアログでハローワールド

前回の続き

前回までで、コンソールにログをとして出力する。

今回は、スプレッドシートと連携する。

スプレッドシートと連携してハローワールドをスプレッドシート側に出す。

スプレッド・シートに出力ーダイアログ

スプレッド・シートにハローワールドをダイアログとして表示する。

スクリプトエディタで、Browser.msgBox関数を書き込んでいく。
このとき、補完を体験しておく。

Broと打てば補完が効く。

続けて msgBox で補完が効く。

ダイアログを表示する関数を作った

実行する

実行ボタンを押して権限を承認。

実行すると、権限チェックを求められます。

スクリプトがGoogle Spreadsheetにアクセスする権限が必要になります。

権限はそのまま許可してあげればオッケです

ダイアログが表示される。

権限の画面遷移が終われば、ダイアログが表示される。

権限は、初回起動時のみで、次回以降は権限があるので確認されない。

実行者ごとに権限。

自分のアクセスに関するアクセス権限なので、別人物（別アカウント）が実行するときにはまた別に権限が必要になる。

スクリプトはローカル

スクリプトはあくまでJSなので、ローカルのブラウザ内部で実行され。そのために、リモートにあるGoogle Spreadsheetへアクセス権を要求する。などと考えたら理解しやすいと思う。

続く

長いので分割しました。次に続く→ 04

Goole App Script ( GAS ) の基本的な操作方法。02.関数の追加

前回の続き

前回はハローワールドを実行した。

今回は関数を追加する。

関数を追加して、実行する。

関数を追加する。

２つ目の関数を作って使う。関数を作って使うために、サクッと関数を作る。

実行する関数を選ぶ

関数は実行時に選択する必要があるので、作った関数を選択する。

関数を選んでから実行する

関数を選んでから実行ボタンを押す。

実行ログ。

結果が出てくる。

console.log は実行ログ。

console.log は、実行ログとしてスクリプト・エディタに表示される。スクリプト・エディタでの実行ログは初期ロード時に非表示なので、画面構成が変化 するのですぐ気づけると思う。

続く

長いので分割しました。次回に続く→ 次 03

Goole App Script ( GAS ) の基本的な操作方法。01.コンソールでハローワールド

GAS を始める。

GAS を始めるときに、最初に手順を覚える。

最初の流れ。

1. Google Driveで新規スプレッドシートを作る
2. スプレッドシートを開ける。
3. ツールからスクリプト・エディタを開く
4. ツールから実行する

この記事で取り扱うこと

• スクリプトエディタの起動
• ハローワールドの実行（コンソール）
• 関数の追加と実行
• スプレッドシートにハローワールド（ダイアログ）
• スプレッドシートにハローワールド（セルに記入）
• 複数ファイル分割
• ファイルのロード順
• 最初に実行される関数
• メニューで実行
• ボタンで実行
• ショートカット

準備 / Google Driveで新規作成する

Google Driveで新規スプレッド・シートを作って、そのファイルを開く。

スプレッドシートを作り開く

右クリックメニューから新規、スプレッドシートを選んで、新規作成

スクリプトエディタを開く

スプレッドシートが開いたら、メニューからスクリプトエディタを選択する

スクリプト・エディタを別のウインドウにする

スクリプトエディタが別タブで開く。スクリプトエディタとスプレッドシートはペアで使う。

別ウインドウにしておいたほうが、動作チェックが楽。たくさん試すときは、別ウインドウにしておくほうがいい。

別ウインドウで開いておけば、スクリプトを実行したときにすぐに結果がわかる。視覚的にわかるので便利。

GAS でハローワールド

console.log でハローワールドを書く。

実行ボタンを押す。

上部メニューから、実行を押す。

下部に実行結果が出てくる

下部の実行ログの部分に、実行結果が表示される。

続き

長いので、分割しました。

次に続く→02　

potainer でユーザとチーム毎に管理できるホスト(endpoint)を設定する。

ユーザ毎にアクセス権（利用可不可）設定する。

Portainer は「Role単位の詳細権限」については、Bussiness版が必要なのだが、

チーム（グループ）とユーザ毎に、このDockerホストを許可する許可しないと設定できる。

少しわかりにくかったのでメモ。

Endpoints の右カラムから行う。

Portainerに登録された、エンドポイント（サーバー）を１単位として、ユーザにアクセス権を設定できる。 これで、複数のPotainerを起動しなくて済ませられるのは熱い。

アクセス権とユーザー単位で分けておけば、不意に他の人のインスタンスやサーバーをダウンさせてしまうことが減るので便利ですね。

事前にエンドポイントを設定する必要がありますけど。エンドポイントはdocker in docker は docker in lxc などで増やしてしまえばいいので。

docker別ホストから接続、管理SockをTCP経由許可して利用する。

docker の管理を別ホストから行いたい

ほとんどの人はDockerが動いているマシンへ SSHで接続してるともう。

ssh 経由で docker を使う場合

作業用PC ----<SSH>---- docker-host

リモートのDockerがインストールされたマシン中でdocker コマンドを叩いている場合

接続の詳細。

実際には接続がUNIX のソケット経由になっている。

作業用PC ----<SSH>---- docker-host----<unix:/socket>----/var/run/docker.sock

ん？unix/socket？そうですね。ソケットファイル経由です。
「だったら、ファイルじゃなくポートをリッスンすれば直接つながるのでは？」と思った貴方は大正解です。

tcp 接続でダイレクトに接続できる。

docker は TCPリッスンできるので、socket を経由しなくてもダイレクトに接続ができる。

作業用PC --------------- tcp ----------------- docker-host

SSH経由に比べると、ポートは開きっぱなしだし、認証関係もすっ飛ばすし、危険性が高いように思えるかもしれないが 仮想マシン間、開発マシンと仮想マシン、ローカル内部でこれを使うとサクッと接続できるので、便利です。

Docker のTCP接続を有効にする

-H を使って tcp へのリッスンを追加する。

/usr/bin/dockerd -H fd:// --containerd=/run/containerd/containerd.sock -H tcp://0.0.0.0:2375

ただしfd を消しちゃうと socket ファイル経由で接続ができなくなる。

ubuntu の docker にTCPをリッスンさせる

ubuntu で apt install docker.io でインストールした apt のdocker の場合、起動管理をsystemd が行っているのでsystemd のサービスファイルを書き換える。

sudo vim /lib/systemd/system/docker.service

該当サービスの ExecStartに -H tcp://0.0.0.0:2375 を追記する。

ExecStart=/usr/bin/dockerd -H fd:// --containerd=/run/containerd/containerd.sock -H tcp://0.0.0.0:2375

systemd をリロードして docker を再起動する

sudo systemctl daemon-reload
sudo systemctl restart docker

これでTCP経由で接続ができる。

クライアント側から docker接続する

docker コマンドは、DOCKER_HOST 変数で接続先を任意に指定することができる。

DOCKER_HOST=tcp://192.168.100.100:2375 docker ps

または、継続的に変数に入れてあげる。

export DOCKER_HOST=tcp://192.168.100.100:2375
docker ps 

これで、docker を任意のマシンからSSHすることなく操作ができる。

2375 はデフォルトのポートでWindowsのDockerが初期インストール時にデフォルトに設定されていたので。同じものにしました。

portainer / windows docker で使われています。

この機能、portainer や docker windowsで公式に使われています。注意深く見ていると気づいたと思います。

この公式機能であるポートリッスンを使えば、いちいちSSHしなくていんですよね。

portainer などと組み合わせる。

portainer など docker 管理のシステムと組み合わせるときにどうしてもこれらの設定が必要になってくる。

ポート管理に注意

ローカル中のdocker であれば、権限は ユーザーごとに決めることができるが、TCPをリッスンしてしまうと制御不能なので、取り扱い注意ですね。*1

参考資料

How do I expose the docker API over TCP? - Server Fault

*1:前時代のtcpwrapperやxinetd的な systemd.socketを間に挟んでゴニョゴニョすればできるんだろうけど