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

スポンサーリンク
広告

ちょっと大量に

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

【バリアフリー】こだいらバリアフリーアプリ更新 東京都オープンデータなど【PWA】
先週つくったPWAアプリ みんなの入力 一応、どれくらいの方がアクセスしたかがわかります。 小平バリアフリー情…

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

Python(JupyterNoteをBinderで動かす)

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

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

Binder Githubはこちらです

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

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

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

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

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

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

小平バリアフリー情報

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

どんなかというと。。

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

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

こりゃめんどくさい(笑)GASだな

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

GASでGoogleスプレッドシートのセルの値、行数や列数を取得したり、セルに値を入力したりする基本 (1/2)
Googleが提供するGoogle Apps Script(GAS)のプログラミングで、Google Apps(主にスプレッドシート)を操作する方法を解説していく連載。今回は、スプレッドシートのオブジェクトを整理し、セル操作に関する基本的なメソッドの使い方を紹介する。
Excel VBAプログラマーのためのGoogle Apps Script入門
「Excel VBAプログラマーのためのGoogle Apps Script入門」の連載記事一覧です。
【GoogleAppsScript】スプレッドシート操作(セルデータの取得偏) - Qiita
Google Apps Script からスプレッドシートを操作する方法です。 #スプレッドシートの中の特定のシートを取得する ```js var spreadsheet = SpreadsheetApp.openById(...
複数の Google スプレッドシートのデータを集約する - Qiita
Google スプレッドシート、便利ですよね :-) で、いろいろと使うようになると、下記のようなケースもあるのではないでしょうか? * 営業所ごとに作成されたスプレッドシートのデータをまとめたい * 経理ソフトにインポートする...

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

やりたいこと

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

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

ということをやりたい。

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

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

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

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

Google Spreadsheet シート名を取得 [Google Apps Script]
以前の記事でも書きましたが、Googleのサービスが好きで、特にGoogle Spreadsheetの活用が大分好きです。 今回は、Google Sp

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

Google Drive上の指定のフォルダ以下のファイル一覧をSpreadSheetに書き出す - Qiita
## はじめに 前回、 (

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

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

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

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

【Google Apps Script(GAS)】配列と処理速度
Google Apps Scriptでスプレッドシートの処理を自動化する上で、配列を扱えるようになるのは非常に重要です。 なぜかというと、配列を使って処理を行わないと、処理の量や内容によっては非常に、とても、すごく重くなるからです。 今回は、Google Apps Scriptにおける配列についてご紹介します。

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

【初心者向けGAS】スプレッドシートのセル・セル範囲とその値を取得する方法
初心者向けにBotづくりを目指してGoogle Apps Scriptプログラミングの基礎をシリーズでお伝えしています。今回は、Rangeオブジェクト、つまりセルやセル範囲とその値を取得する方法です。
Spreadsheet Service  |  Apps Script  |  Google Developers

↑でも本家をみなきゃできないですね(笑)

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

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

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

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

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

function Tom_getFileListInFolder() {
//フォルダIDを指定して、SpreadSheet の シート1に、ファイル名と、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’; //デフォルトのシート名がシート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をスプレッドシートにとってくるスクリプト【ちょいスクリプト】
GlideやGoogle Spread Sheet の操作 Glideで写真のURLをとってSpread Sheetに張りたい時などにいち...

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

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

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

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]; // 後ろから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の6分の壁にあたりますし、APIを使わないと、行列操作とワークシートへの書き込みで、はまりました(笑)

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

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

GAS と Stackdriver(Logging, Error Reporting) を連携させる - Qiita
(に App Maker 記事ばっかり書いてきたけど最後の日なので GAS ネタ...

少しサンプルです。

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用のファイルの1シートに入れました。

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

小平バリアフリー情報

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

小平 バリアフリーを集めよう!
小平あたりのバリアフリーなお店の情報を集めましょう! みんな協力してね!  ☆いたずらを避けるためにメールアドレスの入力を必須にしています。 ☆メールアドレスはアプリ上では表示されませんし、本活動以外では利用いたしません。 活動に関するお問い合わせは、下記の こだいらあたりでシビックテックのHPからお願いします。

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

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

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

小平バリアフリー情報

Python で Google Colabでやる方が楽だったかも(笑)

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

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

gspreadライブラリの使い方まとめ!Pythonでスプレッドシートを操作する
Python3.x系でも使うことができるスプレッドシートのライブラリ『gspread』の使い方について、とっても詳しく解説します。公式のリファレンスは英語なので、英語アレルギーな人でも、この記事を読めばPythonから縦横無尽にスプレッドシートを操ることが出来るでしょう!

こちらは、サンプル。。

Google Colaboratory

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

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

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

コメントを残していただけるとありがたいです

Loading Facebook Comments ...
%d人のブロガーが「いいね」をつけました。