LANG SELRCT

Apps Script Reference  (Create: Create new Spreadsheet | Create new Apps Script

Sunday, March 29, 2020

JIRA APIをブラウザで実行したい


以下のURLで
SITENAMEとPROJECT_KEYを各自のものに書き換えて
ブラウザのアドレスバーに入れて開くと
クエリの結果がブラウザで確認できます。

https://SITENAME.atlassian.net/rest/api/2/search?jql=project=PROJECT_KEY


補足

jql=の部分は
JIRAの課題の検索画面でのjqlと同様のようです。

https://SITENAME.atlassian.net/issues/?jql=project%20%3D%20PROJECT_KEY

UrlFetchApp.fetchAllを使って複数のfetchを同時にやって実行時間の短縮を試みる


今回やること
  • UrlFetchApp.fetchAllを試してみる


fetchとfetchAllを比較してみる。
  • それぞれに同じ数のリクエストを送って速度を比較してみる


Google Books APIで任意のキーワードを決めて40件の結果を出力してみる。

今回使うキーワード
  • API
  • Google
  • Chrome

仮説
  • fetchallは並列で処理してくれて、fetch1回分の処理時間で実行できるだろう


検証してみます


UrlFetchApp.fetch
で1つずつ処理する場合

コード.gs
function getBooksFetch() {
  getBooksWithParams1();
  getBooksWithParams2();
  getBooksWithParams3();
}

function getBooksWithParams1() {
  var url = 'https://www.googleapis.com/books/v1/volumes?q=intitle:API&country=JP&langRestrict=ja&maxResults=40&orderBy=newest';
  var json = UrlFetchApp.fetch(url);
  Logger.log(json)
}

function getBooksWithParams2() {
  var url = 'https://www.googleapis.com/books/v1/volumes?q=intitle:Google&country=JP&langRestrict=ja&maxResults=40&orderBy=newest';
  var json = UrlFetchApp.fetch(url);
  Logger.log(json)
}

function getBooksWithParams3() {
  var url = 'https://www.googleapis.com/books/v1/volumes?q=intitle:Chrome&country=JP&langRestrict=ja&maxResults=40&orderBy=newest';
  var json = UrlFetchApp.fetch(url);
  Logger.log(json)
}


結果をログで見ると1.769秒



UrlFetchApp.fetchAll
で同時に複数処理する場合

コード2.gs
function getBooksFetchAll() {
  var url1 = 'https://www.googleapis.com/books/v1/volumes?q=intitle:API&country=JP&langRestrict=ja&maxResults=40&orderBy=newest';
  var url2 = 'https://www.googleapis.com/books/v1/volumes?q=intitle:Google&country=JP&langRestrict=ja&maxResults=40&orderBy=newest';
  var url3 = 'https://www.googleapis.com/books/v1/volumes?q=intitle:Chrome&country=JP&langRestrict=ja&maxResults=40&orderBy=newest';
  var response = UrlFetchApp.fetchAll([url1, url2, url3]);
  Logger.log(response);
}


getBooksFetchAllを実行した結果は0.929秒


結果

今回の検証データでは仮説には及びませんでした。
しかし、約半分くらいの処理時間になりました。

もっと重たい処理を試すと、Googleのサーバに負荷をかけてしまうと思うのでここではやりませんが、今回のような軽くて3つくらいの処理でも確実にfetchAllの方が処理が早いことがわかりました。

同時処理数の上限についてリファレンスに記載が見つけられませんでしたが、QuotasにあるプランごとのURL Fetch callsがそれにあたるかもしれません。


補足

ちなみに認証が必要なAPIをたたく場合はこのように書くと実行できました。

コード3.gs
function getBooksFetchAll() {
  var request1 = {
    'url': 'https://foobar',
    'method' : 'get',
    'contentType': "application/json", 
    'headers': {"Authorization": " Basic " + TOKEN}
  }

  var request2 = {
    'url': 'https://hogefuga',
    'method' : 'get',
    'contentType': "application/json", 
    'headers': {"Authorization": " Bearer " + TOKEN}
  }
  UrlFetchApp.fetchAll([request1, request2]);
}


Friday, March 27, 2020

JIRAでラベルフィールドのサジェストを削除したい


以下のサポートページに書かれていた通りにやるとできました。

How to delete a label in Jira
https://confluence.atlassian.com/jirakb/how-to-delete-a-label-in-jira-297667646.html


どうやるか
  • 対象のラベルが設定されている全ての課題からそのラベルを削除する

それをどうやるか
  • 検索して一括処理でラベルを削除する


それを実際にどうやるかやってみた手順です。


STEP1
  1. 検索画面を開きます
    • https://SITENAME.atlassian.net/issues/
  2. 対象のフィールドで削除したいラベルを指定して検索します
    • 今回の例ではlabelsフィールドのラベル2を指定してみます
    • labels = ラベル2
  3. 右上の「…」を開きます
  4. Bulk change all 87 issue(s) の部分を選択します
    • 87件にラベル2がついているのでそのラベルを削除したい


STEP2
  1. Nextの下のチェックボックスをチェックしてすべて選択します
  2. Nextをクリックします


STEP3
  1. Edit issuesをチェックします
  2. Nextをクリックします


STEP4
  1. Change Labelsをチェックします
  2. 選択肢からFind and remove theseを選択します
  3. 削除したいラベル(今回はラベル2)を指定します

一番下までスクロールしてNextをクリックします。


STEP5
内容を確認して、Confirmをクリックします


STEP6
完了するまで待って、Acknowledgeをクリックします。


これで対象のラベル(ここではラベル2)が全ての課題から削除されました。


以上でラベルをリストから削除することができました。


参考

How to delete a label in Jira
https://confluence.atlassian.com/jirakb/how-to-delete-a-label-in-jira-297667646.html

Friday, March 20, 2020

Google Visualization APIでresponseのcolsに値が入らない場合がある


スプレッドシート側のデータや設定によって、Google Visualization APIで取得したデータの形式が異なるケースがあったので、試したパターンを書き残しておきます。

CASE1のようにすべてのセルを埋めておけば問題ないですが、空のセルがあるとresponseが変わってくるようです。



CASE1
すべてのセルが埋まっている場合は1行目がcolsに入ってきました。
(表示形式はABCを自動、Dを日付に設定)

response
 /*O_o*/
 google.visualization.Query.setResponse({
   "version": "0.6",
   "reqId": "0",
   "status": "ok",
   "sig": "705247912",
   "table": {
     "cols": [{
       "id": "A",
       "label": "midashi",
       "type": "string"
     }, {
       "id": "B",
       "label": "description",
       "type": "string"
     }, {
       "id": "C",
       "label": "tags",
       "type": "string"
     }, {
       "id": "D",
       "label": "created",
       "type": "date",
       "pattern": "yyyy/mm/dd"
     }, {
       "id": "E",
       "label": "",
       "type": "string"
     }],
     "rows": [{
       "c": [{
         "v": "見出し1"
       }, {
         "v": "説明1"
       }, {
         "v": "タグ1"
       }, {
         "v": "Date(2020,2,20)",
         "f": "2020/03/20"
       }, {
         "v": null
       }]
     }, {
       "c": [{
         "v": "見出し2"
       }, {
         "v": "説明2"
       }, {
         "v": "タグ2"
       }, {
         "v": "Date(2020,2,21)",
         "f": "2020/03/21"
       }, {
         "v": null
       }]
     }, {
       "c": [{
         "v": "見出し3"
       }, {
         "v": "説明3"
       }, {
         "v": "タグ3"
       }, {
         "v": "Date(2020,2,22)",
         "f": "2020/03/22"
       }, {
         "v": null
       }]
     }],
     "parsedNumHeaders": 0
   }
 });




CASE2
特定の列(D列)に1つだけ値が入っている場合はcolsに一行目以降の値も入ってきました。
(表示形式はABCを自動、Dを日付に設定)

response
/*O_o*/
google.visualization.Query.setResponse({
  "version": "0.6",
  "reqId": "0",
  "status": "ok",
  "sig": "131156031",
  "table": {
    "cols": [{
      "id": "A",
      "label": "midashi 見出し1 見出し2",
      "type": "string"
    }, {
      "id": "B",
      "label": "description 説明1 説明2",
      "type": "string"
    }, {
      "id": "C",
      "label": "tags タグ1 タグ2",
      "type": "string"
    }, {
      "id": "D",
      "label": "created ",
      "type": "date",
      "pattern": "yyyy/mm/dd"
    }, {
      "id": "E",
      "label": "",
      "type": "string"
    }],
    "rows": [{
      "c": [{
        "v": "見出し3"
      }, {
        "v": "説明3"
      }, {
        "v": "タグ3"
      }, {
        "v": "Date(2020,2,22)",
        "f": "2020/03/22"
      }, {
        "v": null
      }]
    }],
    "parsedNumHeaders": 0
  }
});




CASE3
データ範囲の表示形式をすべて「書式なしテキスト」にした場合はcolsに値は入らず、1行目からrowsに入ってきました。


response
 /*O_o*/
 google.visualization.Query.setResponse({
   "version": "0.6",
   "reqId": "0",
   "status": "ok",
   "sig": "703682130",
   "table": {
     "cols": [{
       "id": "A",
       "label": "",
       "type": "string"
     }, {
       "id": "B",
       "label": "",
       "type": "string"
     }, {
       "id": "C",
       "label": "",
       "type": "string"
     }, {
       "id": "D",
       "label": "",
       "type": "string"
     }, {
       "id": "E",
       "label": "",
       "type": "string"
     }],
     "rows": [{
       "c": [{
         "v": "midashi"
       }, {
         "v": "description"
       }, {
         "v": "tags"
       }, {
         "v": "created"
       }, {
         "v": null
       }]
     }, {
       "c": [{
         "v": "見出し1"
       }, {
         "v": "説明1"
       }, {
         "v": "タグ1"
       }, null, {
         "v": null
       }]
     }, {
       "c": [{
         "v": "見出し2"
       }, {
         "v": "説明2"
       }, {
         "v": "タグ2"
       }, null, {
         "v": null
       }]
     }, {
       "c": [{
         "v": "見出し3"
       }, {
         "v": "説明3"
       }, {
         "v": "タグ3"
       }, {
         "v": "43912"
       }, {
         "v": null
       }]
     }],
     "parsedNumHeaders": 0
   }
 });



CASE4
D列の表示形式を日付に戻すと、CASE2と同じくclosに1行目以外の値も入ってきました。

response
/*O_o*/
google.visualization.Query.setResponse({
  "version": "0.6",
  "reqId": "0",
  "status": "ok",
  "sig": "393464895",
  "table": {
    "cols": [{
      "id": "A",
      "label": "midashi 見出し1 見出し2",
      "type": "string"
    }, {
      "id": "B",
      "label": "description 説明1 説明2",
      "type": "string"
    }, {
      "id": "C",
      "label": "tags タグ1 タグ2",
      "type": "string"
    }, {
      "id": "D",
      "label": "created ",
      "type": "date",
      "pattern": "yyyy/MM/dd"
    }, {
      "id": "E",
      "label": "",
      "type": "string"
    }],
    "rows": [{
      "c": [{
        "v": "見出し3"
      }, {
        "v": "説明3"
      }, {
        "v": "タグ3"
      }, {
        "v": "Date(2020,2,22)",
        "f": "2020/03/22"
      }, {
        "v": null
      }]
    }],
    "parsedNumHeaders": 0
  }
});



CASE5
日付が入るD列に日付以外の値が入っていると、CASE2と同じくclosに1行目以外の値も入ってきました。例では日付の列に-を入れています。

response
/*O_o*/
google.visualization.Query.setResponse({
  "version": "0.6",
  "reqId": "0",
  "status": "ok",
  "sig": "374075250",
  "table": {
    "cols": [{
      "id": "A",
      "label": "midashi 見出し1 見出し2",
      "type": "string"
    }, {
      "id": "B",
      "label": "description 説明1 説明2",
      "type": "string"
    }, {
      "id": "C",
      "label": "tags タグ1 タグ2",
      "type": "string"
    }, {
      "id": "D",
      "label": "created - -",
      "type": "date",
      "pattern": "yyyy/MM/dd"
    }, {
      "id": "E",
      "label": "",
      "type": "string"
    }],
    "rows": [{
      "c": [{
        "v": "見出し3"
      }, {
        "v": "説明3"
      }, {
        "v": "タグ3"
      }, {
        "v": "Date(2020,2,21)",
        "f": "2020/03/21"
      }, {
        "v": null
      }]
    }],
    "parsedNumHeaders": 0
  }
});



参考

Google Visualization API Reference
https://developers.google.com/chart/interactive/docs/reference

Saturday, March 14, 2020

GitHub Pagesでウェブサイトのホスティングができる


以下の手順でmaster branchのindex.htmlを表示することができました。
  1. リポジトリを作る
  2. index.htmlを作る
  3. リポジトリのSettingsを開く
  4. GitHub PagesでSourceをmaster branchにする
  5. サイトURLにアクセスする


詳しい制限や使い方については公式サイトの GitHub Pages について に記載されているので、用法、容量を守って利用しましょう。


GitHubアカウントを持っていない場合

GitHubアカウントを作ってリポジトリをクローンしてみる
1. GitHubアカウントを作成する

の手順で作成できると思います。
または、ググったりして作成してみてください。


参考

Azure App Serviceを試してみる


Azureで無料のWeb Appの作成を試した時の備忘録です。

1時間の期限付きでウェブサイトの公開を試すことができました。

ここではHTML5の空のサイトを表示することができた手順を書き残しておきます。


手順

STEP1
https://azure.microsoft.com/ja-jp/services/app-service/
Web Apps を今すぐ試すをクリックします。


STEP2
Web Appを選択して「次へ」をクリックします。


STEP3
言語の選択で「HTML5」を選択して
「HTML5 空のサイト」を選択して
「Create」をクリックします。


STEP4
Web App を利用する のリンク
を開くと、デフォルトのサイトが開きます。


STEP5
「コードをオンラインで編集」をクリックします。
「index.html」を選択すると編集画面が表示されます。


STEP6
右上の「Open Preview」ボタンをクリックするとプレビュー画面が確認できます。



helloというラベルを追加してみたコード

index.html
<!DOCTYPE html>

<html lang="en">
    <head>
        <meta charset="utf-8" />
        <title></title>
    </head>
    <body>
        HTML5 Empty Site<br>
        <label>hello</label>
    </body>
</html>


プレビュー


参考

App Service のドキュメント
https://docs.microsoft.com/ja-jp/azure/app-service/

Monday, March 2, 2020

JIRA APIでステータスカテゴリの最終変更日時を取得したい


JIRAには課題の状態を表す値が2つあります
  1. ステータ
  2. ステータスカテゴリ

今回対象にするのはステータスカテゴリです
  • To Do
  • In Progress
  • Done

現在のステータスカテゴリになった日時を
fields内のstatuscategorychangedateで取得するコードを書きました。


各自で設定する変数
  • ISSUE_URL
  • issueKey
  • スクリプトのプロパティにtokenを入れておく

コード.gs
var ISSUE_URL = 'https://SITENAME.atlassian.net/rest/api/2/issue/';

function myFunction(){
  var issueKey = "KEY-1";
  var response = getIssueData(issueKey);
  var jobj = JSON.parse(response);
  var fields = jobj["fields"];
  var statusCategoryChangeDate = formatDate(fields["statuscategorychangedate"]);
  var status = fields["status"]["name"];
  Logger.log([status, statusCategoryChangeDate]);
}

function getIssueData(issueKey) {
  var token = getProp("token");
  var options = {
    contentType: "application/json",
    headers: {"Authorization": " Basic " + token}
  };
  var url = ISSUE_URL + issueKey';
  var response = UrlFetchApp.fetch(url, options);
  return response;
}

function formatDate(datetime) {
  var date = new Date(datetime);
  var formated = Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd HH:mm:ss');
  return formated;
}


myFunction()の実行結果の例

以下のようなログが出力されるはずです。

 [Done, 2020/03/02 18:15:43]


ステータス変更の履歴を取得したい場合は

JIRAで複数の課題の変更履歴をシートに書き出したい(ステータス変更だけ)

にコードを書きました。

関連記事

JIRAでAPIトークンを取得したい
JIRA APIでissueの変更履歴を取得する
JIRAのJQLにはstatusとstatusCategoryがある

Sunday, March 1, 2020

スプレッドシートで取得する行数を決めて複数回実行する処理を考えてみる


A列に2020/01/01〜2020/01/19までの日付が入ったシートがあって
2行目からスタートして5行ずつA列の値を取得してみる。

ポイント

  • 最後は5行ではなく4行残るので4行を取得する
  • 10回繰り返すが実際は4回で終わるため、5回目以降でエラーが出ないようにする


20行くらいの処理ならこんなことをしなくても良いのだけれど
何千、何万行のデータを処理する時に実行回数を分けたいケースにぶつかって
軽い処理で書いてみたのがこのコードです。



コード.gs
function iterateFunction() {
  var numRows = 5;
  var startRow = 2;
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  
  for(var i = 0; i < 10; i++) {
    myFunction(sheet, startRow, numRows, lastRow);
    startRow = startRow + numRows;
    if(startRow > lastRow) {
      return;// startRowがlastRowより大きくなったらfor文から抜ける
    }
  }
}

function myFunction(sheet, startRow, numRows, lastRow) {
  if(startRow + numRows > lastRow) {// 取得しようとする最終行がデータの行数より大きくなるなら
    numRows = lastRow - startRow + 1;// numRowsを残りの行数で上書きする
  }
  var range = sheet.getRange(startRow, 1, numRows, 1); 
  var values = range.getDisplayValues();
  var dates = values.map(function(array) { return array[0] });
  Logger.log(dates);
}




実行結果




Latest post

Extracting data from Google Sheets with regular expressions

Introduction Regular expressions are a powerful tool that can be used to extract data from text.  In Google Sheets, regular expressions ca...