にわかプログラマの備忘録

仕事の過程で調べたことを忘れないように記載しています

【Javascript】機種依存文字チェック

Javascript機種依存文字をチェックする関数です。
modelDependent(変数)で使用します。

/**
 * 機種依存文字チェック
 * 
 * @param {String} v - チェックしたい文字列
 * @return {Boolean} - 使えない文字があった場合Falseを返す
 */
function modelDependent(v){
 //検索するパターン
  var p = /[①②③④⑤⑥⑦⑧⑨⑩⑪⑫⑬⑭⑮⑯⑰⑱⑲⑳ⅠⅡⅢⅣⅤⅥⅦⅧⅨⅩ㍉㌔㌢㍍㌘㌧㌃㌶㍑㍗㌍㌦㌣㌫㍊㌻㎜㎝㎞㎎㎏㏄㎡㍻〝〟№㏍℡㊤㊥㊦㊧㊨㈱㈲㈹㍾㍽㍼㍻㋿]/g;
  //vに上記のパターンに一致する文字があるか探す
  var result = v.match(p);
  //一致するものがなかったら
  if(result == null){
    return true;
  }else{
    return false;
  }
}

match()はパターンに一致した結果を配列で返すので、return falsereturn resultに書き換えれば機種依存文字だけ抜き出すことができます。

【GAS】カスタムメニューを作る【スプレッドシート】

関数をスプレッドシートから実行する際に便利なカスタムメニューを作成します。

function onOpen() {
  SpreadsheetApp.getUi()
    //項目名を作る
    .createMenu('カスタムメニュー')
    //関数を実行するボタンを作る
    .addItem('タイトル', '関数名')
    //線を引く
    .addSeparator()
    //サブ項目名を作る
    .addSubMenu(
      SpreadsheetApp.getUi()
        //サブ項目名
        .createMenu('カスタムメニュー2')
        //関数を実行するボタンを作る
        .addItem('タイトル', '関数名')
    )
    .addToUi();
}

これが↓になります。

f:id:sorane0908:20210802133032j:plain
カスタムメニュー

スプレッドシートを読み込みなおすことでヘルプの隣に表示されます。
少し時間が掛かるかも。

onOpen() スプレッドシートを読み込んだ際に実行されるものなので、他の関数のように保存しただけでは反映されません。

getUi()スプレッドシート(SpreadsheetApp)以外にもドキュメント(DocumentApp)とフォーム(FormApp)で使用できます。
ボタンを別途作成するより圧倒的に楽です。
あくまでスプレッドシートなどのUI(メニュー)に追加するもののため、スタンドアロン(Googleドライブに作成したGoogle Appe Script)では使用できません。

【GAS】スプレッドシートの値を取得する

値の取得法色々

記載範囲すべての値を取得する

function myFunction() {
  //アクティブなシートを取得
  let sheet = SpreadsheetApp.getActiveSheet();
  //記載範囲すべてを取得(Object)
  let range = sheet.getDataRange();
  //範囲の値を取得(2次元配列)
  let values = range.getValues();

  Logger.log(values);
}

getDataRange()は記載のあるすべての範囲を取得します。

f:id:sorane0908:20210730145949j:plain
スプレッドシート

上記の表なら A1:C10 の範囲が取得されます。

getValues()は範囲の値を2次元配列で取得します。
上記の表だと
[[1.0, とくに, いち], [2.0, いみは, にい],...... [10.0, ……, じゅう]]
となります。

function myFunction() {
  //アクティブなシートを取得
  let sheet = SpreadsheetApp.getActiveSheet();
  //記載範囲すべてを取得(Object)
  let range = sheet.getDataRange();
  //範囲の値を取得(2次元配列)
  let values = range.getValues();

  Logger.log(values[0][1]);//結果:いみは
}

となります。

特定のセルの値を取得

function myFunction() {
  //アクティブなシートを取得
  let sheet = SpreadsheetApp.getActiveSheet();
  //特定のセルを取得
  //getRange(row, column)
  let range1 = sheet.getRange(1, 2); 
  //getRange(a1Notation)
  let range2 = getRange('B1');
  //値を取得
  let values1 = range1.getValue();
  let values2 = range2.getValue();

  Logger.log(values1);//結果:とくに
  Logger.log(values2);//結果:とくに
}

getRange(row, column)は直接位置を指定します。
indexではないので、A1が(1,1)となります。
getRange(a1Notation)
また、getValue()対象が1つの時に使うものになります。

f:id:sorane0908:20210730145949j:plain
スプレッドシート
function myFunction() {
  //アクティブなシートを取得
  let sheet = SpreadsheetApp.getActiveSheet();
  //記載範囲すべてを取得(Object)
  let range = sheet.getDataRange();
  //1つの値を取得
  let values = range.getValue();

  Logger.log(values);//結果:1.0
}

getDataRange()(範囲)に対してgetValue()を使用すると一番左上の情報が取得されます。

function myFunction() {
  //アクティブなシートを取得
  let sheet = SpreadsheetApp.getActiveSheet();
  //特定のセルを取得
  let range = sheet.getRange(1, 2);
  //範囲の値を取得(2次元配列)
  let values = range.getValues();

  Logger.log(values);//結果:[[とくに]]
}

getRange(row, column)に対してgetValues()を使用すると[0][0]にしか値のない、あまり意味のない2次元配列が出来上がります。

範囲を指定して値を取得する

function myFunction() {
  //アクティブなシートを取得
  let sheet = SpreadsheetApp.getActiveSheet();
  //範囲を取得
      //getRange(row, column, numRows)
   let range1 = sheet.getRange(1, 2, 2);  //取得範囲:B1:B2
      //getRange(row, column, numRows, numColumns)
   let range2 = sheet.getRange(1, 1, 2, 3);  //取得範囲:A1:C2
      //getRange(a1Notation)
   let range2 = sheet.getRange("A1:C2");  //取得範囲:A1:C2

  let values1 = range1.getValues();
  let values2 = range2.getValues();
  let values3 = range3.getValues();

  Logger.log(values1);
  Logger.log(values2);
  Logger.log(values3);
}

getRange(row, column, numRows)行・列・高さ を指定します。
getRange(row, column, numRows, numColumns)行・列・高さ・幅 を指定します。
getRange(a1Notation)A1表記またはR1C1表記で指定します。

getRange(a1Notation)ではシートではなくスプレッドシート(Excelでいうブック)に使用することでシートを指定して取得することができます。

function myFunction() {
  //アクティブなスプレッドシートを取得
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //範囲を取得
  let range = ss.getRange("シート2!A1:A3");
  //範囲の値を取得(2次元配列)
  let values = range.getValues();

  Logger.log(values);
}

作って覚えるGAS:献立スロット4

見た目を整える

「回す」をボタンらしく

青くしてリンクらしく
styleタグ内

#slot{
  text-decoration : underline;/*下線を付ける*/
  text-decoration-thickness: 1px;/*下線の太さ*/
  color:darkblue;/*文字色*/
  cursor: pointer;/*マウスカーソルを変更*/
}

もしくはボタンらしく
styleタグ内

#slot{
  display: inline-block;/*要素の種類を変更*/
  padding: 0.5em 1em;/*余白をとる*/
  background-color: turquoise;/*ボタンの色*/
  font-weight: bold;/*文字を太字に*/
  color: white;/*文字の色*/
  border-bottom: solid 4px teal;/*下線(影)の設定*/
  border-radius: 3px;/*下線(影)の両サイドを丸める*/
  cursor: pointer;/*マウスカーソルを変更*/
}
#slot:active {/*クリックしたときの動き*/
  transform: translateY(4px);/*下に下げる*/
  border-bottom: none;/*下線を消す*/
}

材料部分の改行を有効に

現在スプレッドシート内に記載した改行が消えていると思います。
これはテキストの改行が\nなのに対しHTMLの改行が<br>のためです。

function listDisplay(list){
  //tableの子要素を削除する
  $( "#display table" ).empty();
  //listの数だけ処理する
  for(let i = 0; i < list.length; i++){
    //表示するHTMLを作成する
    let description = '<tr><td>';
    description += list[i]["sheetName"];
    description += '</td><td>';
    description += list[i]["name"];
    description += '</td><td>';
    description += list[i]["foodstuff"].replace(/\n/g, '<br>');//\nを<br>に置換
    description += '</td><td>';
    description += '<a href="' + list[i]["recipeURL"] + '" target=”_blank”>レシピリンク</a>';
    description += '</td></tr>';
    //table内に出力する
    $( "#display table" ).append( description );
  }
}

見た目を整える

全体が左に寄っているので中央に寄せ、tableの枠線を引きます。

中央にもっていきたい要素をdivで囲います。
body内

<div id="whoppar">
  <a id="slot">回す</a>
  <div id="display">
    <table>
      <!-- ここにスプレッドシートの情報を出す <200b>-->
<200b>     </table>
  </div>
</div> 

cssで中央に寄せ、枠線を表示させる。
style内

#whoppar{
  max-width: 600px;/*幅 指定した幅よりウィンドウが小さいとウィンドウのサイズに沿う*/
  margin: 0 auto;/*中央に寄せる*/
}
#display table{
  border-spacing: 0;/*border間の幅ゼロ*/
  border:2px solid #333;/*borderの設定*/
  border-collapse: collapse;/*borderを重ねる*/
  width: 100%;/*tableの幅*/
}
#display td{
  border:1px solid #333;/*borderの設定*/
  padding: 10px;/*余白を指定*/
}

完成

f:id:sorane0908:20210730100913j:plain
完成品

見た目はcssをいじればどうとでもなるので、とりあえずこれで完成!
お疲れさまでした。

作って覚えるGAS:献立スロット2

GAS側の作成

いよいよプログラムを作っていきます。

Google Appe Scriptの用意

まずは、新規Google Appe Scriptを作成します。
Googleドライブで右クリック→「Google Appe Script」で作成できます。

f:id:sorane0908:20210729110946j:plain
Google Appe Script

名前は分かりやすいものに(「献立スロット」にしました)

function myFunction() {
  
}

は必要ないので削除してください。

左にある「コード.gs」は2バイト文字のままだと都合が悪くなるかもしれないので適当な名前に変更してください(「code.gs」にしました)

スプレッドシートの情報を取得

function kondateSlot(){
  //取得するスプレッドシートを指定
  const spreadSheet = SpreadsheetApp.openById('スプレッドシートID');
  //スプレッドシートあるシートを取得
  const sheets = spreadSheet.getSheets();
  //シートの記載範囲を取得
  let range = sheets[i].getDataRange();
  //範囲の値を配列で取得
  let values = range.getDisplayValues();
  console.log(values );
}

スプレッドシートIDは料理情報を記載したスプレッドシートのURLから取得します。
https://docs.google.com/spreadsheets/d/スプレッドシートID/edit#gid=数字

実行すると権限の確認が出てくるので許可をしてください。
成功すると画面上の「実行ログ」に一番左のシート情報が配列形式で表示されます。

f:id:sorane0908:20210729131038j:plain
実行ログ

これはチェックも兼ねているのでまだほかのシート情報は取得できていません。

全部のシートの情報を取得する

function kondateSlot(){
  //取得するスプレッドシートを指定
  const spreadSheet = SpreadsheetApp.openById('スプレッドシートID');
  //スプレッドシートあるシートを取得
  const sheets = spreadSheet.getSheets();
  //シートの数だけループ
  for(let i = 0; i < sheets.length; i++){
    //シートの記載範囲を取得
    let range = sheets[i].getDataRange();
    //範囲の値を配列で取得
    let values = range.getDisplayValues();
    console.log(values);
  }
}

これで実行ログにすべてのシートのデータが出力されます。

シートの値を連想配列

function kondateSlot(){
  //取得するスプレッドシートを指定
  const spreadSheet = SpreadsheetApp.openById('スプレッドシートID');
  //スプレッドシートあるシートを取得
  const sheets = spreadSheet.getSheets();
  //シートの数だけループ
  for(let i = 0; i < sheets.length; i++){
    //シートの記載範囲を取得
    let range = sheets[i].getDataRange();
    //範囲の値を配列で取得
    let values = range.getDisplayValues();
    //値を連想配列にする。
    let asArray = association(values);
    console.log(asArray);
  }
}

/**
 * 2次元配列を渡すと1行目をkeyに連想配列を作成
 * 
 * @param {array} 2次元配列を渡すと1行目をkeyに連想配列を作成
 * @return {array} 連想配列
 */
function association(array){
  //空の配列を用意
  let asArray = [];
  //渡された配列の数だけループ
  for(let i = 1; i < array.length; i++){
    //空の連想配列を用意
    let item = {};
    //渡された配列の一つ目の要素の数だけループ
    for(let n = 0; n < array[0].length; n++){
      //1列目をkeyにして連想配列に
      item[array[0][n]] = array[i][n];
    }
    //作った連想配列を配列に追加
    asArray.push(item);
  }
  //追加し終わった配列を返す
  return asArray;
}

使い勝手を考えて、取得した情報を連想配列に書き換えます。
実行ログに出力された情報が先ほどとは少し違っているのがわかるかと思います。

association(array)で連想配列を作成する分とメインの機能切り離しています。

連想配列に種類を追加

このままだと、情報は主菜なのか副菜なのかが分かりづらいので先ほどの連想配列に情報を追加します。
追加する情報は記載されていたシートの名前です。

function kondateSlot(){
  //取得するスプレッドシートを指定
  const spreadSheet = SpreadsheetApp.openById('スプレッドシートID');
  //スプレッドシートあるシートを取得
  const sheets = spreadSheet.getSheets();
  //シートの数だけループ
  for(let i = 0; i < sheets.length; i++){
    //シートの記載範囲を取得
    let range = sheets[i].getDataRange();
    //範囲の値を配列で取得
    let values = range.getDisplayValues();
    //値を連想配列にする。
    let asArray = association(values);
    //配列の数だけループする
    for(let n = 0; n < asArray.length; n++){
      //連想配列にシートの名前を追加する
      asArray[n]["sheetName"] = sheets[i].getSheetName();
    }
    console.log(asArray);
  }
}

これでログに出てきた情報にシート名が追加されます。

ランダムで一つ抜き出してまとめる

function kondateSlot(){
  //取得するスプレッドシートを指定
  const spreadSheet = SpreadsheetApp.openById('スプレッドシートID');
  //スプレッドシートあるシートを取得
  const sheets = spreadSheet.getSheets();
  //空の配列を作成
  let listArray = [];
  //シートの数だけループ
  for(let i = 0; i < sheets.length; i++){
    //シートの記載範囲を取得
    let range = sheets[i].getDataRange();
    //範囲の値を配列で取得
    let values = range.getDisplayValues();
    //値を連想配列にする。
    let asArray = association(values);
    //配列の数だけループする
    for(let n = 0; n < asArray.length; n++){
      //連想配列にシートの名前を追加する
      asArray[n]["sheetName"] = sheets[i].getSheetName();
    }
    //配列の中からランダムで一つ選ぶ
    let cooking = asArray[Math.floor( Math.random() * asArray.length)];
    //listArrayに追加
    listArray.push(cooking);
  }
  console.log(listArray);
  //kondateSlot()を実行したときにlistArrayを返す
  return listArray;
}

return listArray;Javascriptから呼び出すときに必要になります。

GASで必要になるのはここまで。
次はHTMLファイルを作ります。

作って覚えるGAS:献立スロット1

献立スロットを作ろう

GASを使ってなんか作りたい。
でも、会社のは公開なんてできないし、ということで一時話題になった献立スロットを作ろうと思います。

基本仕様

既存のものだと作れないor作りたくない料理が出てくるのでスプレッドシートに料理の情報を入力し、その中からランダムで出てくるようにします。
一番簡単なのはスプレッドシート上にメッセージとして出力することなんですが、味気ないのでWebアプリとして作っていきます。

そしてリンクを張ってレシピページへ飛べるようにします。
大変なので栄養バランスは考慮しません。

事前準備

料理情報を入れるスプレッドシートを用意します。
下記のシートをもとに好きなだけ料理を追加してください。
献立スロット - Google スプレッドシート

各シートの1列目はシステム的に使用するので名前は変えないでください。
シート名が料理の種類になるので、ほかにも「ご飯もの」や「パスタ」など好きなものを追加してください。

name料理の名前
foodstuffメインの食材
(どうせ詳細はリンク先で確認するのでそれを作るか判断できる程度に記載)
recipeURLレシピの書いてあるURL

料理の画像も、と思ったのですが直リンクになり引用元のサーバーに負荷をかけてしまうためやめました。
画像を表示させたい場合は自分でレンタルサーバーを借りてUPするかGoogleドライブから使えるURLを生成するなどの手段をとってください。
くれぐれも著作権には気を付けて。

プログラムを作る

やること
  1. スプレッドシートの各シートから情報を取得する
  2. 各シートからランダムで1つ選ぶ
  3. 1つの配列にまとめる
  4. 表示させるHTMLファイルを作る(スロットのスタートボタンを作る)
  5. HTMLに紐づけたJavaScriptに3の配列を渡す
  6. JavaScript(JQuery)で配列情報をHTMLに表示させる

次からいよいよプログラムを作っていきます。

作って覚えるGAS:献立スロット3

HTMLの作成

HTMLファイルの作成

f:id:sorane0908:20210729153102j:plain
ファイルの追加

「ファイル」の横の+をクリックしHTMLファイルを作成します。
(ファイル名は「index.html」としました)

普通はJavascriptCSSはファイルを分けるのですが、今回はそんなに情報量も多くないので1つのファイルで作っていこうと思います。

HTMLファイルの大枠を作る

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
    <style>
      /* cssを書くところ */
    </style>
  </head>
  <body>
    Hello World!
    <script>
      //javascriptを書くところ
    </script>
  </body>
</html>

レシピ表示などにJQueryを使用する予定なので、最初から読み込んでおきます。
これで、HTMLが表示されれば「Hello World!」が表示されるはずです。
HTMLファイルを表示できるようにしましょう。

GASからHTMLファイルを開くようにする

作成したHTMLファイルにはURLがありません。
今回作成したGASのURLを発行しても現状何も起こりません。
まずはGASにアクセスがあったらHTMLファイルを開くようにします。

そのために、下記の文言をgsファイルに追加します。
(私の場合はcode.gs)

//htmlを表示
function doGet() {
  var toppage=HtmlService.createTemplateFromFile("ファイル名");
  return toppage.evaluate();
}

ファイル名部分には開きたいHTMLファイルの”○○.html”の”○○”だけを入力します。

ここで、アプリを公開(デプロイ)してみましょう。

f:id:sorane0908:20210729155238j:plain
デプロイ

右上のデプロイをクリックし「新しいデプロイ」を選択します。
「Webアプリ」を選択し、説明文などは後からでも変更できるので適当に入力し「デプロイ」をクリックします。
するとURLが発行されると思いますので、それをクリックすると別タブが開き「Hello World!」のHTMLファイルが表示されます。>

f:id:sorane0908:20210729160050j:plain
Hello Warld

今後はHTMLを確認したい場合は「デプロイをテスト」
最終的に公開する場合は「デプロイを管理」を行います。

ボタンと表示場所を作る

HTMLファイル内の各箇所に下記のコードを追加します。
body内

<a id="slot">回す</a>
<div id="display">
  <table>
    <!-- ここにスプレッドシートの情報を出す -->
  </table>
</div>

script内

//IDがslotの要素をクリックしたら
$("#slot").click(function() {
  console.log("ボタンをクリックしました");
});

入力したら「デプロイをテスト」をしてみましょう。
「回す」という文字が表示されていると思います。

そのファイルを右クリックから「検証」を行い、「Console」に「ボタンをクリックしました」と出ていれば成功です。

GASの情報を受け取る

script内

//IDがslotの要素をクリックしたら
$("#slot").click(function() {
  //GASの関数を呼び出す
  google.script.run.withSuccessHandler(listDisplay).kondateSlot();
});
//GASの関数から受け取った情報をもとに行う処理
function listDisplay(list){
  console.log(list);
}

「Console」にGASで作成したlistArrayの情報が出ていれば成功です。

GASの呼び出しは
google.script.run.withSuccessHandler(処理する関数).呼び出す関数();
となっています。
非同期のため、今回はlistDisplayに処理をまとめて記載していきます。

受け取った情報を表示する

Javascriptだけだと面倒なのでJQueryを使って表示させます。
function listDisplay(list)内

function listDisplay(list){
  //tableの子要素を削除する
  $( "#display table" ).empty();
  //listの数だけ処理する
  for(let i = 0; i < list.length; i++){
    //表示するHTMLを作成する
    let description = '<tr><td>';
    description += list[i]["sheetName"];
    description += '</td><td>';
    description += list[i]["name"];
    description += '</td><td>';
    description += list[i]["foodstuff"];
    description += '</td><td>';
    description += '<a href="' + list[i]["recipeURL"] + '" target=”_blank”>レシピリンク</a>';
    description += '</td></tr>';
    //table内に出力する
    $( "#display table" ).append( description );
  }
}

これで見た目はいまいちですが、GASの情報が出力できました。
次は見た目を少しマシにします。
(センスがないので少しだけです)