Google Apps Script試行錯誤 Blog: 11月 2019

2019年11月30日土曜日

テキストエリアのheightを入力文字数によって自動調整したい

テキストエリア内で折り返しされた時にheightを自動で変更したくて書きました。

デモ

コード.gs

function doGet() {
  return HtmlService.createHtmlOutputFromFile("index");
}

意訳

この機能がやること
指定したHTMLファイルを表示する

index.html

<!DOCTYPE html>
<html>
  <body>
  <textarea id="ta"></textarea>
    <script>
      var ta = document.getElementById("ta");
      ta.oninput = taInput;
      
      function taInput(e){
        ta.style.height = "1em";
        if(ta.scrollHeight > ta.offsetHeight){   
          ta.style.height = ta.scrollHeight + "px";
        }
      }
   </script>
  </body>
</html>

参考

element.clientHeight
https://developer.mozilla.org/ja/docs/Web/API/Element/clientHeight

Element.scrollHeight
https://developer.mozilla.org/en-US/docs/Web/API/Element/scrollHeight

HTMLElement.offsetHeight
https://developer.mozilla.org/ja/docs/Web/API/HTMLElement/offsetHeight

2019年11月28日木曜日

zendesk APIでチケットのコメントを取得したい

これで取得できた

https://SITENAME.zendesk.com/api/v2/tickets/TICKET_ID/comments.json

（SITENAME と TICKET_ID は各自の対象の値を入れる）

参考

List Comments
https://developer.zendesk.com/rest_api/docs/support/ticket_comments#list-comments

2019年11月21日木曜日

複数行のセルの値を一つのセルに入れたい(スクリプト)

これを

こうしたい

A1　あいうえお

A2　かきくけこ

A3　さしすせそ

A4　空白行

A5　たちつてと

A6　なにぬねの

A7　空白行

A8　はひふへほ

A9　まみむめも

A10　空白行

A11　やゆよ

A12　らりるれろ

A13　空白行

A14　わをん

A1　あいうえお\nかきくけこ\nさしすせそ
A2　たちつてと\nなにぬねの
A3　はひふへほ\nまみむめも
A4　やゆよ\nらりるれろ
A5　わをん
　　　

きっともっとシンプルな方法があるはずですが、思い浮かんだ方法で書きました。

コード.gs

function mergeRows(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var activeRange = sheet.getActiveRange();
  var activeRow = activeRange.getRow();
  var activeCol = activeRange.getColumn();
  var lastRow = sheet.getLastRow();
  var targetRange = sheet.getRange(activeRow, activeCol, lastRow-activeRow+1, 1);
  var targetValues = targetRange.getValues();
  var sectionValues = '';
  var resultArrays = [];
  for(var i = 0; i < targetValues.length; i++) {
    if(targetValues[i][0] === "") {
      sectionValues += "\n"
    } else {
    sectionValues += targetValues[i][0] + "\n"
    }
  }
  targetRange.clear();
  var splitBreaks = sectionValues.split("\n\n");
  for(var i = 0; i < splitBreaks.length; i++) {
    resultArrays.push([splitBreaks[i]])
  }
  setValues(resultArrays)
}

function setValues(arrays){
  var sheet = SpreadsheetApp.getActiveSheet();
  var last_row = sheet.getLastRow();
  var start_row = last_row + 1;
  var start_col = 1;
  var num_rows = arrays.length;
  var num_cols = 1
  var range = sheet.getRange(start_row, start_col, num_rows, num_cols);
  range.setValues(arrays); 
}

スプレッドシートの空白行を削除したい（アクティブセル以下の行を対象）

A列に空白の行があって

その空白の行を削除したい

フィルタを作成して削除しても良いけれど、フィルタの場合はB列やC列を値で埋めておかないと作成できない。

マクロを記録してやろうとしたら、空白のセルではなく空白じゃないセルが決め打ちで選択されて、汎用的に使えなそうだったため、以下のようなコードを書きました。

A列でアクティブセル以下の行で空白の行を削除します。

コード.gs

function deleteBlankRows(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getActiveRange().getRow();
  var lastRow = sheet.getLastRow();
  for(var i = row; i < lastRow; i++){
    var range = sheet.getRange('A' +  i);
    var value = range.getDisplayValue();
    if(value == ""){
      sheet.deleteRows(i, 1);// i行目から1行削除
      lastRow = lastRow - 1
    }
  }
}

意訳

この機能がやること
今開いているシートを取得する
選択している範囲の行数を取得する
シートでデータが入っている最終行を取得する
選択行以下で空白の行を削除していく
A列のi行目の範囲を取得する
その範囲の値を取得する
その値が空なら
i行目の1行を削除する
lastRowから削除した一行分減らしていく

関連記事

特定の列に特定の値が入っている行を削除する

2019年11月20日水曜日

テキストエリアが空になったらアラートを出したい

デモ

コード.gs

function doGet() {
  return HtmlService.createHtmlOutputFromFile("index");
}

意訳

この機能がやること
指定したHTMLファイルを表示する

index.html

<!DOCTYPE html>
<html>
  <body>
    <textarea id="ta">テキスト</textarea>
    <script>      
      document.getElementById('ta').onkeyup = taKeyup;

      function taKeyup() {
        console.log("changed!");
        if(document.getElementById('ta').value === "") {
          alert("blank");
        }
      }
      
    </script>
  </body>
</html>

2019年11月19日火曜日

zendeskのuserIdからユーザ情報を取得したい

これで取得できました

https://SITENAME.zendesk.com/api/v2/users/USER_ID.json

SITENAME と USER_ID を各自のデータにしてアクセスすると取得できます。

response

{
  "user": {
    "id": USER_ID,
    "url": "https://SITENAME.zendesk.com/api/v2/users/USER_ID.json",
    "name": "NAME",
    "email": "NAME@gmail.com",
    "created_at": "2019-11-12T00:03:17Z",
    "updated_at": "2019-11-19T01:06:19Z",
    "time_zone": "Osaka",
    "iana_time_zone": "Asia/Tokyo",
    "phone": null,
    "shared_phone_number": null,
    "photo": null,
    "locale_id": ID,
    "locale": "ja",
    "organization_id": ID,
    "role": "admin",
    "verified": true,
    "external_id": null,
    "tags": [],
    "alias": null,
    "active": true,
    "shared": false,
    "shared_agent": false,
    "last_login_at": "2019-11-19T01:03:33Z",
    "two_factor_auth_enabled": null,
    "signature": null,
    "details": null,
    "notes": null,
    "role_type": null,
    "custom_role_id": null,
    "moderator": true,
    "ticket_restriction": null,
    "only_private_comments": false,
    "restricted_agent": false,
    "suspended": false,
    "chat_only": false,
    "default_group_id": ID,
    "report_csv": true,
    "user_fields": {}
  }
}

参考

https://developer.zendesk.com/rest_api/docs/support/users

2019年11月18日月曜日

2019年11月17日日曜日

スプレッドシートのurlとnameをHTMLServiceでセレクトボックスのオプションに保存したい（機能追加）

スプレッドシートのurlとnameをHTMLServiceでセレクトボックスのオプションに保存したい
で作った仕組みの機能追加です。

追加する機能

saveしたスプレッドシートをselectのoptionから自動で選択する
removeボタンを追加して、選択されているスプレッドシートを選択肢から削除する

スクリプトのプロパティからも削除する

コード.gs

function doGet() {
  return HtmlService.createHtmlOutputFromFile("index");
}

function saveSpreadsheetURL(new_ss_url) {
  var ss = SpreadsheetApp.openByUrl(new_ss_url);
  var ss_name = ss.getName();
  var ss_urls = getProp("ss_urls");
  if(ss_urls === null) {
    var obj = {}// nullならオブジェクトを作る（初回のみ）
  } else {
    var obj = JSON.parse(ss_urls);
  }
  obj[new_ss_url] = ss_name;
  var json = JSON.stringify(obj);
  setProp("ss_urls", json);
  setProp("current_ss_url", new_ss_url);
  var keys = Object.keys(obj);
  setProp("current_ss_index", keys.length-1);
  return json;
}

function returnCurrentSSObj() {
  var obj = {}
  obj["url"] = getProp("current_ss_url");
  obj["index"] = getProp("current_ss_index");
  return obj;
}

function updateRemoveSSJson(json) {
  var obj = JSON.parse(json);
  var keys = Object.keys(obj);
  Logger.log(keys)
  setProp("ss_urls", json);
  setProp("current_ss_url", keys[0]);
  setProp("current_ss_index", 0);
  return json;
}

function getProp(key) {
  return PropertiesService.getScriptProperties().getProperty(key);
}

function setProp(key, value) {
  PropertiesService.getScriptProperties().setProperty(key, value);
}

index.html

<!DOCTYPE html>
<html>
  <body>
    <select id="ss_select">
    </select>
    
    <input type="text" id="new_ss_url">
    <button id="save">save</button>
    <a id="open_sheet" target="_blank">open sheet</a>
    <button id="remove_ss">remove</button>
    <textarea id="ss_json_ta"></textarea>
    
    <script>
      ssUrlsLoad();
    
      function ssUrlsLoad() {
        google.script.run
          .withSuccessHandler(ssUrlsLoaded)
          .getProp("ss_urls");
      }
      
      elem("save").onclick = saveClicked;
      elem("ss_select").onchange = selectChanged;
      elem("remove_ss").onclick = removeClicked;
    
      function elem(id) {
        return document.getElementById(id);
      }
      
      function ssUrlsLoaded(ssUrlsJSON) {
        if(ssUrlsJSON) {
          elem("ss_json_ta").value = ssUrlsJSON;
          createSSList(ssUrlsJSON);
        }
      }

      function currentSSObjLoaded(obj) {
        if(obj) {
          elem("ss_select").selectedIndex = obj["index"];
          elem("new_ss_url").value = obj["url"];
          elem("open_sheet").href = obj["url"];
        }
      }

      function saveClicked() {
        var new_ss_url = elem("new_ss_url").value.split("#gid=")[0]// + "#gid=0";
        google.script.run
          .withFailureHandler(onFailure)
          .withSuccessHandler(ssSaved)
          .saveSpreadsheetURL(new_ss_url);
      }
      
      function ssSaved(ssListJSON) {
        console.log(ssListJSON);
        createSSList(ssListJSON);
      }
      
      function createSSList(ssListJSON) {
        var parentId = "ss_select";
        removeChilds(parentId);
        var obj = JSON.parse(ssListJSON);
        var keys = Object.keys(obj);
        var ss_select = elem(parentId);
        for(var i = 0; i < keys.length; i++) {
          var option = document.createElement("option");
          option.id = keys[i];
          option.name = obj[keys[i]];
          option.innerHTML = obj[keys[i]];
          ss_select.appendChild(option);
        }
        google.script.run.withSuccessHandler(currentSSObjLoaded).returnCurrentSSObj();
        elem("ss_json_ta").value = ssListJSON;
      }

      function selectChanged() {
        var select = elem("ss_select");
        var index = select.selectedIndex;
        var ssUrl = select[index].id;
        elem("new_ss_url").value = ssUrl;
        elem("open_sheet").href = ssUrl;
        var index = select.selectedIndex;
        sendCurrentSS(index, ssUrl);
      }
      
      function sendCurrentSS(index, ssUrl) {
        google.script.run.setProp("current_ss_index", index);
        google.script.run.setProp("current_ss_url", ssUrl);
      }

      function removeClicked() {
        var ss_json = elem("ss_json_ta").value;
        var obj = JSON.parse(ss_json);
        var current_ss_url = elem("new_ss_url").value;
        delete obj[current_ss_url];
        ss_json = JSON.stringify(obj)
        elem("ss_json_ta").value = ss_json;
        google.script.run
          .withFailureHandler(onFailure)
          .withSuccessHandler(ssJsonUpdated)
          .updateRemoveSSJson(ss_json);
      }
      
      function ssJsonUpdated(ssListJSON) {
        var current_ss_url = elem("new_ss_url").value;
        createSSList(ssListJSON);
        
      }

      function removeChilds(id) {
        var target = elem(id);
        for (var i = target.childNodes.length - 1; i >= 0; i--) {
          target.removeChild(target.childNodes[i]);
        }
      }
      
      function onFailure(e) {
        alert([e.message, e.stack]);
      }
    </script>
  </body>
</html>

スプレッドシートのurlとnameをHTMLServiceでセレクトボックスのオプションに保存したい

やりたいこと

HTMLService側で、スプレッドシートのurlとnameをselect optionに保存したい

こういう挙動を実現したい

テキストボックスにスプレッドシートのURLを入力する
saveボタンをクリックする
.gs側でスプレッドシートのnameを取得する
urlとnameを{url: name}の形でスクリプトのプロパティに保存する
HTMLにJSONで返してselectのoptionのidにurlを、innerHTMLにnameを入れる
open sheetのhrefにurlを入れる
別のスプレッドシートのURLを入れてsaveボタンをクリックする
selectのoptionが増えてopen sheetのhrefも変わる
optionを変更するとopen sheetのhrefも変わる

イメージ

コード.gs

function doGet() {
  return HtmlService.createTemplateFromFile('index').evaluate().setTitle("save ID and NAME ");
}

function saveSpreadsheetURL(new_ss_url) {
  var ss = SpreadsheetApp.openByUrl(new_ss_url);
  var ss_name = ss.getName();
  var ss_urls = getProp("ss_urls");
  if(ss_urls === null) {
    var obj = {}// nullならオブジェクトを作る（初回のみ）
  } else {
    var obj = JSON.parse(ss_urls);
  }
  obj[new_ss_url] = ss_name;
  var json = JSON.stringify(obj);
  setProp("ss_urls", json);
  return json;
}

function getProp(key) {
  return PropertiesService.getScriptProperties().getProperty(key);
}

function setProp(key, value) {
  PropertiesService.getScriptProperties().setProperty(key, value);
}

index.html

<!DOCTYPE html>
<html>
  <body>
    <select id="ss_select">
    </select>
    
    <input type="text" id="new_ss_url">
    <button id="save">save</button>
    <a id="open_sheet" target="_blank" href="">open sheet</a>
    
    <script>
      google.script.run.withSuccessHandler(ssLoaded).getProp("ss_urls");
    
      elem("save").onclick = saveClicked;
      elem("ss_select").onchange = selectChanged;
    
      function elem(id) {
        return document.getElementById(id);
      }
      
      function ssLoaded(ssListJSON) {
        if(ssListJSON) {
          createSSList(ssListJSON);
          selectChanged();
        }
      }
      
      function saveClicked() {
        var new_ss_url = elem("new_ss_url").value.split("#gid=")[0]// + "#gid=0";
        google.script.run
          .withFailureHandler(onFailure)
          .withSuccessHandler(ssSaved)
          .saveSpreadsheetURL(new_ss_url);
      }
      
      function ssSaved(ssListJSON) {
        createSSList(ssListJSON);
      }
      
      function createSSList(ssListJSON) {
        var parentId = "ss_select";
        removeChilds(parentId);
        var obj = JSON.parse(ssListJSON);
        var keys = Object.keys(obj);
        var ss_select = elem(parentId);
        for(var i = 0; i < keys.length; i++) {
          var option = document.createElement("option");
          option.id = keys[i];
          option.name = obj[keys[i]];
          option.innerHTML = obj[keys[i]];
          ss_select.appendChild(option);
        }
      }

      function selectChanged() {
        var select = elem("ss_select");
        var index = select.selectedIndex;
        var ssUrl = select[index].id;
        elem("new_ss_url").value = ssUrl;
        elem("open_sheet").href = ssUrl;
      }

      function removeChilds(id) {
        var target = elem(id);
        for (var i = target.childNodes.length - 1; i >= 0; i--) {
          target.removeChild(target.childNodes[i]);
        }
      }
      
      function onFailure(e) {
        alert([e.message, e.stack]);
      }
    </script>
  </body>
</html>

2019年11月16日土曜日

スプレッドシートからシートのidとnameをオブジェクトにして取得したい

スプレッドシートに２つのシート（シート1, シート2）があって

こういうオブジェクトでidとnameを取得したい
{
sheets=[
{
name=シート1,
id=0
},
{
name=シート2,
id=1108729736
}
],
name=無題のスプレッドシート,
id=1MbkVY162q27yF0h4aNnxxxxxxxxxxxxxx
}

コード.gs

function getSSObject(){
  var ssUrl = "https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit#gid=0";// 対象のスプレッドシートのURL
  var ss = SpreadsheetApp.openByUrl(ssUrl);
  
  var ssObj = {};
  ssObj["id"] = ss.getId();
  ssObj["name"] = ss.getName();
  ssObj["sheets"] = getSheetsObjArray(ss);
  Logger.log(ssObj);
}

function getSheetsObjArray(ss) {
  var sheets = ss.getSheets();
  var sheetsObjArray = [];
  for(var i = 0; i < sheets.length; i++) {
    var obj = {}
    var sheet = sheets[i];
    obj["name"] = sheet.getSheetName();
    obj["id"] = sheet.getSheetId();
    sheetsObjArray.push(obj);
  }
  return sheetsObjArray;
}

参考

openByUrl(url)
https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#openByUrl(String)

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

正規表現を使って、漢字+ひらがな1文字で区切りたい

デモ文章
正規表現を使って、漢字+ひらがな1文字で区切ってみたくなった。

上記文章を以下のように区切りたくて

正規表現を,使っ,漢字,文字で,区切っ

作った正規表現がこちら

/[々〆〇〻㐂-頻]+[ぁ-ゟー]|[々〆〇〻㐂-頻]+/g

2019年11月13日水曜日

GASからLambda経由でzendesk APIを実行してシートにチケット情報を書き出したい

GASからLambdaにzendesk_idを渡してzendesk APIのGETで情報取得してシートに書き出したい
で書いたコードの応用です。

zendeskのAPIで一度に取得できるチケット情報は100件ずつのようです。

そのため、101件以上ある場合は100で割った値の小数点以下を繰り上げて、
その回数分繰り返すようにしています。

コード.gs

var host = 'SITENAME.zendesk.com';
var endPoint = 'https://API_ID.execute-api.us-east-1.amazonaws.com/default/get_zendesk_ticket';
var ss_url = 'https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit#gid=0';

//これを実行する
function getZendeskInfo() {
  var response = run(1);
  var objs = JSON.parse(response)["results"];
  var count = JSON.parse(response)["count"];

  objsToSheet(objs);
  runLoop(count);
}

//101件以上ある場合はループで全部とりに行く
function runLoop(count) {
  if(count > 101) {
    var looptime = Math.ceil(count / 100);
    for(var i = 1; i < looptime; i++) {
      var response = run(i + 1);
      var objs = JSON.parse(response)["results"];
      objsToSheet(objs);
    }
  }
}

//受け取ったpageNumのデータを取得して返す
function run(pageNum) {
  var path = '/api/v2/search.json?page=' + pageNum + '&query=created%3E2019-01-01%20type:ticket';
  var payload = {
  'data':{
    'host': host,
    'path': path,
    'zendesk_api_token_base64': getProp('zendesk_api_token_base64')
   }
  }
  var options = {
    'method': 'post',
    'headers': get_headers(),
    'contentType': 'application/json',
    'payload': JSON.stringify(payload)
  }
  var response = UrlFetchApp.fetch(endPoint, options);
  Logger.log(JSON.parse(response)["results"].length)
  return response;
}
  
//API GatewayのAPI KEY
function get_headers() {
  var headers = {
    "x-api-key": getProp('x_api_key')
  }
  return headers;
}

//スクリプトのプロパティから値を取得する
function getProp(key) {
  return PropertiesService.getScriptProperties().getProperty(key);
}

//書き込むシート
function targetSheet() {
  var ss = SpreadsheetApp.openByUrl(ss_url);
  var sheet = ss.getSheets()[0];
  return sheet;
}

//シートに書き込むデータを渡す
function objsToSheet(objs) {
  var sheet = targetSheet();
  var obj = objs[0];
  var keys = Object.keys(obj);// ヘッダーの値
  setSheetHeader(sheet, [keys]);// 一行目に見出しを入れる
  
  var arrays = [];
  for(var i = 0; i < objs.length; i++) {
    var obj = objs[i];
    var values = [];
    for(var j = 0; j < keys.length; j++) {
      var value = obj[keys[j]];
      values.push(value);
    }
    arrays.push(values);
  }
  setDataToSheet(sheet, arrays)
}

//シートにデータを書き込む
function setDataToSheet(sheet, arrays){
  var last_row = sheet.getLastRow();
  var start_row = last_row + 1;
  var start_col = 1;
  var num_rows = arrays.length;
  var num_cols = arrays[0].length;
  var range = sheet.getRange(start_row, start_col, num_rows, num_cols);
  range.setValues(arrays); 
}

//シートの一行目に見出しを書き込む
function setSheetHeader(sheet, arrays) {
  var start_row = 1;
  var start_col = 1;
  var num_rows = 1;
  var num_cols = arrays[0].length;
  var range = sheet.getRange(start_row, start_col, num_rows, num_cols);
  range.setValues(arrays); 
}

//base64tokenを作成する　ここで実行したログの値をスクリプトのプロパティに入れておく
function get_base64() {
  var zendesk_api_token = "ZENDESK_API_TOKEN";
  var base64Token = Utilities.base64Encode("EMAIL" + '/token:' + zendesk_api_token);
  Logger.log(base64Token);
}

Lambda側のindex.jsは
GASからLambdaにzendesk_idを渡してzendesk APIのGETで情報取得してシートに書き出したい
に書かれているものと同じです。

JIRA APIで課題作成時にサブタスクも作成したい

JIRAのAPIでストーリーを作成して、サブタスクも一緒に作るというコードの例です。

/rest/api/2/ と /rest/api/3でdescriptionの書き方が異なるところにちょっとハマりました。

/rest/api/2/ のdescriptionは一行で書けましたが、
/rest/api/3 は複数のプロパティを設定する必要があるようです。

コード.gs
var BASE_URL = 'https://SITENAME.atlassian.net'; //var API_VERSION = "/rest/api/2/";// version 2で実行する場合 var API_VERSION = "/rest/api/3/";// version 3で実行する場合 var ISSUE_URL = BASE_URL + API_VERSION + '/issue/'; var projectKey = 'KT'; // JIRAプロジェクトのKEY //ストーリーを作成する function createStory() { var payload = getPayloadStory(); var options = getOptions(payload); var response = UrlFetchApp.fetch(ISSUE_URL, options); var key = getKey(response); var id = getKey(response); createSubtask(key, "タスク1", "サプタスク1\n", "https://www.google.co.jp/");// サブタスクのタイトルと説明 createSubtask(key, "タスク2", "サプタスク2\n", "https://www.google.co.jp/"); } //ストーリーの内容を作成する function getPayloadStory() { var email = Session.getActiveUser().getEmail(); var data = { "project": {"key": projectKey}, "issuetype": {"name": "Story"}, "summary": "ストーリー12", //"description": "https://beautifier.io/",// /rest/api/2/で実行する場合のdescription "description": { "version": 1, "type": "doc", "content": [ { "type": "paragraph", "content": [ { "type": "text", "text": "https://www.google.com/", "marks": [ { "type": "link", "attrs": { "href": "https://www.google.com/", "title": "Google" } } ] } ] } ] }, "reporter": {"id": getAccountId()} }; var fields = {"fields": data}; var payload = JSON.stringify(fields); return payload; } //ストーリーのissueKeyをparentKeyとして受け取ってサブタスクを作成する function createSubtask(parentKey, summary, description, link) { var payload = getPayloadTask(parentKey, summary, description, link); var options = getOptions(payload); var response = UrlFetchApp.fetch(ISSUE_URL, options); var key = getKey(response); } //サブタスクの内容を作成する function getPayloadTask(parentKey, summary, description, link) { var email = Session.getActiveUser().getEmail(); var data = { "parent": {"key": parentKey}, "project": {"key": projectKey}, "issuetype": {"name": "Sub-task"}, "summary": summary, //"description": description,// /rest/api/2/で実行する場合のdescription "description": { "version": 1, "type": "doc", "content": [ { "type": "paragraph", "content": [ { "type": "text", "text": description }, { "type": "text", "text": link, "marks": [ { "type": "link", "attrs": { "href": link, "title": "Google" } } ] } ] } ] }, "reporter": {"id": getAccountId()} }; var fields = {"fields": data}; var payload = JSON.stringify(fields); return payload; } //スクリプトのプロパティから指定したkeyの値を取得する function getProp(key) { return PropertiesService.getScriptProperties().getProperty(key); } //optionsを作成する function getOptions(payload) { var options = { method: "post", payload: payload, contentType: "application/json", headers: {"Authorization": " Basic " + getProp('token')} } return options; } //responseからissueKeyを取得する function getKey(response){ var jobj = JSON.parse(response); var key = jobj["key"]; return key; } //emailからaccountIdを取得する function getAccountId() { var email = Session.getActiveUser().getEmail(); var url = BASE_URL + API_VERSION + 'user/search?query=' + email; var options = { method: "get", contentType: "application/json", headers: {"Authorization": " Basic " + getProp('token')} } var response = UrlFetchApp.fetch(url, options); var jobj = JSON.parse(response); var accountInfo = getAccountInfo(jobj, email); var accountId = accountInfo['accountId']; return accountId; } //accountIdの情報を取得する function getAccountInfo(jobj, email) { var accountInfo; for(var i = 0; i < jobj.length; i++) { var emailAddress = jobj[i]['emailAddress']; if(emailAddress === email) { accountInfo = jobj[i]; } } return accountInfo; }

関連記事

JIRA APIでissueを作成する

参考

Creating Sub task of an issue using REST API.
https://community.atlassian.com/t5/Answers-Developer-Questions/Creating-Sub-task-of-an-issue-using-REST-API/qaq-p/500838

Mark - link
https://developer.atlassian.com/cloud/jira/platform/apis/document/marks/link/

2019年11月12日火曜日

2次元配列を1次元配列にしたい（mapでやってみる）

Array.prototype.push.applyで配列を結合する（2次元配列を1次元配列にしたい）

でやったことをmapでやってみます。

var arrays = [[123], [456], [789]]

を

こうしたい

[123 456, 789]

コード.gs

function getArray0FromArrays() {
  var arrays = [[123], [456], [789]];
  var values = arrays.map(function(array) { return array[0] });
  Logger.log(values);
}

実行結果

一次元配列になってログに出力されます。
[123.0, 456.0, 789.0]

GASからLambdaにzendesk_idを渡してzendesk APIのGETで情報取得してシートに書き出したい

GASからLambdaにzendesk_idを渡してzendesk APIのGETで情報取得したい
で取得したデータをスプレッドシートに書き出したくて書いたコードです。

コード.gs

var host = 'SITENAME.zendesk.com';
var endPoint = 'https://API_ID.execute-api.us-east-1.amazonaws.com/default/get_zendesk_ticket';

//これを実行する
function getZendeskInfo() {
  var zendesk_id = 2;// zendesk_idを指定する場合
  var response = run(zendesk_id);
  var objs = JSON.parse(response)["results"];
  objsToSheet(objs);
}

function run(zendesk_id) {
  //var path = '/api/v2/tickets/' + zendesk_id + '.json';// zendesk_idを指定したい場合
  var path = '/api/v2/search.json?page=1&query=created%3E2019-01-01%20type:ticket';// queryで取得したい場合
  var payload = {
  'data':{
    'host': host,
    'path': path,
    'zendesk_id': zendesk_id,
    'zendesk_api_token_base64': getProp('zendesk_api_token_base64')
   }
  }
  var options = {
    'method': 'post',
    'headers': get_headers(),
    'contentType': 'application/json',
    'payload': JSON.stringify(payload)
  }
  var response = UrlFetchApp.fetch(endPoint, options);
  return response;
}
  
function get_headers() {
  var headers = {
    "x-api-key": getProp('x_api_key')
  }
  return headers;
}

function getProp(key) {
  return PropertiesService.getScriptProperties().getProperty(key);
}

//書き込むシート
function targetSheet() {
  var ss_url = 'https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit#gid=0';
  var ss = SpreadsheetApp.openByUrl(ss_url);
  var sheet = ss.getSheets()[0];
  return sheet;
}

//シートに書き込むデータを渡す
function objsToSheet(objs) {
  var sheet = targetSheet();
  var obj = objs[0];
  var keys = Object.keys(obj);
  var arrays = [keys];// keysを一行目に入れておく
  for(var i = 0; i < objs.length; i++) {
    var obj = objs[i];
    var values = [];
    for(var j = 0; j < keys.length; j++) {
      var value = obj[keys[j]];
      values.push(value);
    }
    arrays.push(values);
  }
  setDataToSheet(sheet, arrays)
}

//シートにデータを書き込む
function setDataToSheet(sheet, arrays){
  var last_row = sheet.getLastRow();
  var start_row = last_row + 1;
  var start_col = 1;
  var num_rows = arrays.length;
  var num_cols = arrays[0].length;
  var range = sheet.getRange(start_row, start_col, num_rows, num_cols);
  range.setValues(arrays); 
}

//base64tokenを作成する　これをスクリプトのプロパティ
function get_base64() {
  var zendesk_api_token = "ZENDESK_API_TOKEN";
  var base64Token = Utilities.base64Encode("EMAIL" + '/token:' + zendesk_api_token);
  Logger.log(base64Token);
}

Lambda側のコード（関連記事のコードと同じ）

index.js

var https = require('https');

var zendesk_id;
var zendesk_api_token_base64;
var host;
var path;
exports.handler = async function(event) {
 console.log(event);
 
 /******
 pyloadで受け取る場合
 ******/
 var body = event['body'];
 var jobj = JSON.parse(body);
 host = jobj['data']['host']
 path = jobj['data']['path']
 zendesk_id = jobj['data']['zendesk_id']
 zendesk_api_token_base64 = jobj['data']['zendesk_api_token_base64']
 console.log([zendesk_id]);
 
 var results = await getData(zendesk_id); //getData()の処理が終わってから次の処理を実行する
 var json = returnJson(results);
 return json;
}

//Promise
function getData() {
 return new Promise(resolveFunc);
}

//Promise resolved
function resolveFunc(arg) {
 https.get(getOptions(), function(res) {
    var body = '';
    //res.setEncoding('utf8');
    res.on('data', function(chunk) {
     body += chunk;
    });
    res.on('end', function(chunk) {
     arg(body);
    });
    res.on('error', function(e) {
     console.log(e.message);
     arg(e.message);
    });
 });
 //ここまでの処理をPromiseでやりたい
}

function getOptions() {
 var options = {
    "method": 'GET',
    "host": host,
    "path": path,
    "headers": {
     "Content-type": "application/json",
     "Authorization": " Basic " + zendesk_api_token_base64// Lambda側に設定して読む場合はprocess.env. zendesk_api_token_base64
    }
 };
 return options;
}

//JSONを返す
function returnJson(results) {
 const response = {
    //body: JSON.stringify(result),
    body: results,
 };
 return response;
}

2019年11月10日日曜日

UserAgentを取得してブラウザや端末情報を取得してみる

window.navigator.userAgent
でユーザーエージェントが取得できる

ユーザーエージェントは例えば以下のような文字列で取得できる

Macで開いた場合
Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.87 Safari/537.36

Android（Pixel 3a）で開いた場合
Mozilla/5.0 (Linux; Android 10; Pixel 3a) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/76.0.3809.132 Mobile Safari/537.36
OK

開くブラウザや端末によって内容は異なり、OS、端末の種類、ブラウザ、Mobileかどうかなどの情報が書かれている。

コード.gs

function doGet() {
  return HtmlService.createHtmlOutputFromFile("index");
}

意訳

この機能がやること
指定したHTMLファイルを表示する

index.html

<!DOCTYPE html>
<html>
  <body>
    <script>
      getUserAgent();
      function getUserAgent() {
        var userAgent = window.navigator.userAgent;
        alert(userAgent);
      }
    </script>
  </body>
</html>

参考

window.navigator.userAgent
https://developer.mozilla.org/ja/docs/Web/API/NavigatorID/userAgent

Android（Pixel3a）でUSBデバッグをしたい（DevToolsでconsoleを見たい）

Webアプリ開発中に、スマホでもconsoleを見たくて

事前準備

Android端末（Pixel3a）で開発者オプションを表示したい
で開発者オプションを表示できるようにしておきます

開発者オプションを開いて「USBデバッグ」をタップしてオンにします

許可を求められるので「OK」をタップします

PCのChromeのアドレスバーに

chrome://inspect/#devices

を入力して開きます

するとこのような画面が開くので
検証したいページの「inspect」をクリックします

DevToolsが表示されて

左側には端末側の画面が表示されます

右側には検証ツールが表示されます

関連記事

Android端末（Pixel3a）で開発者オプションを表示したい

参考

デバイスの開発者向けオプションを設定する
https://developer.android.com/studio/debug/dev-options?hl=ja

Google Chrome inspect devices?
https://stackoverflow.com/questions/45366421/google-chrome-inspect-devices

Android の Chrome で開発者ツールを使う方法
https://qiita.com/hojishi/items/12b726f8b02ef3d713e4

Android端末（Pixel3a）で開発者オプションを表示したい

USBデバッグをしたくて

「設定」アイコンをタップします

「デバイス情報」をタップします

「ビルド番号」を7回タップします

前の画面に戻って「システム」をタップします

「詳細設定」をタップします

「開発者向けオプション」が表示されます

参考

デバイスの開発者向けオプションを設定する
https://developer.android.com/studio/debug/dev-options?hl=ja

2019年11月9日土曜日

gitでbranchを作ってaddしてcommitしてGitHubにpushしたい

普段はSourceTreeを使っているので、gitのコマンドは忘れがち。

ここでやること

作業用ブランチを作って
リモートにプッシュして
マージして
masterをpullして
作業用ブランチを削除する

コマンド

ローカルリポジトリのフォルダ $ git branch add-list
ローカルリポジトリのフォルダ $ git checkout add-list
Switched to branch 'add-list'
ローカルリポジトリのフォルダ $ git branch
* add-list
  master


ローカルリポジトリのフォルダ $ git diff
ローカルリポジトリのフォルダ $ git status
ローカルリポジトリのフォルダ $ git add .
ローカルリポジトリのフォルダ $ git commit -m "メッセージ"
ローカルリポジトリのフォルダ $ git push origin add-list



ローカルリポジトリのフォルダ $ git checkout master
ローカルリポジトリのフォルダ $ git pull origin master
ローカルリポジトリのフォルダ $ git branch --delete add-list

意訳

add-listというブランチを作る
add-listというブランチに切り替える $ git checkout -b add-list で作成と切り替えができる
切り替わった
ブランチを確認する
現在のブランチはadd-list

ローカルのファイルやフォルダに変更を加える

変更点を確認する
現在の状態を確認する
変更を追加する
メッセージを付けてコミットする
リモートのadd-listにプッシュする

リモートでプルリクを作ってマージする

masterブランチに切り替える
リモートのmasterブランチをローカルに反映させる
add-listブランチを削除する

参考

https://backlog.com/ja/git-tutorial/stepup/01/

2019年11月7日木曜日

GASのsetNumberFormatで表示形式の数値を自動にしたい場合は.setNumberFormat("General")

公式リファレンスに書かれている場所を見つけられず、Stack Overflow で見つけました。

コード.gs

function set_format() {
 var format = SpreadsheetApp.getActiveSheet().getRange("A2").setNumberFormat("General");
 Logger.log(format);
}

参考

https://stackoverflow.com/questions/38042702/applying-automatic-number-formatting

2019年11月6日水曜日

Cloud Firestoreのルールを安全にしたい

Gmailの件名：
[Firebase] Cloud Firestore データベースに安全でないルールがあります

というメールが来ました。

メールの「詳細」をクリックすると英語版のページが開いて対処方法が書かれていました。

英語版
Fix insecure rules
https://firebase.google.com/docs/firestore/security/insecure-rules

日本語版
安全でないルールを修正する
https://firebase.google.com/docs/firestore/security/insecure-rules?hl=ja

僕は「詳細」ボタンを押さずにGmailの件名でググってしまいましたが、一番上に「安全でないルールを修正する」が出てきたので、たぶんこのブログに辿り着く人はいないと思いますが、備忘録として。

2019年11月4日月曜日

Google Visualization APIでシートのデータを取得する（ヘッダをキーにしたオブジェクトを作る）

Google Visualization APIでシートのデータを取得する
ではシートのデータを配列にして取得しましたが
今回はオブジェクトにして取得するコードを書きます。

例としてこのようなシートを用意して

そのシートのデータを
このようなオブジェクトにして取得したい。

[{
  date = 2019 / 11 / 01,
  task = task1,
  label = label1
}, {
  date = 2019 / 11 / 01,
  task = task2,
  label = label2
}, {
  date = 2019 / 11 / 01,
  task = task3,
  label = label1
}, {
  date = 2019 / 11 / 02,
  task = task4,
  label = label3
}, {
  date = 2019 / 11 / 02,
  task = task5,
  label = label2
}, {
  date = 2019 / 11 / 02,
  task = task6,
  label = label1
}]

デモデータ

date	task	label
2019/11/01	task1	label1
2019/11/01	task2	label2
2019/11/01	task3	label1
2019/11/02	task4	label3
2019/11/02	task5	label2
2019/11/02	task6	label1

コード.gs

var SS_ID = "スプレッドシートのID";
var SH_ID = 0;// シートのID
var BASE_URL = "https://docs.google.com/spreadsheets/d/";
var URL = BASE_URL + SS_ID + "/gviz/tq?gid=" + SH_ID + "&tqx=out:json&tq=";

SpreadsheetApp.openById(SS_ID);

function get_public_data() {
  var options = null;
  data_via_gviz(options);
}

function get_private_data() {
  var access_token = get_access_token();
  var headers = get_headers(access_token);
  var options = get_options(headers);
  data_via_gviz(options);
}

function get_access_token() {
  var access_token = ScriptApp.getOAuthToken();
  return access_token;
}

function get_headers(access_token) {
  var headers = {
    "Authorization": "Bearer " + access_token
  }
  return headers;
}

function get_options(headers) {
  var options = {
    "contentType": "application/json",
    "headers": headers,
    "muteHttpExceptions": true
  }
  return options;
}

function data_via_gviz(options) {
  var query = encodeURIComponent("SELECT A, B, C");
  var response = UrlFetchApp.fetch(URL + query, options);
  var jobj = get_jobj(response);
  var array = get_array(jobj);
  Logger.log(array);
}

function get_jobj(response) {
  var data = response.getContentText();
  data = data.split("google.visualization.Query.setResponse(")[1];
  data = data.substr(0, data.length - 2);
  var jobj = JSON.parse(data);
  return jobj;
}

function get_array(jobj) {
  var col_labels = get_col_labels(jobj);
  var row = jobj["table"]["rows"];
  var array = [];
  for (var i = 0; i < row.length; i++) {
    var obj = {};
    for (var j = 0; j < col_labels.length; j++) {
      var value = '';
      var icj = jobj["table"]["rows"][i]["c"][j];
      if(icj === null) {// nullならば
        value = '';
      } else {
        value =icj["v"];
        var formatted = icj["f"];
        if(formatted) {// formattedが存在すれば
          value = formatted;// valueに入れる
        }
        obj[col_labels[j]] = value;
      }
    }
      array.push(obj);
  }
  return array;
}

function get_col_labels(jobj) {
  var cols = jobj["table"]["cols"];
  var col_labels = [];
  for(var i = 0; i < cols.length; i++) {
    var label = cols[i]['label'];
    col_labels.push(label)
  }
  return col_labels
}

補足

jobj["table"]["cols"]のlabelに値が入っている場合と入っていない場合があった。

手元で確認したこと
セルの表示形式を確認しました。

「自動」の場合はlabelに一行目の値が入っていました
「書式なしテキスト」の場合はlabelは空になっていました

labelが空の場合はrowsの0番目に一行目の値が入っているので、以下のコード2.gsを書きました。

コード2.gs

var SS_ID = "スプレッドシートのID";

var SH_ID = 0;// シートのID
var BASE_URL = "https://docs.google.com/spreadsheets/d/";
var URL = BASE_URL + SS_ID + "/gviz/tq?gid=" + SH_ID + "&tqx=out:json&tq=";

SpreadsheetApp.openById(SS_ID);

function get_public_data() {
  var options = null;
  var result_array = data_via_gviz(options);
  Logger.log(result_array);
}

function get_private_data() {
  var access_token = get_access_token();
  var headers = get_headers(access_token);
  var options = get_options(headers);
  var result_array = data_via_gviz(options);
  Logger.log(result_array);
}

function get_access_token() {
  var access_token = ScriptApp.getOAuthToken();
  return access_token;
}

function get_headers(access_token) {
  var headers = {
    "Authorization": "Bearer " + access_token
  }
  return headers;
}

function get_options(headers) {
  var options = {
    "contentType": "application/json",
    "headers": headers,
    "muteHttpExceptions": true
  }
  return options;
}

function data_via_gviz(options) {
  var query = encodeURIComponent("SELECT A, B, C");
  var response = UrlFetchApp.fetch(URL + query, options);
  var jobj = get_jobj(response);
  var array = get_array(jobj);
  return array;
}

function get_jobj(response) {
  var data = response.getContentText();
  data = data.split("google.visualization.Query.setResponse(")[1];
  data = data.substr(0, data.length - 2);
  var jobj = JSON.parse(data);
  return jobj;
}

function get_array(jobj) {
  var row0_values = get_row1_values(jobj);
  var rows = jobj["table"]["rows"];// 0番目は
  rows.shift();// 0番目はrow0_valuesで取得しているのでここで除外しておく
  var array = [];
  for (var i = 0; i < rows.length; i++) {
    var obj = {};
    for (var j = 0; j < row0_values.length; j++) {
      var value = '';
      var icj = jobj["table"]["rows"][i]["c"][j];
      if(icj === null) {// iのcのjがnullならば
        value = '';
      } else {
        value =icj["v"];
        var formatted = icj["f"];
        if(formatted) {// formattedが存在すれば
          value = formatted;// valueに入れる
        }
        obj[row0_values[j]] = value;
      }
    }
      array.push(obj);
  }
  return array;
}

function get_row1_values(jobj) {
  var row1s = [];
  var row0 = jobj["table"]["rows"][0]["c"];
  for(var i = 0; i < row0.length; i++) {
    var label = row0[i]["v"];
    row1s.push(label)
  }
  return row1s
}

オブジェクトの中から条件に一致する要素を抜き出す(filter, 複数プロパティを対象)

オブジェクトの中から条件に一致する要素を抜き出す(filter)
ではオブジェクトの中の一つのプロパティを見て一致するものを取得しました。

今回は２つのプロパティを見て一致するものを取得してみます。

objのようなオブジェクトを用意して
enが「Libra」で
jaが「てんびん座」のidを取得してログに出してみます。

コード.gs

var obj = [
  {"id": 1, "en": "Aries", "ja": "おひつじ座"},
  {"id": 2, "en": "Taurus", "ja": "おうし座"},
  {"id": 3, "en": "Gemini", "ja": "ふたご座"},
  {"id": 4, "en": "Cancer", "ja": "かに座"},
  {"id": 5, "en": "Leo", "ja": "しし座"},
  {"id": 6, "en": "Virgo", "ja": "おとめ座"},
  {"id": 7, "en": "Libra", "ja": "てんびん座"},
  {"id": 8, "en": "Scorpius", "ja": "さそり座"},
  {"id": 9, "en": "Sagittarius", "ja": "いて座"},
  {"id": 10, "en": "Capriconus", "ja": "やぎ座"},
  {"id": 11, "en": "Aquarius", "ja": "みずがめ座"},
  {"id": 12, "en": "Pisces", "ja": "うお座"},
  {"id": 13, "en": "Aries", "ja": "牡羊座"},
  {"id": 14, "en": "Taurus", "ja": "牡牛座"},
  {"id": 15, "en": "Gemini", "ja": "双子座"},
  {"id": 16, "en": "Cancer", "ja": "蟹座"},
  {"id": 17, "en": "Leo", "ja": "獅子座"},
  {"id": 18, "en": "Virgo", "ja": "乙女座"},
  {"id": 19, "en": "Libra", "ja": "天秤座"},
  {"id": 20, "en": "Scorpius", "ja": "蠍座"},
  {"id": 21, "en": "Sagittarius", "ja": "射手座"},
  {"id": 22, "en": "Capriconus", "ja": "山羊座"},
  {"id": 23, "en": "Aquarius", "ja": "水瓶座"},
  {"id": 24, "en": "Pisces", "ja": "魚座"},
]

function filter_obj() {
  var filtered = obj.filter(judge);
  var result = get_result(filtered);
  Logger.log(result);
}

function judge(items) {
  if(items["en"] === "Libra" && items["ja"] === "てんびん座"){
    return true;
  }else{
    return false;
  }
}

function get_result(filtered) {
  var result = [];
  for (var i = 0; i < filtered.length; i++) {
    result.push(filtered[i]["id"]);
  }
  return result;
}

実行結果

filter_obj() を実行すると以下のようなログが出ます。

en が「Libra」で ja が「てんびん座」の id は「7」