【GAS】複数のSpread sheetの中のデータを集約してひとつのシートに記入する方法【Google検索とコピペで作る】

ちょっと大量に

PDFから、データを作ったり、エクセルシートから情報を集約しなおす作業が発生（笑）

【バリアフリー】こだいらバリアフリーアプリ更新　東京都オープンデータなど【PWA】

もらった情報がまずはPDFだったり。。それがイマイチなので、エクセルでもらったり。でも。。エクセルで書かれていて、しかも、そのエクセルは人間がわかりやすいようにセル結合などが行われている形式。。そのままPDFにしたんでしょうね。

Python（JupyterNoteをBinderで動かす）

PDFからPythonで、TEXTにする方法は、

PythonでPDFminerというライブラリを使って、Binderで動くようにしてやってみました。

　Githubはこちらです。

Binder便利ですね。どこからでもPythonプログラムができる。requirement.txtと.ipynb　(Jupyter Notebookファイル)があれば、コンテナが動いて環境設定なしで動かせる。

さくって、プログラミング！できてファイルもできる↓

こいつを保存する方法もできる(笑)

でもでも。。これだと、表がくずれてしまって、人間が分からない状態をコンピュータはわかるはずないので。。取り出すプログラムを書くのを断念（笑）それでエクセルの方を送ってもらったわけですが。。

今度は人間にはわかりやすい形式もコンピュータにはわかりにくい。。

何のデータかというと。。市のバリアフリー情報

https://barrier-free.glideapp.io/

アプリに投入しようと思ったら。。情報を抜き出してコンピュータがわかりやすい形に変えてあげなくてはいけない。。　しかもファイルが１０分割されていて、その中にタブ（Gooleではシート）が何個も入っていてその中にある。タブ名（シート名）は設備情報という形。。

どんなかというと。。

いっぱいファイルに分割されてる。。

でシートごとに情報がある(笑)　しかも。。　表がいろいろ結合されている。。

こりゃめんどくさい（笑）GASだな

で・・・エクセルマクロでやろうかな？って思ったのですが、GASでやってみようかな？ってことで。。

https://www.atmarkit.co.jp/ait/articles/1702/09/news021.html

https://www.atmarkit.co.jp/ait/series/5004/

https://qiita.com/chihiro/items/3e1d17b78676c6a39d24

https://qiita.com/kz_takatsu/items/a89e89a4c5e82414ae3f

このあたりを参考にして、まず、何ができるのかを見てみて（最近のプログラムは、もともとライブラリーというか関数が入ってないと、できることが限られているんですよね。。Cで書くわけにも行かないし、CでGoogle Driveに入れたSpreadsheetを見る方法を調べてたら日が暮れるし）

やりたいこと

まず、こういうのを読む時に、注意です。何をやりたいか？を書いてみることが重要ですね。なぜか？っていうと、読む量がとっても多いから、連続して読んでいる間に、何がしたかったんだっけ？？ってなるためです（笑）

1. 複数のスプレッドシートを、ひとつにしたい。（各シートはそのままでいい）
2. ひとつにしたスプレッドシートから、シートの中にある設備名と住所とバリアフリー度って書いてあるものの下の行にある項目を取り出す。
3. それを新しいシートに、うまい具合に入れていく。

ということをやりたい。

ということで、ざ～っと上のやつを読んで。。Googleでの検索のキーワードになるやつを実は選んでいるだけで、本当にしたいことを、上のHPから見ているわけではないです。

なぜかって言うと。。　もう、作っている人。。居そうだし（笑）

たとえば、２の項目で、スプレッドシートから、シート名を取ってくるスクリプトが必要になるんですが。。

Google で、GAS spreadsheet Sheet名 取得　と検索すると　以下のようなページにたどりつきます。ここには、ひとつのスプレッドシートに複数のシートがある場合の、シート名を取得するスクリプトが書かれています。（ほら。。あったでしょ）

https://www.wmapst.net/google/gss/20161207-google-spreadsheet-sheet-name-get/

ちなみに１は、Google　Drive ファイル名　取得　で。。ありますよね。

https://qiita.com/akiko-pusu/items/43c89dcfeb1d544cce38

あとは、３を考えればいいということになるわけです。

GAS久しぶりなんで・・忘れてます(笑)

GASで取り込んだデータを操作するには、配列や、SpreadSheetに対する操作を少し理解する必要があります。っていうか、もともとプログラマーでもないので、覚えてないんですよね。。

配列に対しての操作などがよくわかるページ

https://vba-gas.info/gas-array

SpreadSheetに対する操作などがよくわかるページ

https://tonari-it.com/gas-spreadsheet-range-value-values/

https://developers.google.com/apps-script/reference/spreadsheet/

↑でも本家をみなきゃできないですね（笑）

これらを、さくっと検索して(笑)

Step1　Google　Driveの特定のフォルダーからファイル名、URLを抽出

そして、ワークシートに書き込みする。

まずは、GASのはじまりは、SpreadSheetを作ります。そして。。スクリプトを書いていきます。

サンプルのコードは、こちら。。

function Tom_getFileListInFolder() {
//フォルダIDを指定して、SpreadSheet の　シート１に、ファイル名と、URLを取得して、SpreadSheet に書込み
var folder_id = ‘フォルダID; //フォルダIDを指定する
folder = DriveApp.getFolderById(folder_id);
files = folder.getFiles();
list = [];　//この変数のファイル名・URLが入っていきます
rowIndex = 1; // The starting row of a range.
colIndex = 1; // The starting row of a column.
ss, sheet,range;
sheetName = ‘シート1’; //デフォルトのシート名がシート１です。

//ファイル名を読み取り、名前とURLをlist配列に代入していきます。
while(files.hasNext()) {
var buff = files.next();
list.push([buff.getName(), buff.getUrl()]);
};

ss = SpreadsheetApp.getActive();
sheet = ss.getSheetByName(sheetName);
range = sheet.getRange(rowIndex, colIndex, list.length, list[0].length);

// 対象の範囲にまとめて書き出します
range.setValues(list);
}

そうすると、そのSpreadSheetに、こんな風に、ファイル名と、URLが書き込まれます。

このあたりの詳しいことは、ここにまとめました。実は、これだけ単独でいろいろ使えるScriptだと思います。

【GAS】Googleドライブのファイル名とURLをスプレッドシートにとってくるスクリプト【ちょいスクリプト】

Step２　それぞれのワークシートから、シート名を抽出

そして、ワークシートに書き込みする。　次に　シート名抽出します。

サンプルのコードはこちら

function tom_getsheetidfromval(){
//現在のシートからSpreadSheetのIDを取得
Logger.clear();

for (var ii = 2; ii< 12; ii++) {
var sheet=SpreadsheetApp.getActiveSheet();
var urls=sheet.getRange(ii,2).getValue();
//Logger.log(urls);
var paths = urls.split(‘/’);
//Logger.log(paths);
ssId = paths[paths.length – 2]; // 後ろから２つ目をとってくる
Logger.log(ssId);
//SpreadSheet を特定して、シート名を読み込む
var sheets = SpreadsheetApp.openById(ssId).getSheets();
var sheet_names = new Array();
if (sheets.length > 1) {
for(var i = 0;i < sheets.length; i++)
{
sheet_names.push(sheets[i].getName());
}
}
Logger.log(sheet_names);

sheetName = ‘シート1’;
ss = SpreadsheetApp.getActive();
sheet2 = ss.getSheetByName(sheetName);

for ( var j = 0; j<sheet_names.length -1;j++){
range = sheet2.getRange(ii, 3+j);
range.setValue(sheet_names[j]);
//Logger.log(sheet_names[j]);

}
}
}

そうすると、こういう風に、ファイル名、URL、シート名がならんだファイルができます。

こういう風にステップ踏んでおくと、あとで手戻りしても。。もう一回スクリプト動かせば、元に戻せるので。。便利です(笑)

Step 3　シート名　住所　バリアフリーの状況を読み込んでくる

これ・・ちょっとハマりました（笑）

読み込んでくるのは簡単なんですが。。APIを多様して使うと、GASの６分の壁にあたりますし、APIを使わないと、行列操作とワークシートへの書き込みで、はまりました（笑）

施設名が、【シート名】　住所が【E8】にあって、バリアフリー情報は、【E37:F42】にある。このバリアフリー情報を、getValues()で呼び込むと、２次元配列にはいるんですが、Pushで配列に入れると、１次元と２次元がまざって、うまくワークシートに書き込めない（笑）まぁ、ちゃんと考えれば、すべてを１次元にして、やればいいんですが、それを考えるのがめんどくさかったのです（笑）

結局、↓このログで出力して、SpreadSheetにコピペしました（笑）

https://qiita.com/howdy39/items/398f4969639a558c8487

少しサンプルです。

function Tom_getbarrerfree(){
var sheet=SpreadsheetApp.getActiveSheet();
var ii = 3;
for(ii=2;ii<3;ii++){
var url=sheet.getRange(ii,2).getValue();
var dataArr = new Array();
var j=0;
while (sheet.getRange(ii, 3+j).getValue() != “”) {
var dataArr = new Array();
var sheetname = sheet.getRange(ii, 3+j).getValue();
var ss = SpreadsheetApp.openByUrl(url);
var sssheet = ss.getSheetByName(sheetname);
dataArr.push(sheetname);
var ssadress = sssheet.getRange(8,6).getValue();
dataArr.push(ssadress);
var ssvalue = sssheet.getRange(39,5,5,2).getValues();
//len1=ssvalue.length;
//len2=ssvalue[0].length;
//for (coi =0; coi<len1-1;coi++){
// for (coj=0; coj<len2-1;coj++){
// dataArr.push(ssvalue[coi,coj]);
//}
//}
dataArr.push(ssvalue)
console.log(dataArr);
j++;
}
}
}

苦労の跡が(笑)　これを、コピペで、Glide用のファイルの１シートに入れました。

これで、無事、市の施設のバリアフリー情報がPWAアプリに反映されました。

https://barrier-free.glideapp.io/

みなさん、情報くださいね！

https://docs.google.com/forms/d/e/1FAIpQLSe5izGAMJmGzimCjDLAfkjYSMO9c4pxN5uRs_rVnbfAmiwfCg/viewform

コピペでGASを動かす時の注意

GAS のコピペを動かすと「文字が無効です」って出ることがあります。その時、（）とか、’とか、スペースとかに全角が入っていたりします。。なので、そういう文字を疑ってみてください(笑)

で・・・実際にできたデータは、Glideで市の施設のシートに貼り付けました。

https://barrier-free.glideapp.io/

Python で　Google Colabでやる方が楽だったかも（笑）

ここまでやってなんですが。。GASって、６分の壁があったり、直接ファイルに落とせなかったり（SpreadSheetを基本としているのですが、その書き込みが、きちんと２次元配列でないと、setValues()とかできれいに書き込めなかったり。。）もともとが、JavaScriptなので、行列に対してのハンドリング（操作）が結構めんどくさかったりするのですよね。　おじさんなので、コンソールとかにでてきてくれる方が楽なんですが。。なら、自分のPCにインストールして使えよっていう話もあるのですが。。プログラミング環境を構築するところから説明するのもめんどくさいんですよね。

いまだと、Google Colabで作ったりする方がpandasなども使えていいですね。

https://tanuhack.com/python/library-gspread/

こちらは、サンプル。。

https://colab.research.google.com/drive/1cKOzEf_YiH7pOlNKa8BmM0x3izdX_fHA

これ何気に便利ですね。Googleはやっぱ凄いですねぇ～　Binderより便利かも。。

Githubとも連携しているので。。編集とかもできるし。。Gistもできるし。。

ただこちらも、承認とか、OAuth関係は、ちょっと複雑です。