LANG SELRCT

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

Saturday, November 30, 2019

テキストエリアの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>


Thursday, November 28, 2019

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


これで取得できた

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

(SITENAME と TICKET_ID は各自の対象の値を入れる)


Thursday, November 21, 2019

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



これを

こうしたい


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から削除した一行分減らしていく





関連記事

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

Wednesday, November 20, 2019

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



デモ




コード.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>




Tuesday, November 19, 2019

keyCodeを取得して表示したい


キーボードのキーを押すとkeyCodeが表示されます。






コード.gs
function doGet() {
  return HtmlService.createHtmlOutputFromFile("index");
}
意訳
この機能がやること
指定したHTMLファイルを表示する




index.html
<!DOCTYPE html>
<html>
  <body>
    <label id="key_code" style="font-size: 30px;"></label>
    
    <script>
      document.body.onkeyup = bodyKeyup;
      
      function bodyKeyup(e) {
        document.getElementById("key_code").innerHTML = e.keyCode;
      }
    </script>
  </body>
</html>


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": {}
  }
}


Monday, November 18, 2019

ヘッダのテキストに一致する列を見つけたい


ヘッダのテキストに一致する列に入力する
では見つけた列に値を入力しました。

ここでは見つけるところまでのコードを改めて書きます。

titleとidとdescriptionをヘッダーに持つシートがあって

idは何列目にあるのかコードで書いてログに出してみる。



コード.gs
function run() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var targetHeader = "id";
  var targetCol = getHeaderCol(sheet, targetHeader);
  Logger.log(targetCol);
}

function getHeaderCol(sheet, targetHeader) {
  var lastCol = sheet.getLastColumn();
  var range = sheet.getRange(1, 1, 1, lastCol);
  var headers = range.getValues()[0];
  var targetCol = headers.indexOf(targetHeader) + 1;
  return targetCol;
}




関連記事

ヘッダのテキストに一致する列に入力する

Sunday, November 17, 2019

スプレッドシートの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に保存したい

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

  1. テキストボックスにスプレッドシートのURLを入力する
  2. saveボタンをクリックする
  3. .gs側でスプレッドシートのnameを取得する
  4. urlとnameを{url: name}の形でスクリプトのプロパティに保存する
  5. HTMLにJSONで返してselectのoptionのidにurlを、innerHTMLにnameを入れる
  6. open sheetのhrefにurlを入れる
  7. 別のスプレッドシートのURLを入れてsaveボタンをクリックする
  8. selectのoptionが増えてopen sheetのhrefも変わる
  9. 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>



Saturday, November 16, 2019

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


スプレッドシートに2つのシート(シート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



漢字以外も後ろのひらがなを1文字だけくっつけて区切ってみたくなった。

デモ文章
正規表現を使って、漢字+ひらがな1文字で区切ってみたくなった。漢字以外の文字でもカタカナやハンカクの文字やabcやABCは異なり、数字の1と1も違う。


このように区切りたくて
正規表現を,使っ,て,漢字,ひらがな,1,文字で,区切っ,てみたくなった,漢字以外の,文字で,も,カタカナや,ハンカクの,文字や,abcや,ABCは,異な,り,数字の,1と,1も,違う


書いた正規表現がこちら
/[々〆〇〻㐂-頻]+[ぁ-ゟー]|[々〆〇〻㐂-頻]+|[ぁ-ゟー]+|[゠-ヿ]+[ぁ-ゟー]|[゠-ヿ]|[ヲ-゚]+[ぁ-ゟー]|[゠-ヿ]|[a-zA-Z0-9]+[ぁ-ゟー]|[a-zA-Z0-9]|[a-zA-Z0-9]+[ぁ-ゟー]|[a-zA-Z0-9]/g



関連記事

テキストを漢字、ひらがな、カタカナ、半角カタカナ、半角英数、全角英数に分割したい


デモ環境
正規表現を書いてmatchで抽出する

Wednesday, November 13, 2019

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/

Tuesday, November 12, 2019

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;
}



関連記事

GASからLambdaにzendesk_idを渡してzendesk APIのGETで情報取得したい

Sunday, November 10, 2019

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>


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

Saturday, November 9, 2019

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


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

ここでやること

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




コマンド
ローカルリポジトリのフォルダ $ 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/

Thursday, November 7, 2019

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

Wednesday, November 6, 2019

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の件名でググってしまいましたが、一番上に「安全でないルールを修正する」が出てきたので、たぶんこのブログに辿り着く人はいないと思いますが、備忘録として。

Monday, November 4, 2019

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
}]



デモデータ

datetasklabel
2019/11/01task1label1
2019/11/01task2label2
2019/11/01task3label1
2019/11/02task4label3
2019/11/02task5label2
2019/11/02task6label1



コード.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に値が入っている場合と入っていない場合があった。

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

  1. 「自動」の場合はlabelに一行目の値が入っていました
  2. 「書式なしテキスト」の場合は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
}



関連記事

Google Visualization APIでシートのデータを取得する
Google Visualization APIの vはvalueで、fはformettedValueっぽい

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


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

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

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」



関連記事

オブジェクトの中から条件に一致する要素を抜き出す(filter)

Sunday, November 3, 2019

SlackでRSSのフィードをキャッチしたい


公式リファレンスにありました
Slack に RSS フィードを追加する
https://slack.com/intl/ja-jp/help/articles/218688467-add-rss-feeds-to-slack


手元でやった手順

Slackにログインして
https://slack.com/apps/A0F81R7U7-rss
にアクセスして


Add to Slackをクリックします


Add RSS integration をクリックします


ここでFeed URLと通知するChannelを設定できました



参考

Slack に RSS フィードを追加する
https://slack.com/intl/ja-jp/help/articles/218688467-add-rss-feeds-to-slack

RSS wikipedia
https://ja.wikipedia.org/wiki/RSS

Saturday, November 2, 2019

Google Visualization APIの vはvalueで、fはformettedValueっぽい


公式のリファレンスを見るとこう書かれていました。

  • v [Optional] The cell value. 
  • f [Optional] A string version of the v value, formatted for display. 


vはセルの値
fはそれをフォーマットした値
ということらしい。


参考

Cell Objects
https://developers.google.com/chart/interactive/docs/reference#cell_object

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...