2021-05-25

【PowerQuery】CSVの空白行に残ったカンマを削除する

Excel PowerQuery

f:id:twixoreo:20210525215904p:plain — サムネイル

もう題名に答え乗っちゃってますが、
Slackにこんな質問があってPowerQueryで秒で解決したのでメモ。
f:id:twixoreo:20210525214241p:plain

IF式で空欄っぽく見せていても、データ的には数式が入っており空白行になってしまっているので
CSVにしたときに区切り文字が残ってしまいます。
f:id:twixoreo:20210525214531p:plain

▼解決方法は以下の通りです。

①CSVにしたい表をテーブルにする

テーブルのやり方はこちら
表をテーブルにすることでいろいろなメリットがあります。
hamachan.info

②テーブルをPowerQueryに取り込み

f:id:twixoreo:20210525214954p:plain

③ホーム＞行の削除＞空白行の削除

f:id:twixoreo:20210525215131p:plain

④③をテーブルとして出力

f:id:twixoreo:20210525215228p:plain

⑤CSVにする

できた！

2021-05-24

【GAS】DeepLAPIを使って英文ブログを翻訳する(後半)

GAS しごと

f:id:twixoreo:20210524235505p:plain — サムネイル

▼前回のおさらい
azumikan.hatenablog.com

いよいよGASで差分をとって、
ブログの英文をDeepLで翻訳します。

今回のスクリプトの流れ

1.スプレッドシートで更新を検知したらブログの文章を取り込む
↓
2.雑に1000文字ずつに区切って、バンバンAPIになげる
↓
3.返ってきた日本語を配列に格納して成形
↓
4.日本語をメールに流す

1.スプレッドシートで更新を検知したらブログの文章を取り込む

英語のブログ…MSの公式のブログを今回はとってくるのですが、
RSSからIMPORTFEED関数を使って
文章まで一気にとってきます。

▼取り方はこんな感じ。
更新があった際にGASを動かすようにします。
azumikan.hatenablog.com

トリガーは日次で発火します。

2.雑に1000文字ずつに区切って、バンバンAPIになげる

※API利用で注意すること
①APIの一度のリクエストは30kbまでなので、たくさん投げすぎるとエラーで翻訳できません。
②DeepLには一文ずつも投げられますが、一文ずつの場合1リクエストで50文まで。
③urlfetchappを使うのですが、連続でアクセスしすぎると、たまに止まるらしい。(ドキュメント記載なし)

最初一文ずつ投げないのかと行けないと思ったら雑に改行コードつきでも長文を投げられることが判明。
しかし、一気にAPIに放り込んで全部訳そうとしたら、キャパオーバーなのか死んだので
1000文字ずつくらいに区切るといい感じになります。
区切る際は良い感じの区切り部分(句読点、改行、感嘆符など)で区切るとさらにいい感じです。
ちなみに↓のコードはリクエストのエラー処理していない処理ですが、そこは許して…。

3.返ってきた日本語を配列に格納して成形

リクエストのレスポンスはJSON形式なので、JSONの中身を取り出して配列に格納します。

4.日本語をメールに流す

完成！
そしてDeepLから返ってきた日本語、長文は途中で文書が抜けたり、ダブったり
日本語はそれっぽいけど内容が怪しかったりもする…。
ので、完璧なに和訳ではないですが、ざっくり概要把握にはいいかも。

ところどころハードコーディングですが、
完成したコードがこちら：

// シートの新旧で更新を比較
// トリガーに設定するのはこの関数
// C1にFALSEの件数を反映させます。
function copySheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
 
  const sheet_new = ss.getSheetByName('new'); 
  const sheet_old = ss.getSheetByName('old'); 
 
  const lastRow = sheet_new.getLastRow(); 
  const lastColumn = sheet_new.getLastColumn(); 
 
  const copyValue = sheet_new.getRange(1,1,lastRow,lastColumn).getValues(); 
  sheet_old.getRange(1,1,lastRow,lastColumn).setValues(copyValue); 

  const data =  sheet_old.getDataRange().getValues();

// C1がFALSEだったら更新通知
  if (data[0][2] > 0){
    console.log("FALSEがあります");
    sendMail(data);
  }

}

// メールを送るスクリプト
// 引数はシートのセル情報
function sendMail(dt) {

  const strTo = "example@example.com";
  const strSubject = "【自動送信】英語のブログに更新がありました";
// シートからとってきたブログURLやら文書やらを格納※ハードコーディングですみません
  const text = dt[5][5];
  const translation = splitTxt(text);
  const strBody = 
    "▼ブログURL：" +　dt[5][3]　+ "\n"
    + "▼DeepL翻訳：" + "https://www.deepl.com/ja/translator"+ "\n"
    + "▼記事名：" + dt[5][1] + "\n" 
    +  translation;

  /* メールを送信 */
  GmailApp.sendEmail(
    strTo,
    strSubject,
    strBody
  ); 
}


// 英文を分割してAPIに送り出すところまでの関数
function splitTxt(str){

// パラグラフをセンテンスに分割
const array = str.replace(/(\.+|\:|\!|\?)(\"*|\'*|\)*|}*|]*)(\s|\n|\r|\r\n)/gm, "$1$2|").split("|");
// APIに投げる文字列を入れる配列
let apiArr = [];
let countArr = [];
let text = '';

for(let i = 0; i <= array.length; i++){
        // 1000字までは配列に格納
    if(text.length < 1000){
        text = text + array[i];
    }else{
        // 1000字超えたら
        apiArr.push(text);
        countArr.push(i);
        text = '';
    }
}
    text = '';
    // console.log(count[0]);
    // console.log(count.length);

for(let i = countArr[countArr.length-1]; i <= array.length; i++){
    text = text + array[i];
}
    apiArr.push(text);
    return DeepLTool(apiArr);

}


// APIに送り出して日本語にする関数
// 引数はブログの英語本文
function DeepLTool(transArr) {

  console.log(transArr);
  const DeepLURL = 'https://api-free.deepl.com/v2/translate?auth_key=';
  const authkey = 'APIのトークン';
  let transresult = [];
  let fetchUrl = '';
  let response = '';
  let results  = '';

  for (const element of transArr) {
    // 必要に応じて設定変えるともっとましなのかもしれない…
    fetchUrl = DeepLURL + authkey + '&text=' + encodeURI(element) +'&target_lang=ja&source_lang=en&preserve_formatting=1';
    response = UrlFetchApp.fetch(fetchUrl);
    results = JSON.parse(response.getContentText());
    transresult.push(transJSON(results));
  }

  return transresult.join('');
  // return transJSON(results);
}

// 受け取ったJSONをテキスト化
function transJSON(JSONdt){
  let results  = JSON.parse(JSON.stringify(JSONdt));
  let trans = results.translations[0].text;
  return trans
}

2021-05-15

【GAS】DeepLAPIを使って英文ブログを翻訳する(前編)

GAS しごと

仕事で英語のドキュメントを読んだり、
英語のブログをたびたびチェックしなければならなくなりました😿
全然英語読みたくない…全然英語のブログチェックしたくない…
全部自動で通知して自動で翻訳してくれたらな…ということで、、
怠惰な自分のためにスクリプトを書いたのでメモ。

まず自動で翻訳…どうやればいいのか…以下選択肢があるようでした。

▼DeepL…なんかかっこいいし使ってみるか…。
以前からめちゃ精度が高い！と巷で話題だったやつ。
ほーんAPIあるのか。やってみるか…ということでメールアドレス登録。

無料版でもクレジットカードの登録が必要となります。※プリペイド可
「無料版でクレカ登録必要だけど課金しないから安心してね」のポップアップあり。
登録するとTOKENが発行されます。

▼DeepLドキュメント
www.deepl.com
結構親切に書いてあります。
・一度のリクエストは30kbまで
・リクエストを送るとJSON形式で帰ってくる
・リクエストはUTF-8で送る
・テキストだけでなくドキュメント(パワポかワード)の翻訳も可能
・左のメニューの下の「Simulator」を押すとどのパラメーターで送ればいいのかシミュレーションできます。

Simulatorはログイン状態であればトークンつきになっているのでそのまま参考にできる。

f:id:twixoreo:20210515120929p:plain

後編へ続く…。

2021-05-13

【GAS】MSのドキュメントを簡単スクレイピングして差分があったら更新メールを送る

GAS しごと

スプレッドシートのIMPORTHTML関数とGASで更新を検知できたので共有。
※一応MSドキュメントの利用規約みましたが禁則事項にスクレイピングについて記載はありませんでした。
　サイトによってはNGですので気を付けてください。

▼みなきゃいけない対象サイト

docs.microsoft.com

▼やりたいこと

新しい分野だからか、この用語集がいつの間にか更新されることがあるので
変更タイミングがあったらメール通知。あわよくば差分も取りたい。

▼やったこと

最初はhtmlparse的なライブラリを使おうと思っていたのですが、
スプレッドシート関数のIMPORTHTML関数で簡単に取得できました。

▼スプレッドシートの用意

newシートにIMPORTANTHTML関数を図中①のように入力
もとの用語集が表形式になっていたのでIMPORTHTML関数でtableを取得でいとも簡単にいけました。

↓

表がとれたらnewシートを複製してoldシートと名付け、値貼りしておく

↓

newのほうの表の横(今思うと表の列が伸びる可能性もあるので、表の左に書いておけばよかった)に
newとoldの文言の差分を抽出できるようにEXACTとCONCATENATE関数を合わせたものを入れておく(図中②)
=EXACT(CONCATENATE(A1,B1),CONCATENATE(old!A1,old!B1))

↓

COUNTIF関数でnewのTRUE/FALSEの値の数をカウント(図中③)

f:id:twixoreo:20210513001253p:plain

▼スクリプトの流れ

日次でnewからoldへ表をコピーして、
表に差分＝F2セルにFALSEが1つ以上あった場合は
アーカイブとして日付のシートを作成してメールを送る。

▼スクリプトの用意

  /* コピーして差分を確認する関数 */
function copySheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
 
  const sheet_new = ss.getSheetByName('new'); 
  const sheet_old = ss.getSheetByName('old'); 
 
  const lastRow = sheet_new.getLastRow();
  const lastColumn = sheet_new.getLastColumn();
 
//　newからoldへコピペ
  const copyValue = sheet_new.getRange(1,1,lastRow,lastColumn).getValues(); 
  sheet_old.getRange(1,1,lastRow,lastColumn).setValues(copyValue); 

//　FALSEがあったら日付のシートを作成してアーカイブ作成。メール送信。
  if (sheet_old.getRange("F2").getValue() > 0){
    console.log("FALSEがあります");
    const sheet_copy = sheet_old.copyTo(ss);
    const yyyymmdd = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyyMMdd');
    sheet_copy.setName(yyyymmdd);
    sendMail();
  }
}


  /* メールを送信する関数 */
function sendMail() {
let strTo = PropertiesService.getScriptProperties().getProperty("EMAIL_ADDRESS");
let strSubject = "【自動送信】Workplace Analytics 用語集に更新がありました";
let strBody = PropertiesService.getScriptProperties().getProperty("SS_ADDRESS");
    + "\n\n▼元用語集URL\n" 
    + "https://docs.microsoft.com/ja-jp/workplace-analytics/use/glossary";

  GmailApp.sendEmail(
    strTo,
    strSubject,
    strBody
  ); 
}

余談ですがPowerQueryの表取得の機能でも簡単に表を取得できました🎉

2021-05-06

【PDF化】文字をコピーしたときにおかしくならないPDF化の設定

しょっちゅうPDFにしているのに知らなかったのでメモ。

たまにPDFから文字列をコピペしたときに、
文字化けしたり、□□□□になったり、繰り返し同じ文字列がコピーされたりしていたのですが
パワポ等をPDF化して保存するときに

オプション＞デフォルトで「アクセシビリティ用のドキュメント構造タグ」のチェックを外すとうまくいきました。
画像の名前とかも消すために「ドキュメントのプロパティ」も併せてチェックを外したほうがよさそうですね。

f:id:twixoreo:20210506232551p:plain

f:id:twixoreo:20210506232728p:plain

おわり

2021-05-04

【Discord.js】メンバーの音声チャンネルの入退出を検知する

5/6追記：立ち上げていきなり音声チャンネルに入った場合streaming以外はnull　streamingはfalseになるため注意が必要です。

めっちゃ悩んだ結果、無理やり解決させたのでメモ。

表題の件、ググるとvoiceStateUpdateイベントで監視するもろもろがでてくるのですが、
入退出だけでなく、ミュート等音声まわりをいじるだけで検知してしまうので悩んでおりました。(v11より前の書き方だと検知できたようですが…。)
IDつきの入退出のブール値や、メンバーの接続状態などとくにオブジェクト内に表記がないため簡単には出せないという罠。

▼同じように悩んでいた人々

node.js - discord.js event voiceStateUpdate - Stack Overflow

bots - problem with voiceStateUpdate in JavaScript - Stack Overflow

bots - Discord.js join/leave voice channel notification in text channel - Stack Overflow

▼解決法

ミュートや音声まわりで反応しちゃうなら、条件からひけばいいじゃん、ということで
無理やり条件から引いて解決しました。

いまのところ以下条件に反応するようです(oldState/newStateの中身の最後のパート)
5/6追記：立ち上げていきなり音声チャンネルに入った場合streaming以外はnull　streamingはfalseになるため注意が必要です。
プロパティ　oldState/newState
serverDeaf null false
serverMute null false
selfDeaf null false
selfMute null true
selfVideo null false
streaming false false

client.on("voiceStateUpdate", function(oldState, newState) {

  // ミュートでも反応してしまうので無視用
  const statusChk =
    oldState.serverDeaf === newState.serverDeaf &&
    oldState.serverMute === newState.serverMute &&
    oldState.selfDeaf === newState.selfDeaf &&
    oldState.selfMute === newState.selfMute &&
    oldState.selfVideo === newState.selfVideo &&
    oldState.streaming === newState.streaming;

  if((statusChk == true || oldState.serverDeaf == null) && newState.channel){
  //チャンネルに入ってきたときの処理
    }
  } else if (statusChk && oldState.channel) {
  // チャンネルから出たときの処理
  }
});

あー今思えばoldState/newStateとでトリガー引いた人のチャンネルの人数の変化をみるのでもよかったのかもしれない。
teratail.com

おわり。

2021-05-02

2021年4月に読んだ本

読書

今月はあんまり読めなかった…。
KindleUnlimitedは図書館に行かなくても、気軽に借りられる・途中でポイできるのでとてもよき。

4月の読書メーター
読んだ本の数：9
読んだページ数：1602
ナイス数：40

売上が上がるバックオフィス最適化マップーーテレワーク・コスト減・利益増・DXを一気に実現する経営戦略の感想
IT化ができていない企業向け。重要なのは責任者が現場を理解していること。現場で何かしらのITツールを勝手に使っていることがあるので、トップダウンで新ツールを導入する前に現場を把握する必要がある。またシステムを取り入れて満足するのではなく真のゴールは現場で新システムが問題なく運用できるようになること。バックオフィス業務(人事・総務・経理・CRMなど)SaaSサービスの紹介がメインなので、特に新鮮味はなかった。
読了日：04月29日著者：本間卓哉
 手ぶらで生きる。見栄と財布を捨てて、自由になる50の方法 (サンクチュアリ出版)の感想
感想投稿し忘れてた。カフェで読了。この人はかなり過激派で布団も所有しないらしい。生活の方向性・大切にしていきたいこともはっきりしていて極端ながらも面白い。
読了日：04月29日著者：ミニマリストしぶ
 月10万円でより豊かに暮らすミニマリスト生活の感想
前半はモノの片づけ方・手放し方。後半はかなり自己啓発的な内容(好きなこと・夢・縁・幸せの感じ方・GIVERとかそういう感じ。)。片づけ方の部分は基準が明確でとても参考になった⇒「片付けをしなくてもいい部屋」にしてすぐにやりたいことにとりかかれる環境を常に作っておくこと。片づけるときは一番簡単なものから取り組むこと。以下のものは残す①お金を生み出してくれるもの②時間を生み出してくれるもの③ポジティブな気分にしてくれるもの。耳が痛い⇒「本や教材は、今まで自分が思い描いてきた夢の数だけ増えていきます。」
読了日：04月29日著者：ミニマリスト Takeru
英国アフタヌーンティー&お菓子 (講談社のお料理BOOK)の感想
バターの暴力！！でもどれもめちゃくちゃおいしそう。いつか湯河原のお店にいってみたくなった。紅茶とスコーンのセットのことをクリームティーと呼ぶのは初めて知りました。
読了日：04月29日著者：小関由美,小澤祐子
 Power Query基本操作ブックの感想
KindleUnlimited本。例に沿って操作手順は教えてくれるので、とりあえず触ってみたい人にはいいかも。基本機能の説明が絞られていて少ないので、物足りなくかんじるかも。
読了日：04月29日著者：サトウヨシヒロ
 わかりやすい！現場で使えるパワークエリ: マクロより全然簡単！専門分野でなくても業務で必ず役に立つ！エクセル整備の感想
良書。パワークエリのリボンの基本的な説明あり(わかりにくい日本語のメニュー文の解説あり)。何パターンか結合されたセルの表、その他よくありがちな表の成形方法についてもわかりやすく注意書き付きで解説されている。巻末にはエラー対処や筆者の実体験に基づいたTipsが詰め込まれていてとてもよかった。初心者にはいい内容だと思う。
読了日：04月29日著者：データセイバー
 「辞める人・ぶら下がる人・潰れる人」さて、どうする？の感想
読み物として面白かった。組織の活性化にはマイナス感情への対処と社員の心に興味を持つことが大事。分析する立場になったので、まずは組織や個人がどんな状態になっているか、マイナス感情がたまっている部分はどこなのか可視化していくことが大事だと再認識。手元にデータはあるものの、どのように分析していけばいいのかわからなかったのでマトリクス指標や分類分けがとても参考になった。しっかりとターゲティングを行って誰のどんな課題にどのようにアプローチしたいのか明確にして分析・施策を打っていきたい。
読了日：04月27日著者：上村紀夫
 AI分析でわかったトップ5％社員の習慣の感想
どこに行っても平積みされてるので読了。顕著な成果を出したハイパフォーマーをモニターして分析した結果の行動パターンが紹介されている。個人的にはこれをどういう風に分析したのかが気になったので分析編の書籍もぜひ出してほしい。書かれている行動パターンはビジネス本にありがちな内容ではあるが、自分に当てはめるとできていないことが多い。。他部署とのコラボ状況など、最近Microsoft Analysis で見れるようになったが、サンクスカードや他部署跨いだランチの交流状況などもみられるようになったら面白そう。
読了日：04月25日著者：越川慎司
 コンサル一年目が学ぶことの感想
 自己啓発本。タイトルにコンサルと書いてあるけど、社会人一年目の社員にお勧めしたい。仕事の基本がまとまっている。「ロジックツリーなどで論点を整理・分解し、それぞれの論点について数値分析を行い、最後に項目ごとの重みづけをして、大事なものをアクションに落とす。」これはできていないので見直したい。。。一番最後に書かれているチームワークの部分は新人の時に知っておきたかった⇒「簡単なことでも自分の能力で貢献できる分野を考えて行動を起こすこと。」
読了日：04月24日著者：大石哲之

 読書メーター