Googleスプレッドシートのデータをシェルスクリプトで読み込んで整形し、JSファイルとして書き出す。

ときどき更新されるスライドショーのようなもののために、静的にJavaScriptファイルをたくさん作る必要がありました。いろいろと試行錯誤した結果、Googleスプレッドシートでデータを管理して、シェルスクリプトで読み込んで処理するというものになりました。シェルスクリプトはよく知らないので、もっといい方法がある気がします。ひとまずは事足りたのでこちらにメモしておきます。

## Googleスプレッドシートの内容

| 日付 | 画像1 | 画像2 | 画像3 |
|------------|---|---|---|
| 2020/10/01 | A | B | C |
| 2020/10/02 |  |  |  |
| 2020/10/03 | A | C | F |
| 2020/10/04 | B | D | C |
| 2020/10/05 |  |  |  |

日付と画像ファイル名が指定されている。わけあって画像名に`.jpg`などの拡張子はつけられない。ブランクのところは更新が必要ない。


## 出力されるJSファイルの内容
各行ごとに以下のようなファイルが生成される。上記のスプレッドシートの場合は日付のみの行を除いた3行分、3ファイルを生成されます。
JavaScriptのファイル名は日付を整形した`1001.js`のような名前にします。各列は画像名として、`images/A.jpg`のようにします。
ここでは、配列をグローバル変数に入れているだけのJavaScriptですが、単純なテキストとして出力するだけなので、ある程度どんな形のものでもできるだろうと思います。
静的なファイルが必要ないならば、GASでスプレッドシートを整形するプログラムを書いて、「Webアプリケーションとして導入」したほうがいいと思います。

```js
//1001.js
window.example = [
    "images/A.jpg",
    "images/B.jpg",
    "images/C.jpg"
];
```


## 直接スプレッドシートをさわれない時
自分管理のスプレッドシートであれば特に必要ないですが、そうでないときは新しいスプレッドシートを作って、importrangeでデータを取り込みます。コピーしてもいいけど、元のスプレッドシートが更新される場合にはこのほうがいいと思います。
適宜filterなどを使ってデータを絞り込んだりします。今回はB列が空の場合は読み込まないようにしているのと、見出し行はいらないので2行目以降を読み込むようにしています。

```
=filter(
    importrange(
        "https://docs.google.com/spreadsheets/d/スプレッドシートのID/edit",
        "シート名!A2:D"
    ),
    not(
        isblank(
            importrange(
                "https://docs.google.com/spreadsheets/d/スプレッドシートのID/edit",
                "シート名!B2:B"
            )
        )
    )
)
```
一番左上のセルにこの数式を入れるだけです。スプレッドシートのIDとシート名は読み替えてください（以下同）。

## csvとして公開する
Googleスプレッドシートのメニューから、
`ファイル > ウェブに公開 > カンマ区切り形式（.csv）`
 でcsvとして公開できます。
あたりまえですが、公開されてしまうので、機密情報的なものは扱えません。

公開URLをメモしておく。
`https://docs.google.com/spreadsheets/d/e/.../pub?output=csv`
`...`は省略部分（以下同）。


## シェルスクリプトを書く
このcsvをシェルスクリプトで読み込んで処理します。今回の流れとしては、以下のようになります。
1. `curl`でcsvを読み込んでファイルに出力
2. `while read`で一行ずつ処理
3. 各行をカンマ区切りにして、一列ずつ取り出す
4. `sed`で日付のフォーマットを変える
5. 処理内容をjsファイルとして出力する

正直、シェルスクリプトは書きなれないので、もっとスマートにできると思いますが、ご容赦ください。

```bash
#!/bin/bash
status=`curl -w "%{http_code}" -o example.csv -L https://docs.google.com/.../pub?output=csv`

if test $status -eq 200; then
  while read row; do
    row=`echo ${row} | tr -d '\r\n'`
    columns=(${row//,/ })
    columns[0]=`echo ${columns[0]} | sed -E 's/[0-9]{4}\/([0-9]{1,2})\/([0-9]{1,2})/\1\2-/'`
    echo -e "window.example = [\\n  \"images/${columns[1]}.jpg\",\\n  \"images/${columns[2]}.jpg\",\\n  \"images/${columns[3]}.jpg\"\\n];" > ${columns[0]}.js
  done < example.csv
fi
```

## シェルスクリプトの解説

### curlでcsvファイルを取得

上記のURLはリダイレクトされるので、`-L`オプションをつけてリダイレクトを有効にします。つけないとリダイレクト前のページを取得してしまってうまくいきません。
また、今回は、レスポンスが `200` （成功）のときだけ処理するようにしたいので、 `-w` オプションでステータスコードを取得します。
`-w`オプションを付けると`%{variable_name}`の形式で指定したいろいろな値を抽出できます。ステータスコードの場合は`%{http_code}`と指定します。
`-o` オプションで内容をファイルに書き出します。

```bash
$ curl -w "%{http_code}" -o example.csv -L https://docs.google.com/.../pub?output=csv
```

ステータスコードは変数に代入します。`curl`の実行結果を代入することになるので、右辺は`` ` ``（バッククォート）か`$()`で囲みます。

```bash
status=`curl -w "%{http_code}" -o example.csv -L https://docs.google.com/.../pub?output=csv`
```

### ステータスコードのエラー処理
エラー処理というか、ステータスコードが `200` （成功）の場合だけ処理します。
`test` コマンドで変数 `status` を評価。

```bash
if test $status -eq 200; then
...
fi
```

略式の書き方だと

```bash
if [ $status -eq 200 ]; then
...
fi
```
となります。

### csvを一行ずつ処理する
`while read` にリダイレクト（`<`）でファイルを読み込んで一行ずつ処理する。
`done < example.csv`で先程出力したcsvファイルを読み込む。
`row` に各行が入ります。

```bash
  while read row; do
  ...
  done < example.csv
```

#### （参考）ステータスコードもファイル出力もいらない場合
ちなみに、curlで`w`オプションをつけない、かつ`o`オプションでファイル出力もしない場合、以下のようにすればいい。

```bash
csv=`curl -L https://docs.google.com/.../pub?output=csv`
while read row; do
...
done < <("$csv")
```
`<(list)`でコマンドの実行結果や変数展開をファイルのように扱うことができるようです。詳しくは、bashのマニュアルなどでProcess Substitutionを参照ください。
変数`csv`は改行を含んでいるので、`"$csv"`とダブルクォートで囲って展開しないとうまくいかない。
`<(curl -L https://docs.google.com/.../pub?output=csv)`のように`curl`コマンドを直接指定しても動作します。


### 各行を「,」区切りで列ごとに分けてリストにする

```bash
row=`echo ${row} | tr -d '\r\n'`
columns=(${row//,/ })
```
シェルスクリプトの配列（リスト）は、`var=(val1 val2 val3)`のように記述します。
`${parameter/pattern/string}`というパラメータ展開の式を使って、カンマ区切りのリストをスペース区切りに変換します。`pattern`が`/`で始まる場合は、`parameter`を展開した値の、 `pattern`にマッチするすべての部分が`string`に置換されます。今回は`${row//,/ }`なので、変数`row`を展開した値（たとえば、`2020/10/01,A,B,C`という文字列）の、`,`（カンマ）がすべて` `（スペース）に置換されます。
行末に改行が残ってしまうので、`tr -d '\r\n'`で削除しています。単純に`echo -n ${row}`でもいけるかなと思って、``row=`echo -n ${row}` ``としたものの、うまくいきませんでした。たぶん、`\r`（CR）が残ってしまうのかと想像します。

#### （参考）各列に個別の処理が必要な場合など
冗長だけど、例のスプレッドシートのように一行あたりの列が少ないならば、以下のように一列ずつ `cut` コマンドで、`cut -d , -f 1` のように、1列分ずつとりだしてもいいと思う。

```bash
column1=`echo ${row} | cut -d , -f 1`
column2=`echo ${row} | cut -d , -f 2`
column3=`echo ${row} | cut -d , -f 3`
column4=`echo ${row} | cut -d , -f 4`
```

各列に別の処理が必要な場合はこのようにすればいいと思う。
column1=`echo ${row} | cut -d , -f 1 | hogehoge...`
column2=`echo ${row} | cut -d , -f 1 | fugafuga...`

しかし、そんな場合はループで処理するほうがいいかもしれません。その場合は以下のようになるのかなと思います。適宜if文などを駆使して、各列にいろいろな処理を書くようにすればいいのかなと思います。今回は日付部分だけ`sed`で整形するだけなので、こういう処理はやってません。

```bash
i=0
for col in ${columns[@]}; do
    columns[$i]=`echo ${col} | hogehoge...`
    let i++
done
```

`for..in`ループの中でリストの項目を一つずつ参照するときに、`let i++`の値を加算して、配列のインデックスとして利用しているが、ここはもう少しいい方法があるんじゃないかと思って探したけれど、どうも見つけられなかった。ちなみに```col=`echo ${col} | ...` ```のようにしてもうまくいかない。


### 日付部分のフォーマット変更
`sed` コマンドで `2020/10/01` のような書式の日付を`1001`のように変更する。しかし、このくらいであれば、Googleスプレッドシートで書式を変更したほうがいいと思う。

```bash
columns[0]=`echo ${columns[0]} | sed -E 's/[0-9]{4}\/([0-9]{1,2})\/([0-9]{1,2})/\1\2/'`
```

環境によっては`E`オプションじゃなくて`r`かもしれない。


### JavaScriptファイルの出力

`echo`とリダイレクと（`>`）でJavaScriptファイルをつくります。`-e`オプションを付けるとエスケープが有効になります。
`echo -e "JSファイルの内容" > ${columns[0]}.js`という文になります。

```bash
echo -e "window.example = [\\n  \"images/${columns[1]}.jpg\",\\n  \"images/${columns[2]}.jpg\",\\n  \"images/${columns[3]}.jpg\"\\n];" > ${columns[0]}.js
```

### 参考
**・[Bash Reference Manual](https://www.gnu.org/software/bash/manual/bash.html)**
**・[curl](https://curl.haxx.se/)**
**・[curl コマンド 使い方メモ - Qiita](https://qiita.com/yasuhiroki/items/a569d3371a66e365316f)**
**・[sedでこういう時はどう書く? - Qiita](https://qiita.com/hirohiro77/items/7fe2f68781c41777e507)**
**・[標準出力をファイルのように扱う方法、例えば2つのコマンドの出力結果のdiffを取るとか - Qiita](https://qiita.com/kawaz/items/98768ba5eea8d6fe6a19)**