こんにちは、おかちゃんせんせいです!
Power Automateネタで追加で共有したいことができたため、記事に書くことに決めました。
今回のテーマは
ExcelデータをGoogleスプレッドシートに転記する
です。
(追記:Power Automateに設定する式の誤り修正 2024/06/12)
目次
背景
今回の連携を考えたきっかけは、開発を依頼いただいた部署と他の部署での連携があまりにも非効率だと感じたから。
一方ではExcelで管理しているけれど、もう一方ではGoogleスプレッドシートで管理している。
でも、連携したいデータがあるとなりますと、手作業で地道にコピペするしかありません。
作業としてはとても簡単でも、簡単がゆえにイージーミス(転記ミス)が少なくないというパターンに陥っていました。
今までであれば、Googleスプレッドシート側のGASで公開してあるWebアプリをつくっておき、Excel VBAで情報の受け渡しをするのが一般的でした。
けれど、Webアプリを全体に公開するわけにもいかず、GASとVBAの両方の実装が必要になり属人化してしまう可能性があったため、この案は却下。
そこでパッと思いついたのが、Power Automateで連携してしまおうというアイディアでした。
今回の連携であれば、無料のMicrosoftアカウントでも実装が可能なので、導入ハードルはかなり低いはず。
Power Automateは基本ノーコードで実装が可能なツールなので、開発初心者の方でも取り組みやすいiPaaSの一つです。
かなりニッチな連携かもしれませんが、部分的にも参考になる箇所はあるかと思いますので、備忘も兼ねて記事に書くことにしました。
事前準備
事前準備として必要なことが4つあります。
- Microsoftアカウントを準備する(無料アカウントでもOK!)
- GoogleスプレッドシートでExcelデータを転記するための列を用意する
- ExcelデータはOneDriveに保存する
- Excelで取り込みたいデータをテーブル設定する
①②③については特に問題ないと思いますが、
初心者の方にとって意味不明なのが④。
そもそも、テーブルとはなんのでしょうか?
Excelには、表形式のデータを扱うための「テーブル」という機能があり、これを使うことでセルの書式設定や色分けなどを自動化できる。一般的な「表」を作成するなら、他にもセル範囲をテーブルに変換することで、便利な機能が利用できるようになる。
itmediaより引用
つまり、表形式での集計・管理作業を効率化するために活用されることが多いのが、テーブル機能なのです。
そして、ある特定の範囲をテーブル化することで、その場所(シート・セルの位置など)も特定できるため、この機能を使ってPower Automateでデータを取得します。
テーブルにはテーブル名を付けることができるため、テーブル名を付けておくと管理しやすくなるのでお勧めです。
表を範囲指定して、[挿入] メニューから「テーブル」をクリックする。


表にフィルターや、セルの色掛けが設定されればテーブル設定は完了です。

①テーブル内をクリック
②[テーブルデザイン]に移動する
③テーブル名を変更する
実際には、①を実行すると自動的に②になります。

操作手順


フロー名やフローを実行する頻度について設定し、『作成』をクリックします。

実行頻度について詳細設定をします。
時・分を指定でき、下図の例では毎日8:00に実行されるような設定になっています。

次に、『アクションの追加』で検索窓で「変数」と入力し、「コネクタでグループ化」のチェックを外し、「変数を初期化する」を選択します。

次に、パラメーターを設定します。
Name:変数名(任意の文字列)
Type:型(String)
Value:utcNow() ※詳細は後述

式の挿入から、入力欄に「utcNow()」を入力して、『Add』をクリックします。

下図のように表示されれば設定完了です。
また、アクションの名前を変えておくと、何の設定なのかをあとから判別しやすいようにしておくと良いです。

STEP.4で取得した現在時刻は世界標準(UTC)時間なので、場合によっては在住中の国の標準時間にタイムゾーンを変更する必要があります。
まずは、『アクションの追加』から検索窓で「Data time」と入力し、ランタイムを「組み込み」に変更し、「タイムゾーンの変換」を選択します。

各パラメーターには下記内容を選択します。
Base Time:STEP.4で作成した変数
Source Time Zone:(UTC) Coordinated Universal Time
Destination Time Zone:設定したい国の標準時間
Time Unit:Round-trip date/time pattern

『アクションの追加』で「変数を初期化する」を追加します。

次に、パラメーターを下記のように設定します。
Name:変数名(任意の文字列)
Type:型(String)
Value:addDays(body(‘タイム_ゾーンの変換’),-1,’yyyy-MM-dd’) ※詳細は後述

関数を入力するときには、直接パラメーター欄に入力するのではなく、下記入力欄に入力して、『Add』をクリックします。

次は、Excelデータを取得します。
『アクションの追加』で「Excel Online (OneDrive)」または「Excel Oneline (Business)」を選択します。
そして、表示されたリストから「表内に存在する行を一覧表示」をクリックします。
「Excel Online (OneDrive)」は個人用OneDriveに保存しているExcelを取得したい場合に選びます。
「Excel Online (Business)」は個人用ではないOneDrive(OneDrive for Business)に保存しているExcelを取得したい場合に選択します。
今回は個人用OneDriveに保存したデータに対しての処理のため、「Excel Online (OneDrive)」について書きますが、基本的な内容は同じです。

各パラメーターは下記の通り設定します。
ファイル:OneDrive内に保存したExcelファイル
テーブル:取得したいテーブル名
フィルタークエリ:日付 eq ‘前日日付’ ※詳細は後述
ここでまずポイントになるのが、「フィルタークエリ」です。
テーブルのデータをすべて取得して、すべて反映する場合には特に不要です。
しかし、条件を絞り込みたい場合に設定する必要があります。
今回はテーブル内の「日付」列が前日日付のもので絞り込みたいので、クエリ文字として「eq」を用いてクエリを設定します。
そして、次にポイントになるのが前日日付は動的なコンテンツとして設定する必要があります。
さらに、その動的コンテンツ「前日日付」をシングルクォーテーション(’)で囲むこともお忘れなく。

もしPower Automate上でMicrosoftアカウントの接続情報が登録されていない場合には、Microsoftアカウントへの接続を求められます。
その場合には、ダイアログで表示されている指示通りにサインインして登録すればOKです。



転記したい一覧は取得したので、あとはそのデータを行単位で繰り返し処理をするだけです。
『アクションの追加』にて検索窓で「コントロール」と入力し、ランタイムを「組み込み」として、コントロールに表示されている「さらに表示」をクリックします。
そして、リストの中から「それぞれに適用する」を選択します。


ここで設定するパラメーターには、前のステップで取得した「表内に存在する行を一覧表示」のbody/valueを選択します。

『アクションの追加』にて検索窓に「Google Sheet」と入力し、「行の挿入」を選択します。
各パラメーターは下記のように設定したら完了です。
ファイル:転記したいスプレッドシート名
ワークシート:転記したい表があるシート名
管理ID:「表内に存在する行を一覧表示」で取得した「管理ID」
日付:「表内に存在する行を一覧表示」で取得した「日付」
転記項目:「表内に存在する行を一覧表示」で取得した「転記項目」

最後に、Excelデータを更新します。
『アクションの追加』で「Excel Online (OneDrive)」または「Excel Oneline (Business)」を選択します。
そして、表示されたリストから「行の更新」をクリックします。
パラメーターは下記のように設定します。
ファイル:更新したいExcelデータ
テーブル:更新したいテーブル名
キー列:ユニークIDとして設定した「管理ID」
キー値:「表内に存在する行を一覧表示」で取得した「管理ID」
フラグ:〇 (転記したレコードについては、フラグ列に「〇」と付けるため)

以上の設定が完了いたしましたら、テスト実行してExcel・Googleスプレッドシートそれぞれが更新されていることを確認できたら完成です。


・事前準備として、ExcelデータをOneDriveに保存しておく。
・ExcelとGoogleスプレッドシートの連携には、ノーコードで実装できるPower Automateを活用する。
独学での習得を考えている方向け。
一つ目は、
侍エンジニア塾
です。
VBAやJavaScript関連でネット検索したことがある方であれば、一度は見かけたことある名前かと思います。
JavaやPython、C言語、VBA、JavaScriptなど、様々なプログラミング言語をオンラインで習得するためのサービスを提供しているところなので、自分が習得したい言語がもしありましたら、無料体験レッスンを受けてみると良いかもしれません。
そこでプロ講師に相談をして、習得するプログラミング言語に対して直接質問するのがベストです。

ストアカ

です。
こちらはあえてプログラミング学習専門ではなく、もっと手軽に学ぶ機会を得るサービスをチョイスしてみました。
VBAやJavaScriptなどのワンツーマンレッスンを開催している講師が多数登録しているので、まずは出費を抑えて学習したいと考えている方にお勧めです。

Value:addDays(body(‘タイム_ゾーンの変換’),-1,’yyyy-MM-dd’)
コピペだと「無効な式」ではじかれました。
↓
addDays(body(‘タイム_ゾーンの変換’),-1,’yyyy-MM-dd’)
アポストロフィー?の違いかわかりませんが、上記で設定できました。
まる様
ご指摘いただき誠にありがとうございます。
確認したところ、確かに「タイム_ゾーンの変換」の前にあるアポストロフィが誤っておりました。
修正完了済みです。