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アプリケーションとして導入」したほうがいいと思います。

//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ファイルとして出力する

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

#!/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 オプションで内容をファイルに書き出します。

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

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

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

ステータスコードのエラー処理

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

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

略式の書き方だと

if [ $status -eq 200 ]; then
...
fi

となります。

csvを一行ずつ処理する

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

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

（参考）ステータスコードもファイル出力もいらない場合

ちなみに、curlでwオプションをつけない、かつoオプションでファイル出力もしない場合、以下のようにすればいい。

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コマンドを直接指定しても動作します。

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

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列分ずつとりだしてもいいと思う。

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で整形するだけなので、こういう処理はやってません。

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スプレッドシートで書式を変更したほうがいいと思う。

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という文になります。

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
・curl
・curl コマンド 使い方メモ - Qiita
・sedでこういう時はどう書く? - Qiita
・標準出力をファイルのように扱う方法、例えば2つのコマンドの出力結果のdiffを取るとか - Qiita