LANG SELRCT

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

Monday, December 31, 2018

LINE BOTのリッチメニューをAPIで作ってみる


以前書いたLINE BOTで「リッチメニュー」を表示してみる
では、管理画面からぽちぽちクリックしながらリッチメニューを表示しました。

今回は個人的に作りたい仕組みの都合でAPIでコントロールしたくてコードを書きました。

知識不足のため思った以上に時間がかかってしまい、途中でこのAPIに手を出したことを後悔しながら、公式リファレンスを上から順番にやってみました。
いくつか試せていないAPIもありますが、Google Apps Scriptでやったりcurlでやったりした過程を書き残しました。


今回試した公式リファレンス
https://developers.line.biz/en/reference/messaging-api/#rich-menu


Create rich menu


https://developers.line.biz/en/reference/messaging-api/#rich-menu
に例示されているCreate rich menuのコードをそのまま使ってみます


コード.gs
function get_token() {
  return PropertiesService.getScriptProperties().getProperty('token');
}

function createRichMenu() {
  var postData = {
    "size": {
      "width": 2500,
      "height": 1686
    },
    "selected": false,
    "name": "Nice richmenu",
    "chatBarText": "Tap here",
    "areas": [
      {
        "bounds": {
          "x": 0,
          "y": 0,
          "width": 2500,
          "height": 1686
        },
        "action": {
          "type": "postback",
          "data": "action=buy&itemid=123"
        }
      }
    ]
  }
  var url = 'https://api.line.me/v2/bot/richmenu';
  
  var options = {
    "method": "post",
    "headers": {
      "Content-Type": "application/json",
      "Authorization": "Bearer " + get_token()
    },
    "payload": JSON.stringify(postData)
  };
  var result = UrlFetchApp.fetch(url, options);
  Logger.log(result);
}


Upload rich menu image


※一度設定したら変更できないので、変更したい場合は新規リッチメニューを作成することになります

curlコマンドの中の -T をコード.gsの中で表現する方法がよくわからないので
リファレンスのとおりにcurlでやってみます

curlコマンド
curl -X POST https://api.line.me/v2/bot/richmenu/richmenu-ID/content \ 
-H 'Authorization: Bearer  アクセストークン' \ 
-H 'Content-Type: image/jpeg' \
-H 'Expect:'  \
-T img.jpg
意訳
richmenu-IDを入れる
アクセストークンを入れる
image/jpegまたはimage/png
これは公式リファレンスのとおりに書いておく
この例ではMacのhomeに保存してある2500×1686ピクセルのimg.jpgファイルを指定している


curlコマンドはMacのターミナルやiTermを起動して入力できます
上記のcurlコマンドの中の以下3箇所を書き換えて
一行ずつenterを押していくとできるはず
  • richmenu-ID
  • アクセストークン
  • img.jpg

この画像アップロードが一番つまずきました、、


Download rich menu image


これも公式リファレンスの通りcurlでやってみましたが、ダウンロードしたファイルが壊れていた、、

今やらなくてもいいのでこれはもうこれ以上追わないことにしました。


Get rich menu list


これは以下のようなコード.gsでログに出せます

コード.gs
function getRichMenuList() {
  var url = 'https://api.line.me/v2/bot/richmenu/list';
  var options = {
    "method": "get",
    "headers": {
      "Content-Type": "application/json",
      "Authorization": "Bearer " + get_token()
    }
  };
  var response = UrlFetchApp.fetch(url, options);
  Logger.log(response);
}


Get rich menu


これも以下のようなコード.gsでログに出せます
richmenu-IDを対象のIDに書き換えます

コード.gs
function getRichMenu() {
  var id = 'richmenu-ID';
  var url = 'https://api.line.me/v2/bot/richmenu/' + id;
  var options = {
    "method": "get",
    "headers": {
      "Content-Type": "application/json",
      "Authorization": "Bearer " + get_token()
    }
  };
  var response = UrlFetchApp.fetch(url, options);
  Logger.log(response);
}




Delete rich menu


これは以下のようなコード.gsでできそうな気がしましたが

コード.gs
function deleteRichMenu() {
  var id = 'richmenu-ID';
  var url = 'https://api.line.me/v2/bot/richmenu/';
  var options = {
    "method": "delete",
    "headers": {
      "Content-Type": "application/json",
      "Authorization": "Bearer " + get_token()
    }
  };
  var response = UrlFetchApp.fetch(url, options);
  Logger.log(response);
}


実行すると 'DELETE', is not supportedというエラーが出たので

curlでやってみます

curlコマンド
curl -v -X DELETE https://api.line.me/v2/bot/richmenu/richmenu-ID \
-H 'Authorization: Bearer  アクセストークン '
意訳
richmenu-IDを入れる
アクセストークンを入れる

これで指定したrichmenu-IDが削除できました


Set default rich menu


これも以下のようなコード.gsで実行できます
richmenu-IDを対象のIDに書き換えます

コード.gs
function setDefaultRichMenu() {
  var id = 'richmenu-ID';
  var url = 'https://api.line.me/v2/bot/user/all/richmenu/' + id;
  var options = {
    "method": "post",
    "headers": {
      "Content-Type": "application/json",
      "Authorization": "Bearer " + get_token()
    }
  };
  var response = UrlFetchApp.fetch(url, options);
  Logger.log(response);
}



Get default rich menu ID


これも以下のようなコード.gsで実行できます

コード.gs
function getDefaultRichMenuId() {
  var url = 'https://api.line.me/v2/bot/user/all/richmenu';
  var options = {
    "method": "get",
    "headers": {
      "Content-Type": "application/json",
      "Authorization": "Bearer " + get_token()
    }
  };
  var response = UrlFetchApp.fetch(url, options);
  Logger.log(response);
}



Cancel default rich menu
Link rich menu to user
Get rich menu ID of user
Unlink rich menu from user

このあたりは今やる必要ないので、後で必要になった時にやろうと思います。


Sunday, December 30, 2018

localStorageを使ってみる(Web Storage)


localStorageを利用すると、ブラウザに{ key: value }のかたちでデータを保存することができます。
そして一度開いたアプリケーションを閉じたあと、再度起動した際にその保存したデータを呼び出すことができます。
実際にそういうことがしたいと思った時に調べるとおそらくこのあたりのサイトが参考になるかと思います。



今回は参考にしたサイトにあるこの3つをGoogle Apps ScriptのHtmlServiceでやってみました
  1. 保存する
    localStorage.setItem(key, value);
  2. 取得する
    localStorage.getItem(key);
  3. 削除する
    localStorage.removeItem(key);



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




index.html
<!DOCTYPE html>
<html>
<body>
  <button id='set'>setStorage</button><br>
  <button id='get'>getStorage</button><br>
  <button id='delete'>deleteStorage</button>
<script>
document.getElementById('set').onclick = setClicked;
document.getElementById('get').onclick = getClicked;
document.getElementById('delete').onclick = deleteClicked;


function setClicked() {
  var key = 'key1';
  var value = 'value1';
  var item = setStorage(key, value);
  alert(JSON.stringify(item));
}

function getClicked() {
  var key = 'key1';
  var value = getStorage(key);
  alert(value);
}

function deleteClicked() {
  var key = 'key1';
  deleteStorage(key);
  var value = getStorage(key);
  alert(value);
}

function setStorage(key, value) {
  var item = localStorage.setItem(key, value);
  return localStorage;
}

function getStorage(key) {
  var value = localStorage.getItem(key);
  return value;
}

function deleteStorage(key) {
  localStorage.removeItem(key);
}

</script>
</body>
</html>

意訳
 


localStorageに保存するボタン
localStorageから取得するボタン
localStorageを削除するボタン

setボタンがクリックされたらsetClickedを実行する
getボタンがクリックされたらgetClickedを実行する
delete'ボタンがクリックされたらdeleteClickedを実行する


この機能がやること
keyを決めて
valueを決めて
setStorageに渡して
返ってきた値をJSONに変換してアラートに出す


この機能がやること
keyを決めて
getStorageに渡して
返ってきた値をアラートに出す


この機能がやること
keyを決めて
deleteStorageに渡して
getStorageに渡してから
返ってきた値をアラートに出す


この機能がやること
受け取ったkeyとvalueをlocalStorageに保存して
返す


この機能がやること
受け取ったkeyのvalueをlocalStorageから取得して
返す


この機能がやること
受け取ったkeyのデータをlocalStorageから削除する








Webアプリケーションを開くとこのようなボタンが表示されます

「setStorage」をクリックするとこのようにsetしたkeyとvalueが表示されます


「getStorage」をクリックするとこのようにsetされているkey1の値が表示されます


「deleteStorage」をクリックするとkey1のデータが削除されてnullが返ってきます


蛇足


Google Apps ScriptにはPropertiesServiceを利用して{ key: value }の形でデータを保存して呼び出すことができますが、そんなことができると知らなかった頃はlocalStorageを使っていました。さらにそのlocalStorageを知らなかった頃は、外部のテキストファイルにデータを書き出して起動時に読み込んだりなど、手探りであの手この手を考えてやっていました。

Google Apps ScriptでHtml Serviceを使う時はPropertiesServiceを利用しているので、localStorageを使うことはなくなりましたが、ふと思い出したので試しながら書き残しておきました。


localStorageって何者なのか調べてみる
  • HTML5から使えるようになった機能
  • localStorageには2つの意味がある
    • ひとつはこの記事で書いたブラウザに保存する(Web Storage)
    • もう一つはローカルコンピュータ自体に保存する
  • Web StorageにはSession Storageというのもあって、それはブラウザを開いている間(セッション中)だけ使えて、閉じたら保存したデータは消える
  • cookieとは違う仕組みで保存する



PropertiesServiceの使い方についてもいくつか参考程度に書きました
http://www.pre-practice.net/p/contents.html#propertiesservice


指定した日時に実行するトリガーをコードで書きたい


以前書いたこの記事では
毎日特定の日時ちょうどに実行するトリガーを作る
毎日繰り返すトリガーを設定しました


今回は繰り返さない一度きりのトリガーをコードで書いてみます
コードを使わずに画面でぽちぽちやる方法はこちらに書きました



コード.gs
function run(){
  var date = '2018/12/30';
  var hour = 15;
  var min = 11;
  setTrigger(date, hour, min)
}

function setTrigger(date, hour, min) {
  var date = new Date(date);
  date.setHours(hour);
  date.setMinutes(min);
  var trigger = ScriptApp.newTrigger('send_mail')
    .timeBased()
    .at(date)
    .create();
} 

function send_mail(){
  var mail_address = Session.getActiveUser().getEmail();
  MailApp.sendEmail(mail_address, "実行したスクリプトID", ScriptApp.getScriptId());
}
意訳
この機能がやること
 実行したい日付を決めて
 時間を決めて
 分を決めて
setTriggerに渡す


この機能がやること(日付、時間、分を受け取って)
new Dateで日付をJSで扱える形にして
時間を設定して
分を設定して
'send_mail'の新規トリガーを作成して
時間ベースで
設定した日時で
トリガーを作成する


この機能がやること
アクティブユーザのemailを取得して
email, 件名, 本文を設定してメールを送信する



試してみる


run()を実行します
編集 > 現在のプロジェクトのトリガーを開きます


このようなトリガーが登録されています


右端の鉛筆ボタンから編集画面を開くと
指定した日時が設定されています


指定日時になるとこのようなメールが飛んできます
※setMinutesで指定した分(例では11分)のピッタリ0秒ではなく
その分(15:11〜15:12)の60秒の間のどこかで実行されるようです


関連記事




参考

Class ClockTriggerBuilder
https://developers.google.com/apps-script/reference/script/clock-trigger-builder

Saturday, December 29, 2018

LINE BOTの「Flex Message」でpostbackを使ってみる


Flex Messgeは以前
LINE BOTで「Flex Message」を使ってみる
でどのように表示できるか試してみました

今回は任意のタイミングでPUSHして
postbackを使ったコードを書きました

postbackを使うと何がいいか
  • そのボタンを押した時に、そのボタンに設定したdataを返すことができる
※以下の例のURIボタンやmessageボタンのように、固定テキストを返したりページを開いたりするだけならpostback不要

ちなみに、ボタンテンプレートはスマホのみでしか表示できませんが、Flex MessageはMacでも表示されました



コード.gs
function get_token() {
  return PropertiesService.getScriptProperties().getProperty('token');
}

function get_user_id() {
  return PropertiesService.getScriptProperties().getProperty('user_id');
}

function doPost(e) {
  var contents = e.postData.contents;
  var obj = JSON.parse(contents);
  var events = obj["events"];
  for (var i = 0; i < events.length; i++) {
    if (events[i].type == "postback") {
      post_back(events[i]);
    }
  }
}

function flex_message() {
  var postData = {
    "to": get_user_id(),
    "messages": [{
      "type": "flex",
      "altText": "this is a flex message",
      "contents":
      {
        "type": "bubble",
        "body": {
          "type": "box",
          "layout": "vertical",
          "spacing": "md",
          "contents": [
            {
              "type": "text",
              "text": "Please select"
            },

            {
              "type": "button",
              "style": "primary",
              "action": {
              "type": "postback",
              "label": "POSTBACK",
              "data": "postback selected"
              }
            },            
            
            {
              "type": "button",
              "style": "primary",
              "color": "#ffc0cb",
              "action": {
                "type": "uri",
                "label": "URI",
                "uri": "https://linecorp.com"
              }
            },

            {
              "type": "button",
              "style": "secondary",
              "action": {
              "type": "message",
              "label": "message",
              "text": "text:message"
              }
            },
            
            {
              "type": "button",
              "style": "primary",
              "style": "secondary",
              "color": "#7fffd4",
              "action": {
              "type": "datetimepicker",
              "label": "datetimepicker",
              "data": "datetimepicker selected",
              "mode": "datetime",
              "initial": "2018-12-29T00:00",
              "max":   "2019-10-15T23:59",
              "min": "2018-12-29T00:00"
              }
            }
           
          ]
        }
      }
    }]
  };
  var url = "https://api.line.me/v2/bot/message/push";
  fetch_data(postData, url);
}

function post_back(e) {
  var data = e.postback.data;
  var replay_text = "";
  if (data == "postback selected") {
    replay_text = data;
  } else if (data == "datetimepicker selected") {
    replay_text = data + "\n" + e.postback.params['datetime'];
  }

  var postData = {
    "replyToken": e.replyToken,
    "messages": [{
      "type": "text",
      "text": replay_text + "\n" + JSON.stringify(e.postback)
    }]
  };
  var url = "https://api.line.me/v2/bot/message/reply";
  fetch_data(postData, url);
}

function fetch_data(postData, url) {
  var options = {
    "method": "post",
    "headers": {
      "Content-Type": "application/json",
      "Authorization": "Bearer " + get_token()
    },
    "payload": JSON.stringify(postData)
  };
  UrlFetchApp.fetch(url, options);
}


関連記事


LINE BOTで「ボタン」テンプレートメッセージをPUSHしてみる


「ボタン」テンプレートメッセージは以前
LINE BOTで「ボタン」テンプレートメッセージを表示する
ということをやってみました

それを元にして
今回はPUSHメッセージを利用して
任意のタイミングでこのようなテンプレートメッセージを表示してみます

元の記事と異なる点
  1. アクセストークンとuser IDはスクリプトのプロパティに保存しています
  2. thumbnailImageUrlは今回利用しないのでコメントアウトしています
  3. fetch_data(postData, url)でそれぞれのurlを渡すようにしています
    • 理由は「今回ぶつかった壁」に書きました


元の記事でやっていたことのおさらい

元の記事では「button」というメッセージが送られた時に
このようなテンプレートメッセージを返して
それぞれ反応する選択肢を配置していました




コード.gs
function get_token() {
  return PropertiesService.getScriptProperties().getProperty('token');
}

function get_user_id() {
  return PropertiesService.getScriptProperties().getProperty('user_id');
}

function doPost(e) {
  var contents = e.postData.contents;
  var obj = JSON.parse(contents);
  var events = obj["events"];
  for (var i = 0; i < events.length; i++) {
    if (events[i].type == "postback") {
      post_back(events[i]);
    }
  }
}

function push_message(e) {
    var postData = {
      "to": get_user_id(),
      "messages": [{
        "type": "template",
        "altText": "select",
        "template": {
          "type": "buttons",
          //"thumbnailImageUrl": "https://~.png",
          "title": "Menu",
          "text": "Please select",
          "actions": [{
              "type": "postback",
              "label": "postback",
              "data": "postback selected"
            },
            {
              "type": "message",
              "label": "message",
              "text": "text:message"
            },
            {
              "type": "uri",
              "label": "uri",
              "uri": "https://linecorp.com"
            },
            {
              "type": "datetimepicker",
              "label": "datetimepicker",
              "data": "datetimepicker selected",
              "mode": "datetime",
              "initial": "2018-12-29T00:00",
              "max":   "2019-10-15T23:59",
              "min": "2018-12-29T00:00"
            }
          ]
        }
      }]
    };
  var url = "https://api.line.me/v2/bot/message/push";
  fetch_data(postData, url);
}

function post_back(e) {
  var data = e.postback.data;
  var replay_text = "";
  if (data == "postback selected") {
    replay_text = data;
  } else if (data == "datetimepicker selected") {
    replay_text = data + "\n" + e.postback.params['datetime'];
  }

  var postData = {
    "replyToken": e.replyToken,
    "messages": [{
      "type": "text",
      "text": replay_text + "\n" + JSON.stringify(e.postback)
    }]
  };
  var url = "https://api.line.me/v2/bot/message/reply";
  fetch_data(postData, url);
}

function fetch_data(postData, url) {
  var options = {
    "method": "post",
    "headers": {
      "Content-Type": "application/json",
      "Authorization": "Bearer " + get_token()
    },
    "payload": JSON.stringify(postData)
  };
  UrlFetchApp.fetch(url, options);
}


関連記事


今回ぶつかった壁

fetch_data(){} の関数が元の記事では

function fetch_data(postData) {}
になっていますが、今回はurlも渡して

function fetch_data(postData, url) {}
のようにしました。

これはpushで使うurlとreplyで使うurlが異なるためです。

UrlFetchApp.fetch(url, options)を共通処理にしているので、
元記事のまま
"https://api.line.me/v2/bot/message/reply"
にしたらPUSHできなくて

PUSHしたくて
"https://api.line.me/v2/bot/message/push"
にしたらpostbackでreplyが効かなくなってしまい、、

それに気づくのにちょっと手こずってしまいました。

Sunday, December 23, 2018

AdminDirectory.Users.get(userEmail)でユーザを取得する


公式リファレンスにあるgetUser()をやってみました
https://developers.google.com/apps-script/advanced/admin-sdk-directory


事前準備

実行するには拡張のAPIを有効する必要があります

手順は以下リンクの記事と同じです
AdminDirectoryでUsers.listを取得したい



コード.gs
function getUser() {
  var userEmail = 'EMAIL';
  var user = AdminDirectory.Users.get(userEmail);
  Logger.log('User data:\n %s', JSON.stringify(user, null, 2));
}


getUser()を実行するとこのようなデータが返ってきます


ログ
User data: {
    "orgUnitPath": "/",
    "isMailboxSetup": true,
    "id": "ID",
    "isAdmin": true,
    "suspended": false,
    "isDelegatedAdmin": false,
    "isEnforcedIn2Sv": false,
    "etag": "\"TAG\"",
    "ipWhitelisted": false,
    "archived": false,
    "changePasswordAtNextLogin": false,
    "customerId": "ID",
    "nonEditableAliases": ["MAIL.test-google-a.com"],
    "includeInGlobalAddressList": true,
    "lastLoginTime": "2018-12-22T23:56:57.000Z",
    "primaryEmail": "MAIL",
    "isEnrolledIn2Sv": true,
    "kind": "admin#directory#user",
    "name": {
        "givenName": "FIRST NAME",
        "familyName": "FAMILY NAME",
        "fullName": "FULL NAME"
    },
    "creationTime": "2018-09-03T00:15:05.000Z",
    "emails": [{
        "address": "MAIL",
        "primary": true
    }, {
        "address": "MAIL.test-google-a.com"
    }],
    "agreedToTerms": true
}


参考

Admin SDK Directory Service
https://developers.google.com/apps-script/advanced/admin-sdk-directory

Saturday, December 22, 2018

AdminDirectoryでUsers.listを取得したい

事前準備

  • AdminDirectory APIをONにする
  • Admin SDKを有効にする

上記2つを有効にする手順

1. リソース > Google の拡張サービスを選択します

2. Admin Directory APIを「ON」にします
「Google Cloud Platform API ダッシュボード」のリンクをクリックします


3. 左のメニューで「ライブラリ」を選択します

4. 検索ボックスで「admin」と入力します
「Admin SDK」を選択します

5.「有効にする」をクリックします


6.「OK」をクリックします


APIを有効にしてからコードを保存して実行します

公式サイトのコードで試してみます
https://developers.google.com/apps-script/advanced/admin-sdk-directory

※domain: 'example.com',はGsuitで使用しているドメインに書き換えます

コード.gs
function listAllUsers() {
  var pageToken;
  var page;
  do {
    page = AdminDirectory.Users.list({
      domain: 'example.com',
      orderBy: 'givenName',
      maxResults: 100,
      pageToken: pageToken
    });
    var users = page.users;
    if (users) {
      for (var i = 0; i < users.length; i++) {
        var user = users[i];
        Logger.log('%s (%s)', user.name.fullName, user.primaryEmail);
      }
    } else {
      Logger.log('No users found.');
    }
    pageToken = page.nextPageToken;
  } while (pageToken);
}


listAllUsers()を実行すると
ユーザのリストがログに出力されます

参考

アプリへのアクセス許可を削除する


アカウントにアクセスできるアプリの一覧を表示するリンク
https://myaccount.google.com/permissions?pli=1


Google Apps Scriptでアプリへのアクセス許可が必要な時に
このような許可を確認が求められて
手順に沿って進んで


「許可」をクリックすると


以下のようなメールが送られてきます
上記の「アカウント情報」をクリックすると

以下のような一覧が表示されます
アクセス権を削除したいプロジェクトを選択して
「アクセス権の削除」をクリックします


表示されるダイアログで「OK」をクリックします
これでアプリへのアクセス許可を削除できました。

Sunday, December 16, 2018

PropertiesServiceを使ってスクリプトのプロパティにSecretKeyを保存しておく


スクリプトのプロパティの場所と保存・取得の方法について書きます
storageとしてデータの読み書きができるので便利です

ブログやGithub等でコードを公開するときにも
トークンなどの機密情報をコードにべた書きして消したり消し忘れたりなどを心配しなくてよくなります


スクリプトのプロパティを使ってみる

どこにあるかというとここにあります↓

ファイル > プロジェクトのプロパティを開きます


スクリプトのプロパティタブを開きます

「行を追加」してプロパティの名前(例ではtoken)と値(abc)を入れて「保存」します

どうやって取得するかというと
以下のコードで取得することができます↓


コード.gs
function get_token() {
  return PropertiesService.getScriptProperties().getProperty('token')
}
意訳
この機能がやること
スクリプトのプロパティからtokenを取得して返す



確認のためにログに出してみると

コード.gs
function run() {
  var token = get_token();
  Logger.log(token);
}

function get_token() {
  return PropertiesService.getScriptProperties().getProperty('token');
}

意訳
この機能がやること
get_token()から返ってきた値を
ログに出す


この機能がやること
スクリプトのプロパティからtokenを取得して返す




run()を実行した結果
保存した値がログに出ます



関連

PropertiesServiceにデータを保存して次回実行時に読み込む
ScriptPropertiesを読み書きしてみる


参考

Properties Service
https://developers.google.com/apps-script/reference/properties/

西暦とその年の何週目かをWEEKNUMとARRAYFORMULAで出してみる


WEEKNUM(日付, 種類)を使ってみる

ARRAYFORMULA関数を組み合わせて
与えられた日付がその年の何週目にあたるか出したときの備忘録です



日付がその年の何週目かを知りたいとき

CONCATENATEで書くとこのように書けますが
=CONCATENATE(LEFT(B2,4), "-", WEEKNUM(B2,2),"週")

これだとARRAYFORMULAで囲むときに上手く行かなかったので
(補足の失敗談参照)

同じ結果を得られる&で結合した数式を例にします
=LEFT(B2,4)&"-"&WEEKNUM(B2,2)&"週"

これをARRAYFORMULA関数で先頭行に書くだけにしたい
というのがこの記事でやりたいことです

これを
=LEFT(B2,4)&"-"&WEEKNUM(B2,2)&"週"

こうする
=ARRAYFORMULA(LEFT(B2:INDIRECT("B"&COUNTA(B2:B)+1),4)&"-"&WEEKNUM(B2:INDIRECT("B"&COUNTA(B2:B)+1),2)&"週")

この長い数式を分解して理解する手順は以下の記事に書きました
特定の列の値から特定の文字だけ抜き出したい(ARRAYFORMULA関数で)



補足 (失敗談)

この数式は
=LEFT(B2,4)&"-"&WEEKNUM(B2,2)&"週"

こう書いても同じ結果を得られますが
=CONCATENATE(LEFT(B2,4), "-", WEEKNUM(B2,2),"週")

ARRAYFORMULA関数の中でこのようにつなげると
=ARRAYFORMULA(CONCATENATE(LEFT(B2:INDIRECT("B"&COUNTA(B2:B)+1),4),"-",WEEKNUM(B2:INDIRECT("B"&COUNTA(B2:B)+1),2)&"週"))

結果が1つのセルに結合されてうまくいきませんでした


関連記事

特定の列の値から特定の文字だけ抜き出したい(ARRAYFORMULA関数で)

Saturday, December 15, 2018

CONCATENATE関数を使って文字列を結合してみる


複数のセルの値をつなげる関数ないかなと探して見つけたのははるか昔のことで
今では当たり前のように使っているCONCATNATE関数について軽く書いてみます


D1に以下のように書くと
=CONCATENATE(A1:C1)
あい う えお
を結合して
あいうえお
となり、A1からC1の値がつながる


D1
=CONCATENATE(A1,C1)
あい えお
を結合して
あいえお
となり、指定されていないB1の値は含まれない


INDIRECT関数を使ってみる


"A1"という文字列で A1を参照する

=INDIRECT("A1")

&でつなげたこれも"A1"という文字列
=INDIRECT("A"&"1")



参照方法には =A1 もある
けれどそれは行数を決め打ちして参照するときに使うもので

以下のように行数が可変のケースでは決め打ちでは正しく参照できない


参考

INDIRECT
https://support.google.com/docs/answer/3093377?hl=ja

特定の列の値から特定の文字だけ抜き出したい(ARRAYFORMULA関数で)


A列の日時から日付だけを取り出してB列に書き出したい


今回書いた関数

年月日を取り出す (10文字)
=ARRAYFORMULA(MID(A2:INDIRECT("A"&COUNTA(A2:A)+1),1,10))

年月を取り出す (7文字)
=ARRAYFORMULA(MID(A2:INDIRECT("A"&COUNTA(A2:A)+1),1,7))

年だけ取り出す (4文字)
=ARRAYFORMULA(MID(A2:INDIRECT("A"&COUNTA(A2:A)+1),1,4))



使う関数
  • ARRAYFORMULA
  • IF
  • INDIRECT
  • COUNTA
  • MID


作ったシート

試す用の表

日時日付
2018/12/15 0:00:002018/12/15
2018/12/16 0:00:002018/12/16
2018/12/17 0:00:002018/12/17
2018/12/18 0:00:002018/12/18
2018/12/19 0:00:002018/12/19
2018/12/19 0:00:002018/12/19


補足

ちなみに、ARRAYFORMULA関数を使わなければ
B2に =MID(A2,1,10) とだけ書いて下に引っ張れば同じ結果を得られます

この作業が一度だけなら良いですが
何度も同じ作業をする場合は毎回下に引っ張る作業が地味につらくなります
スクリプトで自動入力してもよいですが
ARRAYFORMULA関数でやってみたのがこの記事です


数式を分解して理解する

=MID(A2,1,10)

=ARRAYFORMULA(MID(A2:INDIRECT("A"&COUNTA(A2:A)+1),1,10))
に書き換える

ここまで関数が多いとよくわからないので
↓この部分を分解しながら意訳してみます
:INDIRECT("A"&COUNTA(A2:A)+1)

A2以降でA列に存在する値の個数
COUNTA(A2:A)

1行目はヘッダなのでその1行分を値の個数に足して行数を合わせる
COUNTA(A2:A)+1
上にあるシートの例のようにA2以降に6個の値が入っている時の最終行は7行目になります
単純にCOUNTA(A2:A)の個数では6になってしまいます
値が入っている7行目まで範囲に入れたいので +1します

INDIRECT("A7")の形にして
INDIRECT("A"&COUNTA(A2:A)+1)

A2:A7の範囲を作って
A2:INDIRECT("A"&COUNTA(A2:A)+1)

MID(A2:A7,1,10)の形にして
MID(A2:INDIRECT("A"&COUNTA(A2:A)+1),1,10)

ARRAYFORMULAで囲んでA2以降にも自動で反映させる
=ARRAYFORMULA(MID(A2:INDIRECT("A"&COUNTA(A2:A)+1),1,10))


ちなみに西暦の年だけを取り出す場合は

このように取り出す文字数を変えるだけ
=ARRAYFORMULA(MID(A2:INDIRECT("A"&COUNTA(A2:A)+1),1,4))

LEFT関数でも同じような事ができます
=ARRAYFORMULA(LEFT(A2:INDIRECT("A"&COUNTA(A2:A)+1),4))


関連記事


IF関数を使ってみる
INDIRECT関数を使ってみる
COUNTIFSを使ってみる
MID(文字列, 開始位置, 長さ)を使ってみる

IF関数を使ってみる


A列の値が はれ だったら B列に晴れマークを入力する

このようにA列に「はれ、あめ、ゆき、くもり」などを入力した表があって
B1に =if(A1="はれ","☼","") と入力して以下の行にも数式を反映させると
A列が はれ のときだけB列に晴れマークが入力される


ちなみにB列にはこのような数式が入っています



スプレッドシートのUNIQUE関数を横に向けたい


スプレッドシートのUNIQUE関数とTRNASPOSE関数を組み合わせると
以下のようなことができます


B1に =transpose(unique(A2:A)) と書いて

A列のデータをB列以降にUNIQUE関数で書き出す


Google Apps Script側でAPIを用意してJSONを返したい


Google Apps Scriptで作ったWebアプリのURLにアクセスしたらJSONを返すという仕組みを作りたくて試したコードです。


コードだけ先に書いておきます

APIを提供する側のコード

コード.gs
function doGet () {
    var data =  {key1:"value1"};
    var json = JSON.stringify(data);
    return ContentService.createTextOutput(json)
    .setMimeType(ContentService.MimeType.JSON);
}
意訳
 
返したいデータを用意して
JSONに変換して
返す




APIを利用する側のコード

コード2.gs
function getData() {
  var url = 'https://script.google.com/macros/s/ID/exec'  
  var response = UrlFetchApp.fetch(url).getContentText();
  Logger.log(response);
  var jobj = JSON.parse(response);
  Logger.log(jobj['key1']);
}

意訳
この機能がやること
アクセス先のURLを指定して
データを取得して
ログに出す
JSONをオブジェクトに変換して
key1の値をログに出す





以下は実際に試すときの手順です


APIを提供する側のコード


コード.gs
function doGet () {
    var data =  {key1:"value1"};
    var json = JSON.stringify(data);
    return ContentService.createTextOutput(json)
    .setMimeType(ContentService.MimeType.JSON);
}
意訳
 
返したいデータを用意して
JSONに変換して
返す



公開 > ウェブアプリケーションとして導入して

アクセスできるユーザを全員(匿名ユーザを含む)にして導入します

このURLにアクセスするとdataがJSONで返ってきます



APIを利用する側のコード


コード2.gs
function getData() {
  var url = 'https://script.google.com/macros/s/ID/exec'  
  var response = UrlFetchApp.fetch(url).getContentText();
  Logger.log(response);
  var jobj = JSON.parse(response);
  Logger.log(jobj['key1']);
}

意訳
この機能がやること
アクセス先のURLを指定して
データを取得して
ログに出す
JSONをオブジェクトに変換して
key1の値をログに出す




実行 > 関数を実行 > getDataを選択します


許可を確認をクリックします


自分のアカウントを選択します


許可をクリックします

ログを見ると

このように取得できています


参考

Class ContentService
https://developers.google.com/apps-script/reference/content/content-service

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