Google Apps Scriptは、なかなかいいんだけど
バージョン管理があんまりうまくできなくて
なので、ローカルに落として
Gitで管理する手順をメモメモ
まずは、node-google-apps-script をインストール！
|
1 |
$ npm install -g node-google-apps-script |
Google Developer Console のプロジェクトで Drive API を有効にし、
OAuth client ID と secret（json）を取得
下記のコマンドを実行して、返ってきたURLにブラウザでアクセス
|
1 |
$ gapps auth [jsonファイル] |
ファイルを落としたいフォルダに移動して
下記のコマンドを実行！
|
1 |
$ gapps init [キー] |
キーには、ScriptのファイルID、またはスクリプト IDを設定
以上！
Google Apps Scriptで処理を実行していて
[起動時間の最大値を超えました]
なんてメッセージが出たので対応方法をメモメモ
どうやら5分以上かかる処理をやっちゃダメよ！
ってことらしいのだ。。。
まぁ無限ループとかされてもねってことで
しょうがないのかな
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
function main(){ var startTime = new Date(); //トリガーを削除 var allTriggers = ScriptApp.getProjectTriggers(); for(var i=0; i < allTriggers.length; i++) { ScriptApp.deleteTrigger(allTriggers[i]); } // 適当なループ for (var i in xxxxxxx) { // 処理時間に5分以上経過している場合は、一度処理を終わらせ、再度実行させる var nowtime = new Date(); var nowdiff = parseFloat((nowtime.getTime()-startTime.getTime())/(1000*60)); if(nowdiff >= 4.8){ ScriptApp.newTrigger("main") .timeBased() .everyMinutes(1) .create(); return; } } } |
5分（この例だと正確には4分48秒）以上かかってたら
自分の関数を、1分間隔のトリガーにセットしてから処理終了。
で、再度始まったときにそのトリガーを削除するようにすればOK！
途中までの結果を残しておきたい場合は、
シートとかユーザプロパティやらになんか書き残しておこう！
Google スプレッドシートに
Google apps scriptでグラフを挿入
そんときの手順をメモメモ
感想としては微妙、、、
というか、スプレッドシートのグラフが微妙
エクセルで出来ることが、実現できない。。。
がんばれGoogle、、、あんまり力を入れてないのか、、、
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName('シート1'); var chart = sheet.newChart() .setChartType(Charts.ChartType.COLUMN) // 縦棒グラフ .addRange(sheetGraph.getRange('A1:D10') // グラフ範囲 .setPosition(1, 1, 0, 0) // 挿入位置 .setOption('width',800) // 横幅 .setOption('height',200) // 高さ .setOption('chartArea',{left:50, right:50, height:'80%'}) // グラフの位置 .setOption('isStacked', 'percent') // 積み上げグラフ true, percent .setOption('legend.position','none') .setOption('colors', ['#FF99CC', '#92D050']) // 色の指定 .setOption('vAxis.direction', -1) // メモリ反転 .setOption('vAxis.format', '0%') .setOption('vAxis.textStyle',{fontSize:8}) .setOption('vAxis.gridlines',{count:5}) .setOption('hAxis.textStyle',{fontSize:8}) .setOption('hAxis.gridlines',{color:'none'}) .build(); sheet.insertChart(chart); |
スプレッドシートをいろいろ触ってて
最終行を取得したくなったのでメモメモ
１．単純にシートの最終行を取得
|
1 2 |
var sheet = SpreadsheetApp.getActiveSheet(); var lastRow = sheet.getLastRow(); |
２．指定した範囲の最終行を取得
|
1 2 |
var sheet = SpreadsheetApp.getActiveSheet(); var lastRow = sheet.getRange('A1:A6').getLastRow(); |
３．指定した範囲で文字が入力されている最終行を取得
|
1 2 3 |
var sheet = SpreadsheetApp.getActiveSheet(); var list = sheet.getRange('A:A').getValues(); var lastRow = list.filter(String).length; |
var CLIENT_ID = ‘xxxxxxxxxxxxxx’;
var CLIENT_SECRET = ‘xxxxxxxxxxxxxx’;
var AUTHORIZE_URL = ‘https://accounts.google.com/o/oauth2/auth’;
var TOKEN_URL = ‘https://accounts.google.com/o/oauth2/token’;
var REDIRECT_URI = ‘urn:ietf:wg:oauth:2.0:oob’;
こちらのURLにブラウザでアクセスして承認コードを取得（search console api を使用する場合）
使用するAPIによってscopeを変更
|
1 |
AUTHORIZE_URL + '?client_id='+CLIENT_ID+'&redirect_uri='+REDIRECT_URI+'&scope=https://www.googleapis.com/auth/webmasters.readonly&response_type=code&approval_prompt=force&access_type=offline' |
次に、 取得したコードからリフレッシュトークンを取得
|
1 2 3 4 5 6 7 8 9 10 11 |
var res = UrlFetchApp.fetch(TOKEN_URL, { "method" : "POST", payload : { "code" : [取得したコード], "client_id" : CLIENT_ID, "client_secret" : CLIENT_SECRET, "redirect_uri" : REDIRECT_URI, "grant_type" : "authorization_code" }, muteHttpExceptions : true }); |
リフレッシュトークンを指定して、アクセストークンを取得
|
1 2 3 4 5 6 7 8 9 10 11 |
var opt = { "method" : "POST", "payload" : { "client_id" : CLIENT_ID, // 取得した クライアントID "client_secret" : CLIENT_SECRET, // 取得した クライアントシークレット "refresh_token" : refresh_token, // 取得したリフレッシュトークン "grant_type" : "refresh_token" }, "muteHttpExceptions" : true }; var res = UrlFetchApp.fetch(TOKEN_URL, opt); |
これで取得したアクセストークンを使用して
APIをたたけばOK！
有効期限が切れそうになったら、アクセストークンを
再取得しよー
Google Apps Script を使って
スプレッドシートにHTMLを表示させたいときのサンプル
HTMLといってもURL指定とかじゃなくて
htmlのソースをスクリプトエディタで作って、、、
まずは、スクリプトエディタから
新規作成でHTMLファイルを作る。
|
1 2 3 4 5 6 7 8 9 10 11 |
function showHtml(){ var ss = SpreadsheetApp.getActiveSpreadsheet(); var html = HtmlService.createHtmlOutputFromFile('example'); ss.show(html); } // 直接htmlを書く場合 function doGet(){ var html = HtmlService.createHtmlOutput('<p>Hello World!</p>'); return html; } |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
function doGet() { // データ作成 var data = Charts.newDataTable() .addColumn(Charts.ColumnType.STRING, 'Month') .addColumn(Charts.ColumnType.NUMBER, 'In Store') .addColumn(Charts.ColumnType.NUMBER, 'Online') .addRow(['January', 10, 1]) .addRow(['February', 12, 1]) .addRow(['March', 20, 2]) .addRow(['April', 25, 3]) .addRow(['May', 30, 4]) .build(); // グラフを作成 var chart = Charts.newAreaChart() .setDataTable(data) .setStacked() .setRange(0, 40) .setTitle('Sales per Month') .setOption('vAxis.direction', -1) .build(); // 画像を作成 var img = DriveApp.createFile(chart.getBlob()); // 画像のURLを取得してimgタグ作成 var url = img.getDownloadUrl().replace('?e=download&gd=true',''); var html = '<img src=\"'+url+'\">'; img.setTrashed(true); return HtmlService.createHtmlOutput(html); } |
・参考
https://developers.google.com/apps-script/reference/charts/
GASを学んでみる
自己証明書を使ったベーシック認証のかかったAPIにアクセスして
スプレッドシートに取得した値を表示してみる。
// スプレッドシートが開かれたタイミングで実行される
function onOpen(event){
var menuitems = [
{name:’データ取得’, functionName:’getData’}
];
var sheet = SpreadsheetApp.getActiveSpreadsheet(); // アクティブなシート
sheet.addMenu(‘★’, menuitems); // スプレッドシートにメニューを追加
}
function getData(){
var apiUrl = ‘https://xxxx.com’;
// リクエストの中身
var extUrl = ‘Api/getLog’,
auth_data = Utilities.base64Encode(‘user:pass’), // ベーシック認証のURL
headers = {‘Authorization’ : ‘Basic ‘ + auth_data},
params = {
‘headers’ : headers,
‘validateHttpsCertificates’ : false // 自己証明書の場合、証明書のチェックしない
};
// fetch
var response = UrlFetchApp.fetch(apiUrl + extUrl , params),
result = JSON.parse(response.getContentText());
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
for (var i = 0; i < result.length; i++) {
var val = result[i];
sheet.getRange(10+i,1).setValue(val);
}
}