自動化・効率化ならKTパソコンスクール

テレワークを応援する意図で、指定のフォルダ内のExcelファイルをGoogleスプレッドシートに一括変換できるツールを無料で提供することにしました。下のリンクからどうぞ。↓
Excelからスプレッドシートに一括変換

Googleスプレッドシートを使うメリットですが、

①　無料で使える

②　どこでも使える（オンライン上のGoogleドライブに保存されるため）

③　スマートフォン（アイフォン、アンドロイド）、タブレット、PC（ウィンドウズ、マック、リナックス）でも使える

④　複数人でファイル共有できる

⑤　リアルタイムで自動保存される

などがあります。（Googleの回し者ではありません。）


このツールを利用して、エクセルのファイルをGoogleスプレッドシートに変換するには

①　Googleアカウントがない方はアカウントを作成

　　　こちらを参考にしてください。→　https://support.google.com/accounts/answer/27441?hl=ja


②　Googleドライブにエクセルがあるフォルダをアップロード



③　フォルダのURLからフォルダIDを右クリックしてコピー


④　Excelからスプレッドシートに一括変換するツールを開く。

⑤　テキストボックスで右クリックしてコピーしたフォルダIDを貼り付け

⑥　「変換」ボタンをクリック（※ファイルの数によって、数秒から数分かかります。）

⑦　貴方のGoogleアカウントへのアクセスを許可する必要があります。

新たにWebサイトを立ち上げました。https://ktpcschool.com/です。今後はこちらのサイトでブログを更新する予定です。よろしくお願いします。

令和1年度の確定申告書Bの作成に役立つエクセルファイルを作成しました。所得税の計算、給与所得の計算、雑所得の計算、生命保険料控除の計算、医療費控除の計算に役立ちます。ダウンロードは下のリンクからどうぞ。↓
令和1年度の確定申告書Bの作成に役立つエクセルファイル

使用方法等
①　ダウンロードしたIncometax_R1.zipを解凍、どこかのフォルダに保存する。

②　Excel2007以降のバージョンをご使用の方は「所得税計算R1.xlsm」のほうを、Excel2003以前のバージョンをご使用の方は「所得税計算R1旧.xls」のほうをご使用ください。

③　給与所得の計算、雑所得の計算、生命保険料控除の計算が必要な方は先にそちらのシートを開いてください。手入力する箇所はセルの色が赤くなっています。赤枠で囲ってあるところは自動で計算されます。セルの色が赤いところに入力すると「所得税の計算」シートにも自動で転記されるようになっています。雑所得の計算が必要な方で昭和３０年１月１日以前に生まれた方はチェックを外してから入力してください。「所得税の計算」シートの手入力する箇所はセルの色が濃くなっています。赤枠で囲ってあるところは必要なところに数値を入力すると自動で計算されます。赤枠のところは保護をかけてあります。医療費控除の計算の所得金額の合計額は手入力してください。

④　ファイルの改変は自由ですが、著作者の許諾を得ずに元のファイル及びファイルを改変したものを配布したり販売したりすることは禁止します。また、このファイルを利用して発生した損害などに関して著作者は一切責任を負いません。

UWSCで西暦を和暦に変換するプログラムです。↓

//西暦を和暦に変換.uws

GETTIME()

date = G_TIME_YY + "/" + G_TIME_MM + "/" + G_TIME_DD

PRINT WesternCalendarToJapaneseCalendar(date)



//西暦から和暦に変換する関数

FUNCTION WesternCalendarToJapaneseCalendar(date)

	japanese_calendar = CreateOleObj("System.Globalization.JapaneseCalendar")

	era_num = japanese_calendar.GetEra(date)



	SELECT era_num

		CASE 1

			era = "明治"

		CASE 2

			era = "大正"

		CASE 3

			era = "昭和"

		CASE 4

			era = "平成"

		CASE 5

			era = "令和"

	SELEND



	year = japanese_calendar.GetYear(date)

	month = japanese_calendar.GetMonth(date)

	day = japanese_calendar.GetDayOfMonth(date)

	RESULT =  era + year + "年" + month + "月" + day + "日"

FEND

参考
JapaneseCalendar クラス

UWSCでブラウザを操作する時、InternetExplorerは比較的操作しやすいものの、Chromeの操作は難しいのですが、SeleniumBasicを使うとUWSCからでも操作しやすくなります。

SeleniumBasicは以下のサイトからダウンロードします。↓
https://github.com/florentbr/SeleniumBasic/releases/latest

現在（2019/11月）の最新版はv2.0.9.0になっているので、SeleniumBasic-2.0.9.0.exeをダウンロードします。インストールはダウンロードしたSeleniumBasic-2.0.9.0.exeをダブルクリックして、あとは画面指示に従うだけで大丈夫です。

その後、使用しているChromeのバージョンに合わせて、chromedriver.exeをダウンロードして入れ替える必要があります。



Chromeのバージョンは下の画像のように右上の「Google Chromeの設定」⇒「ヘルプ」⇒「Google Chromeについて」をクリックすると調べることができます。↓


chromedriver.exeは下のサイトからダウンロードします。↓
https://sites.google.com/a/chromium.org/chromedriver/downloads

下の画像の赤い四角の中から、さきほど調べたChromeのバージョンにあったものをダウンロードしてください。例えば、（Chromeのバージョンが78.○○なら、ChromeDriver 78.○○をダウンロード）↓


chromedriver.exeはWindows10の場合はC:\Users\ユーザー\AppData\Local\SeleniumBasicにあるので、上書き保存して、そこにあるものと差し替えします。

下は指定のキーワードで検索して、表示されたページのスクリーンショットを取るサンプルコードです。↓
SeleniumBasic検索.uws

①　driver = CreateOleObj("Selenium.WebDriver")とwaiter = CreateOleObj("Selenium.Waiter")でSeleniumのCOMオブジェクトを生成します。

②　driver.Startで新しいSeleniumセッションを開始します。１番目の引数はブラウザの種類、２番目の引数はベースURLとなります。

③　driver.Getで相対または絶対URLで新しいWebページをロードします。

④　driver.Window.Maximizeでウィンドウを最大化しています。ここはUWSCのGETIDでウィンドウのIDを取得してCTRLWIN(ID, MAX)としても良いと思います。

⑤　q = .FindElementByName("q")で指定された名前に一致する最初の要素を検索しています。これによってGoogleの検索ボックスのname属性を取得しています。これを調べるためにはChromeで下のように検索ボックスで右クリックし、検証をクリックします。↓（Googleにログインしている場合は一度ログアウトしてください。）


⑥　Chromeのデベロッパーツールが出てくるので、下の画像のようにinputタグのname属性を見ると、name="q"となっているのがわかると思います。これをFindElementByNameの引数にします。


⑦　q.SendKeysで検索する単語をブラウザに送信します。

⑧　q.Submitで検索します。

⑨　While waiter.Not(pos(.Title, search_word))・・・WendのところはUWSCのpos関数を使用してウィンドウのタイトルに検索する単語が出てくるまで待つ処理を入れています。これはUWSCのGETIDで指定のウィンドウが出てくるまで待つ処理にしても良いと思います。

⑩　screenshot = .TakeScreenshot.SaveAs(image_file)でスクリーンショットを指定のファイル名で保存します。ここはUWSCのSAVEIMGを使用しても良いと思います。

⑪　driver.QuitでSeleniumを終了し、関連するすべてのウィンドウを閉じます。


ウェブサイト内の要素を取得するのに、FindElementByNameの他にFindElementByClass、FindElementByCss、FindElementById、FindElementByLinkText、FindElementByPartialLinkText、FindElementByTag、FindElementByXPathなどがあります。

Excelを持っている方は開発タブからVisualBasic⇒ツール⇒参照設定として、Selenium Type Libraryにチェックを入れてOKをクリック、↓

表示⇒オブジェクトブラウザとしてWebDriverで検索すると、使用できる関数やプロパティがわかるので便利です。

下のコードはフォーム入力のサンプルです。↓
SeleniumBasicフォーム入力.uws（http://ktpcschool.web.fc2.com/practice.htmlのiframe内のフォームに入力）

iframe内の要素にはそのままではアクセスできないので、driver.SwitchToFrameでiframeに切り替える必要があります。引数にはインデックス、名前、要素を指定できます。

下はSeleniumBasicのGitHubのサイトです。↓
https://github.com/florentbr/SeleniumBasic

その他、「SeleniumBasic Excel」や「Selenium」などの単語で検索すると、いろいろと参考になるサイトがあるので、調べてみてください。

Google Apps Scriptで自然な会話ができるLINEボット（天気予報を答えることができる機能付き）を作成してみました。このLINEボットを試してみたい方は下のQRコードか、または、@976ixtvzでIDを検索して、友達追加してください。↓


このLINEボット作成にはLINE Messaging APIとリクルート社のTalk API、OpenWeatherMapのWeather APIを使用しました。

Messaging APIは簡単に言うと、LINEのボット作成用のAPIです。Talk APIはChatbotを作成するためのAPIです。ディープラーニングの一種であるRNNを使って、入力文からの応答文生成による日常会話応答機能を提供します。Weather APIは天気予報を取得するAPIです。以下はLINEボット作成の手順です。↓

①　LINE Developersに登録しアクセストークンを取得（詳しくはこちらを見るか、「LINE Developersに登録」で検索、その他、設定変更の必要あり）
②　Talk APIのAPIキーをこちらから取得
③　OpenWeatherMapのアカウントがない場合はこちらから作成
④　サインインした後、Weather APIのAPIキーをこちらから取得
⑤　Googleドライブのマイドライブ上でGoogle Apps Scriptを作成、下に記載のコードを貼り付け
⑥　「公開」⇒「ウェブ アプリケーションとして導入」
⑦　下の画像↓のとおりに設定して「更新」の後、「OK」（⑥⑦の手順はコードを編集した場合は毎回行う）



下は作成したGoogle Apps Scriptのコードです。↓（「LINE Messaging APIのアクセストークンをここに貼り付け」「Talk APIのキーをここに貼り付け」「Weather APIのキーをここに貼り付け」のところはそれぞれ取得したものを貼り付けてください。）

//LINE Messaging APIのアクセストークン

var CHANNEL_ACCESS_TOKEN = 'LINE Messaging APIのアクセストークンをここに貼り付け'; 



var LINE_ENDPOINT = 'https://api.line.me/v2/bot/message/reply';



var TALK_API_KEY = 'Talk APIのキーをここに貼り付け'; //A3RT(talk) - APIキー

var TALK_API_ENDPOINT = 'https://api.a3rt.recruit-tech.co.jp/talk/v1/smalltalk';



var WEATHER_API_KEY = 'Weather APIのキーをここに貼り付け'; //OpenWeather APIキー

var WEATHER_API_ENDPOINT = 'https://api.openweathermap.org/data/2.5/forecast/daily';



function doPost(e) {

  var events = JSON.parse(e.postData.contents).events;

  events.forEach(function(event){

    if(event.type === 'message'){  

      reply(event);

    }else if(event.type === 'follow'){

      follow(event);

    }else if(event.type === 'unfollow'){

      unFollow(event);

    }

  });

}



function reply(e) {

  var obtained_message = e.message; //取得したメッセージ

  var obtained_text = obtained_message.text; //取得したメッセージ中のテキスト

  var lat = 34.780035; //KTパソコンスクールの緯度

  var lon = 138.007834; //KTパソコンスクールの経度

  var weather_str = '天気';

  var today_str = '今日';

  var tomorrow_str = '明日';

  var day_after_tomorrow_str1 = '明後日';

  var day_after_tomorrow_str2 = 'あさって';

  var reply_message; //返信する内容

  

  if(obtained_text.indexOf(weather_str) !== -1){ //取得したメッセージに天気の文字があったら

    var weather_json = getResponceFromOpenWeatherAPI(lat, lon); //OpenWeather APIのレスポンスをJSONで取得する

    reply_message = getWeather(weather_json, 0);

    

    if(obtained_text.indexOf(tomorrow_str) !== -1){ //取得したメッセージに明日の文字があったら

      reply_message = getWeather(weather_json, 1);

    

    //取得したメッセージに明後日、または、あさっての文字があったら

    }else if(obtained_text.indexOf(day_after_tomorrow_str1) !== -1 || 

    obtained_text.indexOf(day_after_tomorrow_str2) !== -1){

      reply_message = getWeather(weather_json, 2);

    }

  }else{

    var talk_json = getResponceFromTalkAPI(obtained_text); //A3RT(talk) - APIのレスポンスをJSONで取得する

    

    if(talk_json.message === 'ok'){

      reply_message = talk_json.results[0].reply;

    }else{

      reply_message = obtained_text; //エラーの場合はオウム返しする

    }

  }

  

  var message = {

    'replyToken' : e.replyToken,

    'messages' : [

      {

        'type' : 'text',

        'text' : reply_message,

      }

    ]

  };



  var replyData = {

    'headers': {

      'Content-Type': 'application/json',

      'Authorization': 'Bearer ' + CHANNEL_ACCESS_TOKEN,

    },

    'method': 'post',

    'payload': JSON.stringify(message)

  };

  

  var response = UrlFetchApp.fetch(LINE_ENDPOINT, replyData);

  return response.getResponseCode();

}



//A3RT(talk) - APIのレスポンスをJSONで取得する

function getResponceFromTalkAPI(query){

  var formData = {

    'apikey': TALK_API_KEY,

    'query': query,

  }

  

  var options = {

    'method': 'post',

    'payload': formData

  };

  

  var responce = UrlFetchApp.fetch(TALK_API_ENDPOINT, options);

  var json = JSON.parse(responce.getContentText());

  

  return json;

}



//OpenWeather APIのレスポンスをJSONで取得する

function getResponceFromOpenWeatherAPI(lat, lon){

  var queryString = '?lat=' + lat + '&lon=' + lon + '&appid=' + WEATHER_API_KEY + '&units=metric&lang=ja&type=get';

  var url = WEATHER_API_ENDPOINT + queryString;

  var options =

  {

    'method': 'get',

  };

    

  var responce = UrlFetchApp.fetch(url, options);

  var json = JSON.parse(responce.getContentText());

  

  return json;

}



//天気を取得

function getWeather(weather_json, index){

  var weather = weather_json.list[index].weather[0].description;

  var temp_max = round(weather_json.list[index].temp.max, 0);

  var temp_min = round(weather_json.list[index].temp.min, 0);

  var speed = round(weather_json.list[index].speed, 1);

  var day = '今日';

  

  if(index === 1){

    day = '明日';

  }else if(index === 2){

    day = '明後日';

  }

  

  var reply_message = day + 'のKTパソコンスクール付近の天気は' + weather + 'です。最高気温は' 

  + temp_max + '℃、最低気温は' + temp_min + '℃、風速は毎秒' + speed + 'mです。';

  return reply_message;

}



//四捨五入

function round(number, precision){

  var shift = function (number, precision, reverseShift){

    if(reverseShift){

      precision = -precision;

    }  

    var numArray = ("" + number).split("e");

    return +(numArray[0] + "e" + (numArray[1] ? (+numArray[1] + precision) : precision));

  };

  return shift(Math.round(shift(number, precision, false)), precision, true);

}

天気予報は今日から明後日までに対応しています。明日の天気予報を知りたいときは「明日の天気は？」のようにすると答えます。下はやり取りのサンプルです。↓

Wordでアバウトに作った表をExcelのセルに取り込みたいときに使える方法です。Wordで下のような表を作ったとします。↓


これを次のように素直に選択してコピーし↓


エクセルに貼り付けると下のようになってしまいます。↓


このようなときは、一度メモ帳を開いて、貼り付けしてテキストファイルにして名前を付けて保存します。↓


名前は適当に付けます（今回はa.txtと名前を付けてデスクトップに保存）。

表を貼り付けたいエクセルのファイルを開いておきます。「データ」タブ⇒「テキストファイル」をクリックします。↓


「テキストファイルのインポート」のウィンドウでテキストファイル（今回はa.txt）を選択し、「インポート」をクリックします。↓


「テキストファイルウィザード」のウィンドウで「スペースによって右または左に揃えられた固定長フィールドのデータ」と「先頭行をデータの見出しとして使用する」をクリックして選択しておき、「次へ」をクリックします。↓


区切り位置を作成したいところでクリックします。↓



「次へ」をクリックします。↓


「完了」をクリックします。↓


「データの取り込み」ウィンドウで表を取り込みたいセルの位置を選択し、「OK」をクリックします。↓


そうすると下のように意図した通りになります。↓

前の記事AIカラス監視・撃退装置を作成のその後ですが、カラス発見後、水鉄砲を発射するようにしたのですが、あれからすっかりカラスも猫も来なくなりました。

雨対策としてアクリルパネルでカバーを作りました。認識はできるだけリアルタイムでできるようにこちらの記事にあるようにIntel® Movidius™ Neural Compute Stickというものを使っています。CPU温度対策として冷却用のファンを使用しています。CPU温度はかなり下がったのですが、気温が高いとエラーが発生してときどき途中でプログラムが止まってしまいます。最近はカラスも猫もほとんど来ないのですが、たまに来ることがあるのでまだ動作はさせています。⇓

Google Apps ScriptでGoogleスプレッドシート上でジオコーディング、リバースジオコーディングするプログラムを作りました。

ジオコーディングのシートは下のような状態とします。⇓


スタートボタンをクリックすると次のような状態になります。⇓

きっちりとした住所でなくてもある程度ジオコーディングできるようです。


リバースジオコーディングのシートは下のような状態とします。⇓


スタートボタンをクリックすると次のような状態になります。⇓


コードは以下です。⇓

//ジオコーディングする

function geoCoding() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('ジオコーディング');

  var address_column = 1; //住所がある列

  var latitude_column = 2; //緯度がある列

  var longitude_column = 3; //経度がある列

  var formated_address_column = 4; //フォーマットされた住所がある列

  var lastrow = sheet.getLastRow();

  var lastcol = sheet.getLastColumn();

  var values = sheet.getRange(1, 1, lastrow, lastcol).getValues();

  var geocoder = Maps.newGeocoder();

  geocoder.setLanguage('ja'); //日本の住所に設定

  

  for (var i=1; i < lastrow; i++) {

    var address = values[i][address_column-1]; //住所

    var latitude = values[i][latitude_column-1]; //緯度

    var longitude = values[i][longitude_column-1]; //経度

    

    //フォーマットされた住所

    var formated_address = values[i][formated_address_column-1];

    

    if (address === ''){

      continue;

    }else if(latitude !== '' && longitude !== '' && formated_address !== ''){

      continue;

    }

    

    var response = geocoder.geocode(address);

    var result = response.results[0];

    values.splice(i, 1, 

    [address, 

    result.geometry.location.lat, 

    result.geometry.location.lng, 

    result.formatted_address]);

  }

  sheet.getRange(1, 1, lastrow, lastcol).setValues(values);

}



//リバースジオコーディングする

function revereseGeoCoding() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('リバースジオコーディング');

  var latitude_column = 1; //緯度がある列

  var longitude_column = 2; //経度がある列

  var formated_address_column = 3; //フォーマットされた住所がある列

  var lastrow = sheet.getLastRow();

  var lastcol = sheet.getLastColumn();

  var values = sheet.getRange(1, 1, lastrow, lastcol).getValues();

  var geocoder = Maps.newGeocoder();

  geocoder.setLanguage('ja'); //日本の住所に設定

  

  for (var i=1; i < lastrow; i++) {

    var latitude = values[i][latitude_column-1]; //緯度

    var longitude = values[i][longitude_column-1]; //経度

    

    //フォーマットされた住所

    var formated_address = values[i][formated_address_column-1];

    

    if (latitude === '' || longitude === ''){

      continue;

    }else if(formated_address !== ''){

      continue;

    }

    

    var response = geocoder.reverseGeocode(latitude, longitude);

    var result = response.results[0];

    values.splice(i, 1, [latitude, longitude, result.formatted_address]);

  }

  sheet.getRange(1, 1, lastrow, lastcol).setValues(values);

}

作り方です。
①　Googleスプレッドシートを作成し、「ジオコーディング」と「リバースジオコーディング」と名前をつけてシートを作成
②　「ジオコーディング」シートの1行目のA列からD列を住所、緯度、経度、フォーマットされた住所とする
③　「リバースジオコーディング」シートの1行目のA列からC列を緯度、経度、フォーマットされた住所とする
④　「ツール」⇒「スクリプトエディタ」としてスクリプトエディタを開き、上のコードをコピペする
⑤　「ジオコーディング」シートで「挿入」⇒「図形描画」としてボタン図形を作成する（「リバースジオコーディング」シートでも同様）
⑥　作った図形を右クリック⇒右の３つの点をクリック⇒「スクリプトを割り当て」をクリック⇒「geoCoding」を割り当て（「リバースジオコーディング」シートの図形は「revereseGeoCoding」を割り当て
⑦　ジオコーディングするときは住所の列に値を入れて、ボタンをクリック
⑧　リバースジオコーディングするときは緯度、経度の列に値を入れて、ボタンをクリック

下はGoogle Apps ScriptのGeocoderクラスのリファレンスです。⇓
https://developers.google.com/apps-script/reference/maps/geocoder

UWSCでプログラム実行時の時刻が指定時間内か調べるプログラムです。⇓

//プログラム実行時の時刻が09:30:00から13:30:00なら「指定時間内です。」と表示

now = GetTime()

today = G_TIME_YY + G_TIME_MM2 + G_TIME_DD2

time1 = "09:30:00"

time2 = "13:30:00"

t1 = GetTime(0, today + time1)

t2 = GetTime(0, today + time2)

if t1 <= now and now <= t2 then

	print "指定時間内です。"

endif

-----------------------------------------------------------------------
上のコードで時間を前日の9:30:00から翌日の13:30:00としたい場合は下の部分を

t1 = GetTime(0, today + time1)

t2 = GetTime(0, today + time2)

次のようにしてください。⇓

t1 = GetTime(-1, today + time1)

t2 = GetTime(1, today + time2)

参考（UWSCのヘルプ）
//　日付、時間の取得（時間変数に時間を設定する）
戻値 = GETTIME( [±n日,　基準日] )　
引数
　±n日： nを指定すると当日もしくは基準日からプラスマイナス n日とする、小数点以下は時間（デフォルト=0）
　基準日： 指定された日付を基に日付を設定する （デフォルト=当日）
　　　　　 （"YYYYMMDD" or "YYYY/MM/DD" or "YYYY-MM-DD" or "YYYYMMDDHHNNSS" or "YYYY/MM/DD HH:NN:SS"）
戻値
　2000年１月１日からの秒数を返す

　値がセットされる特殊変数
　　G_TIME_YY 　　　// 年
　　G_TIME_MM　　　 // 月
　　G_TIME_DD　　　 // 日
　　G_TIME_HH　　 　// 時
　　G_TIME_NN　 　　// 分
　　G_TIME_SS　 　　// 秒
　　G_TIME_ZZ　　 　// ミリ秒
　　G_TIME_WW　　　 // 曜日 （0：日曜....6：土曜）
　　G_TIME_YY2　　　// 年をxxの文字型
　　G_TIME_MM2 　　 // 月をxxの文字型
　　G_TIME_DD2　　　// 日をxxの文字型
　　G_TIME_HH2　　　// 時をxxの文字型
　　G_TIME_NN2　　　// 分をxxの文字型
　　G_TIME_SS2　　　// 秒をxxの文字型
　　G_TIME_ZZ2　　　// ミリ秒をxxxの文字型
　　G_TIME_YY4　　　// 年をxxxxの文字型