Excelで異なるカラム構成のCSVを2つ読み込んで、共通のキーとなる値でデータを結合する。
データベース的に異なるファイル同士の情報を結合したいケースってありますよね。
Excel馬鹿、って言葉が聞こえてこなくもないが、いろいろと課題満載のこのテーマ。
何とか解決したので、記録しておきたい。
※↓のファイルの各シートやコードを見ながらだと理解がスムーズだと思われる。
https://1drv.ms/u/s!AqGyTO4ulx7loiRR-K8FS5KiCriz?e=NX55sv
→サンプルデータとマクロファイルセットになってます。
まずは状況の整理から。
Excelを利用している人は、「自分が作成したファイルの保存されているパスやファイル名、またシート名を取得したい」ということがあるかもしれません。そこで、Excelの関数を使って簡単に取得する方法を紹介します。
以前こちらの記事で参考にしたソースコードでは一部うまく行かないケース（ps1スクリプトに与える引数が正しく渡せない）があったので動くように改善してみました。
Excel VBAからPowershellスクリプトを引数付きで実行する方法について解説します。関数化して、汎用性を持たせることで、複数のスクリプトを扱う場合やpowershell側の期待する引数の数が幾つの場合であっても簡単に実行できる様にしています。
Excel VBAは言わずもがな強力なツールであるが、ユーザへのアナウンスというかマニュアル作成というか、まぁ使ってもらうためのお膳立て面が面倒だったりする。
そこで、VBAで組んだ機能をワークシート関数化してしまえば、そのファイルの中ではワークシート関数（=sum(A1:A10)の様なのと同じ扱い）として使用可能になる。
場面により使える場合使えない場合があるが、VBAで作り込んだ機能の説明を0から行うよりは、簡単になる場面が多いかと思われる。
今回は引数とした任意のセル範囲の文字列に対するSHA256ハッシュ値を取得する関数をVBAで作成し、ワークシート関数として使用してみたい。
ちなみに標準のワークシート関数では現在のところ文字列のハッシュ値を取得は不可。
こんな状況がある。
従業員うん万人、
システムAのテーブル1にユーザIDと所属部署の情報、
システムBのテーブル2にユーザIDと居住都道府県の情報、
がそれぞれ格納されている。
システムA、Bともに外部へのデータ連携手段としてはCSVファイルのダウンロードのみ提供している。
私はシステムCの担当者でキーとなるユーザIDに所属部署と、居住都道府県両方の情報を紐づけてシステムCへインポートしたい。
色々とやり方はあるが、予算もないのでExcelで何とかする。
ワークシートに関数を入力してVlookupやらMatch＋IndexやらやればVBAを使わずとも実現できるのだが、現実の場面ではシステムA,B共に他にも結合したいカラムが多数あってワークシート上に関数を大量に入力した神エクセルでは重くなって破綻するのが怖い。
データベースに取り込んじゃってjoinするってのもありだが、将来誰かに引き継ぐ事を考えると、裾野が広いExcelにしておきたい。
こんな場面をVBAでどう乗り切るかのアイデア。
Servicenowのインポート機能は強力なのだが、時に躓くことがある。
この例も実際自分が躓いた例。Type がReferenceになっていて同名レコードが元テーブル（参照先テーブル）に存在するフィールドの正しいインポート方法。
例えばであるがsys_userに「山田 太郎」という名前の人物が2名いる。
例えばこんなケース。
yamada.taro1とyamada.taro2は「User ID」や「Sys_ID」では区別ができるので、sys_userテーブルに対するインポートや更新では特に問題になる事はない。
問題になるのはこの同名のエントリをルックアップしているテーブルのインポートを行うときである。
例えばグループとユーザの関係性を定義している、sys_user_grmemberテーブルを見ると次のようになっている。
Help Deskグループに山田 太郎が二人とも所属している場合こうなる。
インポート機能を使った事がある方はお分かりと思うが、このようなデータをインポートしようとして、テーブルをエクスポートしたり、更新インポート用のエクセルフォーマットを出力したとしても、次のようなフィールドが得られるだけである。
要するにこのままでは、「山田 太郎」がyamada.taro1とyamada.taro2のいずれなのかを区別して扱う事が出来ない。
当然ながらこのようなデータをインポートしても、１と２どちらの山田太郎が選択されたデータが出来上がるかが不明である（ちゃんと検証していないがSys_IDが若い方が勝手に選ばれていた様な気がする）。
これでは困るので、SYS IDなりユーザIDなり一意の情報を指定してインポートしたいわけだが、User列にyamada.taro1のIDなりSYS_IDなりを記載してインポートしてもエラーになるだけなのである。
色々と検索してみても（どこかにはあるのかもしれないが）なかなかこれを解決するためのドキュメントが見つからず困った。
Excel VBAから引数を渡す.ps1スクリプトを呼び出してExcelマクロで全体の処理をハンドリングするための材料調査。
という事でそこそこ簡単に実現できそうだ。
調べた内容は次の二点。
やりたい事①：VBAからのPS1スクリプト呼び出し。
やりたい事②：PS1スクリプトで引数を受けるお作法
ちょっとwordpressで遊ぶためのサーバを構築しようとして詰まった。
$ sudo vi sample.txt
これが通らない。デフォルトのCLIでのテキストエディタといえばviな世代なのだが。。。
Servicenowのいろんなアプリの記事（インシデントチケットでもナレッジ記事でも）にはおおむね添付ファイルが付けられる。
一般的なファイル形式であればServicenow上で全文検索も可能で便利ではあるがServicenowの外で添付ファイルを扱いたいケースもある。そんな場合は添付ファイルを探してダウンロードという作業を繰り返す必要がある。
API経由でごそっとダウンロードできればそんな面倒が解放されるはずなのでPowershellで試してみる。
ちょっとセキュリティ的には良く無さそうだが、↓のURL（[]内は書き換え）をブックマークしておくとローカルアカウントであれば自動ログインできる。
https://[YourInstanceName].service-now.com/login.do?user_name=[UserName]&sys_action=sysverb_login&user_password=[PassWord]
会社のアカウントとか重要な情報入りのアカウントとかではpassword直書きのURLになってしまうので、セキュリティ的にNGだろうが、developerインスタンスのブックマークとかはこれにしとくと便利かも。ちなみに[PassWord]の部分は普通に平文でOK。
チケットを複数の人にアサインしたい。
個人的には「誰がこのチケットに責任を持つのか？」が不明確になるので辞めた方が良いと思うが、曖昧を好む日本企業ではありがちと言えばありがちな要望かもしれない。
標準では出来ない？と思っていたが、よくよく見てみたらデフォルトで「Additional assignee list」というフィールドが表示されてはいないものの、用意されているではないか。
これを表示して、どう動くのかを確認してみる。
.batや.vbsファイルの場合、ダブルクリックすることでスクリプトを簡単に起動することができますが、.ps1ファイルの場合、標準ではダブルクリックで起動することができません。この記事では、PowerShellでのスクリプト起動方法について紹介します。
さて前回に引き続きServicenowにインターネットに口のないURLからデータをダウンロードしてPCで中継してSNOWのテーブルへ更新インポートする様な仕組みを作らねばならなくなった（執筆時点では出来るかどうか不明）、という状況。
前回データ提供元のInternal環境のURLからファイルをダウンロードする事には成功したので、今回はServicenowからマージ（上書き）対象のデータをゲットすることとしたい。
インポート→更新、からダウンロードできるSysid付きのエクセルが落とせればベストなのだが、残念ながら現状方法が見つけられなかった。
が、一応代替手段っぽいことは出来たのでそれで行く。言語はPowerShellで続行。
ServiceNowのApiを不幸にしてたたかなければ成らなくなったときに強い味方になるツールが実装されている。REST API Explorer。そのまんま。
ひょんなことから限られたリソース・期間でServicenowにインターネットに口のないURLからデータをダウンロードしてPCで中継してSNOWのテーブルへ更新インポートする様な仕組みを作らねばならなくなった（執筆時点では出来るかどうか不明）。
（きっかけはServicenowだが本記事では基本的にWindowsのPowerShellでネットワーク上のファイルをタスクスケジューラを使って定期的に自動取得する仕組みを検討している）
まず今回は元ネタになるファイルのダウンロードの仕組みから検討してみる。
制約条件はWindows10を使用し追加のソフトウェア購入予算無し。
必然的に選択肢は.batか.ps1か.vbsあたりが想定される。
.batでもよいが、将来単なるダウンロードではなく、ログインやら条件分岐やら複雑な事をやろうと思ったときに、スクリプトが困難になる恐れあり、VBSは悪く無いが、経験上今一安定的に動かす用途には難しそう、かつMSさんはvbsを止めたがっている疑惑が個人的に止まらない。
という事でコマンドが直感的に打ちにくくて好きではなくこれまで敬遠してたが、PowerShellでやってみることにする。
最近パワポばっかり作ってる。多分この先もいろいろ作る事になると思うのでよく使うパターンをコピペできるようにしておきたい。と思って作ってみた。いろいろパターンを充実＆習得して、効率的に資料作成出来るようになりたいものだ。
ServiceNowの管理者として避けて通れないのがアカウント管理。AD連携等でうまい事グループや権限まで含めた自動連係が綺麗に作りこめればいいが、恐らくそこまで環境の整っている会社もそれほどないのではなかろうか。
比較的新しめの機能であるフローデザイナーを活用して、アカウント発行時の処理を一部自動化してみたい。
世の中理不尽な事はあるもので、個人的にはチャットボット否定派なのだが、なんだか周りが盛り上がってチャットボットの導入検証みたいな事をやる、みたいなシチュエーションもなくはないと思う。
個人的な持論ではユーザの立場で使用しているシステムの問い合わせ窓口にチャットボットがあって良かったと思った事は今まで一度たりとも無く、正直チャットボットに何か質問を入力したところですでに調べて使えなかった情報や的外れな回答しか返ってこず、オペレータにつなぐまでに無駄な手間を掛けさせられる事しかないので大嫌いである。
単なるボットであればもちろんのこと、AI機能がどうたらこうたらという類のサービスでも正直2021年現在ユーザとしての私には到底利便性が向上するようなものではなく、ストレスの現況でしかないからだ。
まぁAIチャットボットとやらが何とか、ある程度のリテラシのあるユーザにも役立つと感じられるレベルに到達するには早くてもあと10年くらいかかるんじゃなかろうか、というのが個人的な感触ではある。
とはいえサラリーマンはそんなことばかり愚痴っていても仕方ないので、とりあえず使えるようにしてみることにする。
ServiceNowのデフォルトのアプリケーションでは基本的にあまり必須項目は設定されていない。またテーブルの通常の定義ではカラム毎に必須項目を設定する事になる。
つまり最初の保存時に必須項目を設定できる。
ただ実際の使用シチュエーションではステータスに応じて必須項目を設定したいケースがある。
ケースのクローズの承認をエライ人がやる場合に、メンバーが空欄ばかりで承認依頼を出してくる様なケースがあると、起票時は必須でもよいが、クローズ前には最低限すべて埋めて出させるくらいの事はツール側で設定できないのか？という様なわがままな要望が出てくるケースは多い。
偉い人＝発言力もあり要望に応じざるを得ないケースも多い。これを実現するためにはステータスに応じて動的に必須項目を設定することが必要になる。
