エクセルやスプレッドシートの日程表の土日に色を付ける

いつもなんとなくやっていたので、よくわからないままだった。特に条件式の入れ方がわからなかったのでここでまとめておく。

日程表の土日に色を付けることである。

まず、

1. 色を付けたい日程表の範囲を選択して、
2. 条件式を入れて、
3. 色などの書式をつける　である。

例えば、

A2:Z10の範囲で色を付けたいとする

1. 列方向に曜日が並んでいて縦に色を付けたい場合
   1. 条件式は行を変えずに設定すればいいから
      1. =weekday(a$2)=0とか行に＄がつく
2. 行方向に曜日が並んでいて横に色を付けたい場合
   1. 条件式は列を変えずに設定すればいいから
      1. =weekday($a2)=0とか列に＄がつく

でこのあと書式をつける。

なんとなく＄の付け方が理解できた。

Chromebookにマカフィーのポップアップが続けて出る

児童のChromebookにマカフィーのポップアップが出るようになった、ということで学校に様子を見に行く。マカフィーなので、ウイルスに感染したなんて学校では言っていた。
クッキーか何かを消せばどうにかなるかと思っていたが、もっと簡単だった。
児童のIDでログインしていたCromebookからログオフして、その児童のアカウントを削除すると、これだけでマカフィーのポップアップは消えた。たくさんあったマカフィーの履歴までなくなった。
Chromebookは簡単である。Windowsではこうはいかない。

GASでユーザーリストを取得してみた

前に、Google Workspace for Educationの年度更新について、Classroomのことが問題だと書いた。Classroomのオーナーでないと削除ができないのだけれど、誰がオーナーになっているのかわからない。だいたい、ドメインの中にどんなClassroomがあるのかだって管理者でもわからない。ということで、GASを使ってそこがわかるようにしてみた。

ClassroomのAPIからすぐに取得できるのが、オーナーとなっているユーザーIDでこれが２１桁？の固有の数値となっている。EmailアドレスのIDではないのだ。これを、ユーザーのデータから取得しないと、だれなのかわからない。調べるとやっている人がいるので、参考に作ってみた。ユーザーを作るときに、depertmentに学校名を入れてあったので、それをorganizationsから引っ張ってくるところが結構難しかった。

参考にしたのはここ

josys-diary.com

作ったGASのコードは以下

/**
 * Lists all the users in a domain sorted by first name.
 * 参考は　https://developers.google.com/apps-script/advanced/admin-sdk-directory
 * よくわからなかったorganizationsのところは
 * https://josys-diary.com/cloud/g_suite/707/
 */
function listAllUsers() {
  const sheet = SpreadsheetApp.getActiveSheet();//シートに書き込むための準備
  var values = new Array();//取得したデータを入れる２次元配列の準備
  var data = [];//一つの配列

  var pageToken;
  var page;
  var j = 0;
  do {
    page = AdminDirectory.Users.list({
      domain: 'ここは、自分のところのドメイン',
      orderBy: 'email',
      maxResults: 500,//MAXが５００
      pageToken: pageToken
    });
    var users = page.users;
    if (users) {
      for (var i = 0; i < users.length; i++) {
        var user = users[i];
        if (user.primaryEmail.indexOf("s2") == -1) {//Emailのはじめがs2が児童生徒にしてあるので、それ以外の先生たち。来年はs3も出てくる
          if(user.organizations) {
            for (var m = 0; m < user.organizations.length ; m++ ) {
              if (user.organizations[m].primary == true) {
                var department = user.organizations[m].department;//ここが学校名が入っているところ
              }
            }
          } else {
            var department = "なし"
          }
          Logger.log('%s %s %s (%s) %s %s', j, user.id, user.name.fullName, user.orgUnitPath, user.primaryEmail, department);
          //user.orgUnitPath　入っているOU
          data = [j, user.id, user.name.fullName, user.orgUnitPath, user.primaryEmail, department];
          values.push(data);//２次元配列に入れる

        j++;
        }
      }
    } else {
      Logger.log('No users found.');
    }
    pageToken = page.nextPageToken;
  } while (pageToken);
  //values.shift();
  Logger.log(values);
  sheet.getRange(1,1,values.length,values[5].length).setValues(values);//とりあえず配列５番目のLengthを列数
}

これで、スプレッドシートに

通し番号、ユーザーID、ユーザー氏名、OU、Email（ID）、学校名の一覧表ができる。

自分のところでは、管理コンソールから、ユーザーを作成するときに、「Employee Title」、「Department」、「Cost Center」を使うことができ、これを使ってフィルタを使って絞り込むことができたりしたので、ここを使っている。Departmentには、学校名を入れて使っている。なので、そこを使いたかった。

これで、Classroomの情報と合わせれば、どの学校の誰がオーナーになってClassroomを作ったのかがわかるようになった。このあと、学校にスプレッドシートを渡して、削除し忘れがチェックしてもらう予定だ。

GAS(javascript) 配列ArrayオブジェクトのforEachメソッド

GIGA School の１年目の終わりが近づいている。でもその前に年度更新の作業が待っている。昨年は、先生だけしかつかてなかったので先生のIDを変更するくらいの年度更新だけだったが、今年は本格的に使ったのでたくさんある。

その中でも、Classroomの年度更新に結構気を使う。管理コンソールからはClassroomの数くらいしかわからず、先生たちが作ったClassroomは削除もアーカイブもできない。クラスルームに教師で入っていれば、アーカイブはできる。さらにオーナーであれば、削除ができる。教師で入っていても、オーナーでなければ削除はできない。

もし、クラスルームがそのままになっていると、生徒がストリームに勝手に書き込んで、先生がいないと何を書いているかわからない状態になってしまう。オーナーが削除を原則にする必要がある。ここで問題は、クラスルームのオーナーが誰であるか、管理コンソールからはわからないことである。そこで、GASを使ってその学校のクラスルームのオーナーが誰であるか、教頭先生に知らせて、対応をしてもらうためにツールを作ることにした。

やってみると、Arrayオブジェクトの使い方が勉強になった。以下に書く。

GAS(javascript) のコード

function myFunction1() {
  /*
  配列ArrayオブジェクトのforEachメソッド

【参考】詳解！GoogleAppsScript完全入門［第３版］P.280
   */
  const array = ['AA','BB','CC'];
   array.forEach*1;
   //　バックティック（`）の入力はShift+@でできる
//引数の順番が対応する。（値、インデックス（省略可）、もとの配列）
/*
情報  0: Hello AA!,AA,BB,CC
情報  1: Hello BB!,AA,BB,CC
情報  2: Hello CC!,AA,BB,CC
*/
}
function myFunction2() {
/*
2次元配列で
検索をする方法
【参考】https://qiita.com/gen0x39/items/3ed11cf41d1b571283e7
*/
  var hoge = [
    [1,2,3],
    [4,5,6],
    [7,8,9]
  ];
  hoge.forEach((row,i,moto1) => {
    row.forEach((element,j,moto2) => {
      if(element === 6){
        console.log("i:",i,"j:",j,"element:",element,"moto1:",moto1,"moto2:",moto2);
      }
    })
  })
/*
情報  i: 1 j: 2 element: 6 moto1: [ [ 1, 2, 3 ], [ 4, 5, 6 ], [ 7, 8, 9 ] ] moto2: [ 4, 5, 6 ]
*/
}

スプレッドシートにデータを書き出すのがやりやすい。ただ、GASの制限で６分以内がある。APIをその都度呼んでいると結構時間がかかる。配列に読み込んでそこで処理をしたほうが速い。そうするとArrayオブジェクトがうまく使えるといいことがわかった。

*1:name,index,moto) => console.log(`${index}: Hello ${name}!,${moto}`

エクセルの他ファイル参照について

vlookupなどで表を参照する際に、参照するひょうを他ファイルとして保存しておくといろいろなところで利用することができる。

そこで、他ファイルの参照の記述をまとめておくことにする。

[ファイル名]シート名!セル範囲　です。

ファイルが同じディレクトリにない場合は、

'フルパス\[ファイル名]シート名'!セル範囲 です。

例えば、Dドライブのexcelというフォルダにある、data1.elsxというファイルのsheet2というシートにある、A2:C10のせる範囲なら

'd:excel\[data1]sheet2'!$A$2:$C$10　となります。

ただ、’は省略してもいいようです。動きます。また、エクセルの拡張子も書いても書かなくても動きます。

また、シート名だけ書いてファイルを指定しないと、ファイルを指定するダイアログが開いてファイルを選択できます。式をコピーすると１回のコピーに一度だけファイルを選択するダイアログが開きます。

Google Meetの録画について

Google Workspace for Educationの無料版Fundamentalsを使っているのだが、Meetの録画が１月９日から有料になると聞いていて、確認してみると、まだ録画ボタンがあって不思議でした。そこで、GoogleWorkspaceのヘルプを見てみると

support.google.com

Google Workspace for Education アカウント:

Education Fundamentals、Education Standard アカウント: 教師、職員、または生徒は、自分が主催する会議を録画できます。

と主催者ができることになってます。

生徒は、Meetを立ち上げられないようにしてあるので、先生には録画ボタンが表示されるんですね。良かった。どこかで、この情報は先生たちに流しておくか。

 

と書いたのだが、2022/2/11現在、しっかり使えなくなっている。一番上のリンクはそのまま表示されるが、ここにはきちんとFundamentalsでは使えないと書いてある。

support.google.com

残念でした。有料バージョンへの移行を考える必要があるでしょうか。

Amazon Fire HD8を買った

父親が、太陽光発電の充電器のシステムを新しくしてモニターがなくなったため、そのモニター代わりにAmazon Fire HD8を買ってみた。近くのケーズデンキで、当然のことながら割引０で購入。発電状況などの情報は、Web上に送ってその情報をブラウザで見るという方法のようなので、ネット接続とブラウザでモニターの代用ができると思って買ってみた。

Amazon Fire HDはアンドロイドではなく、Chromeブラウザが入ると思ったら、結構難しそうで、標準のブラウザはSilkというのだそうで、ネットを見ると使いにくいなんて情報もあった。でも使ってみると普通のブラウザで問題はなかった。

で、モニタとして使うために、画面のスリープをしないように設定したく、色々調べてみたら、設定は結構マニアックだった。ここに書いてあった。

instahack.jp

スリープの設定は通常は３０分までで、それを変えるには、開発者オプションを有効にしなければならず、そこがマニアックだった。上のリンクにも書いてあるが、ボタンで有効になるとかでなく、開発者オプションからは想像もつかない、シリアル番号を何度もタップすると開発者オプションが有効になるというなかなかな方法だった。

使いにくいと書いてあったSilkブラウザであるが、引っかかることもなく結構サクサクと動いている。１万円以下のタブレットであるので金額を考えると十分すぎる。