liquidjumper GoogleAppsScript,Googleスプレッドシート,javascript,アプリ/ネットサービス Googleスプレッドシート|全角日本語入力モードのままでセルに半角英数字を入力する方法-Google Apps Script

Googleスプレッドシート|全角日本語入力モードのままでセルに半角英数字を入力する方法-Google Apps Script

googlespreadsheet_gas_conversion_single-byte_topimage

「商品名にサイズに価格に商品説明、全角・半角・数字、入力モードをいちいち切り替えるの面倒くさい何とかして!!」

商品管理のデータを入力するのは想像するより大変です。

例えば、スプレッドシートにデータを入力する時の決まり事として、

商品名は全角ひらがな、商品型番は半角大文字英数、商品説明は全角ひらがな、価格は半角英数

とした場合、一つの商品データ入力のために、都度入力モードを切り替える必要があります。

商品名商品型番商品説明
安眠まくらARXM-10ぐっすり眠れる枕


商品名と説明は日本語(ひらがな)で書く必要があるし、商品説明は”RXM-10″や”DZ-7500XM3″などハイフン付きの大文字英数字、価格は数値データとして必要なので半角英数にする。

使用言語が英語だけなら、こんな手間は必要なかったのかもしれませんが、そんな妄想も言ってられません。

全角日本語(ひらがな):あいうえお 012345
全角英数:ABCDEabcde 012345
半角英数:ABCDEabcde 012345


もしも、モードの切り替えを省くことが出来れば、データの入力作業も大分楽になるはず。全角入力モードのままで全てを入力する事ができたら、大分効率的になるのでは?

そこでこの記事では、Google Apps Scriptを使ってGoogleスプレッドシートのセルに全角入力モードの状態で半角英数字を入力する方法を紹介します。


全角日本語入力モードの状態で半角数字をセルに入力するには

全ての枠が全角入力モードで入力出来るように、半角英数の枠に全角英数で入力すると、半角英数字に変換されるスクリプトを作成します。

セルに値を入力すると、全角英数から半角英数に値が変換するように、以下の流れで実行されるスクリプトを作成します。

  1. セルに値を入力したらスクリプトを実行
  2. 値を入力・編集したセルの位置を取得
  3. セルに入力した値を取得
  4. 値が数値か文字列かを判定
  5. 文字列が全角か半角かを判定
  6. 全角英数を半角英数に変換
  7. 変換した半角英数をセルに入力

Google Apps Script(GAS)で全角文字列から半角数字に変換するスクリプトを作成

Google Apps Script(GAS)はGoogleのあらゆるサービス、Gmail、Googleカレンダー、Googleドライブ、Googleドキュメント 等を自動化、連携、機能拡張する事ができるスクリプトプラットフォームです。

プログラミング言語はjavascriptをベースにしているため簡単に学習可能で、Googleアカウントさえ取得すれば誰でも動かすことが出来ます。

そのGoogle Apps Script(GAS)は勿論、Googleスプレッドシートにも対応しているので、セルの値の取得や入力などをスクリプトで操作する事が出来ます。

Googleスプレッドシートのスクリプトエディタで半角英数変換のスクリプトを作成

Google Apps Script(GAS)を編集実行するスクリプトエディタを開いて変換処理のスクリプトを作成します。

Googleスプレッドシートのメニューバーから ツール > スクリプトエディタ を選択します。

スクリプトエディタ を選択


スクリプトエディタが表示されたら、以下のスクリプトを記述します。

function conversion() {
 
  const cell_column_No = 4;//変換する対照の列番号。価格の列がD列なので、1から数えて4番目。
 
  const sheet = SpreadsheetApp.getActiveSheet();
  const lastRow = sheet.getLastRow();
  const activecell = sheet.getActiveCell();
 
  if(activecell.getColumn() == cell_column_No){//編集されたセルがスクリプト対照の列なのかを判定
 
    let active_row_cell = activecell.getRow();
   
    let cell_value = sheet.getRange(active_row_cell , cell_column_No).getValue();//セルの値を取得
   
    let rep_value = "";     
   
    if (Number.isFinite(cell_value)){//値が数値なのか文字列なのかを判定 123:true ”123”:false
     
      rep_value = cell_value;
     
    }else{
     
      let match_value = cell_value.match(/[A-Za-z0-9]/g);//文字列の全角判定
     
      if (match_value != null){
        //全角を半角に変換 アルファベットと数字
        rep_value = cell_value.replace(/[A-Za-z0-9]/g, function(s){return String.fromCharCode(s.charCodeAt(0) - 0xFEE0);});
        
       
      }else{
       
        rep_value = cell_value;
      }
     
    }   
   
    let setCell = sheet.getRange(active_row_cell, cell_column_No);
    setCell.setValue(rep_value);//半角に変換した値をセルに入力
    }
}


説明:

 const activecell = sheet.getActiveCell(); 

getActiveCell()アクティブなセルから、更新されたセルを取得します。

 if(activecell.getColumn() == cell_column_No) 

半角英数に変換処理するセルの列なのか判定。

 let cell_value = sheet.getRange(active_row_cell , cell_column_No).getValue(); 

getActiveCell()からgetColumn()とgetRow()で更新したセルの位置を特定、セルの値を取得します。

 if (Number.isFinite(cell_value)) 

値が数値なのか文字列なのかを判定します。 数値ならtrue 、数値でなければfalseを返します。

 let match_value = cell_value.match(/[A-Za-z0-9]/g); 

match()関数で文字列が全角英数なのか判定します。

 rep_value = cell_value.replace(/[A-Za-z0-9]/g, function(s){return String.fromCharCode(s.charCodeAt(0) - 0xFEE0);}); 

replace()関数で全角英数を半角英数に変換処理します。全角文字の文字コードから”65248″を引いて半角英数を割り出します。

 let setCell = sheet.getRange(active_row_cell, cell_column_No);
setCell.setValue(rep_value); 

編集・更新したセルに変換処理した半角英数の値を入力します。


ファイル > 保存 又は、保存ボタンをクリックします。

保存ボタンを


名前を付けて保存します。

名前を付けて保存

セルの値が変更されたらスクリプトを実行する

Google Apps Script(GAS)で作成したスクリプトは、スクリプトエディタの[実行ボタン]や、スプレッドシートの図形で作成した実行ボタンからスクリプトを実行する事が出来ます。

ただ今回のスクリプトの実行タイミングはボタン等のクリックではなく、セルに値を入力した事をトリガーにして実行するようにします

スクリプト実行のトリガーを設定

セルの値を変更するとスクリプトが実行されるトリガーの設定をします。

スクリプトエディタのメニューバーから 編集 > 現在のプロジェクトのトリガー を選択します。

現在のプロジェクトのトリガー を選択


右下の[トリガーを追加]をクリックします。

[トリガーを追加]をクリッククリック


トリガーの設定をします。

実行する関数を選択:[実行するスクリプトの関数名を選択]
イベントのソースを選択:[スプレッドシートから]
イベントの種類を選択:[変更時]

「保存」をクリックして閉じます。

トリガーの設定


自身のGoogleのアカウントをクリック

Googleのアカウントをクリック


このアプリは確認されていませんと表示されたら、「詳細」をクリック。

「詳細」をクリック


“(安全ではないページ)に移動” をクリック。

(安全ではないページ)に移動


“Googleアカウントへのアクセスをリクエストしています”で、「許可」をクリック。

Googleアカウントへのアクセスをリクエストしています


トリガーが追加されました。これでセルへの入力をきっかけにして、スクリプトが実行されるはずです。

トリガーが追加されました

日本語入力(全角)でセルに値を入力する

全角英数から半角英数に変換されるスクリプトが、セルの更新をトリガーにして実行されるようになりました。

早速、スプレッドシートのセルに全角入力モードで数字を入力してみます。

全角入力モードで数字を入力

日本語入力(全角)で数字を入力する

価格の列に全角入力モードで数字を入力します。

全角入力モードで数字を入力


セルに全角数字が入力されると、直後に変換処理が実行され、半角数字に変わりました

半角数字に変わりました

表示形式を設定

数値だけだと数字の意味が分かりにくいので、表示形式を設定します。商品の価格だとわかるように数字の表示形式を通貨に設定します。

メニューバーから 表示形式 > 数字 > 通貨(端数切り捨て) を選択

通貨(端数切り捨て) を選択


数値が「¥」付きの円通貨表示になりました。

円通貨表示

日本語入力(全角)で英数字を入力する

商品型番の列に全角英数を入力したら、半角英数字に変換されるようにします。

conversion()関数をコピーしてconversion()のペーストします。コピーした関数にconversion_ProductNumber()と関数名を付けました。

function conversion_ProductNumber(){
 
  const cell_column_No = 2;//変換する対照の列番号
 
  const sheet = SpreadsheetApp.getActiveSheet();
  const lastRow = sheet.getLastRow();
  const activecell = sheet.getActiveCell();
 
  if(activecell.getColumn() == cell_column_No){//編集されたセルがスクリプト対照の列なのかを判定
 
    let active_row_cell = activecell.getRow();
   
    let cell_value = sheet.getRange(active_row_cell , cell_column_No).getValue();//セルの値を取得
   
    let rep_value = "";     
   
    if (Number.isFinite(cell_value)){//値が数値なのか文字列なのかを判定 123:true ”123”:false
     
      rep_value = cell_value;
     
    }else{
     
      let match_value = cell_value.match(/[A-Za-z0-9]/g);//文字列の全角判定
     
      if (match_value != null){
        //全角を半角に変換 アルファベットと数字
        rep_value = cell_value.replace(/[A-Za-z0-9]/g, function(s){return String.fromCharCode(s.charCodeAt(0) - 0xFEE0);});
        rep_value = rep_value.toUpperCase() ;
        rep_value = rep_value.replace('-', '-');
       
      }else{
       
        rep_value = cell_value;
      }
     
    }   
   
    let setCell = sheet.getRange(active_row_cell, cell_column_No);
    setCell.setValue(rep_value);//半角に変換した値をセルに入力
    }
}


説明:

 const cell_column_No = 2; 

商品型番の列番号に変更します。商品型番の列は左から数えて2番目なので”2″にします。

 rep_value = rep_value.toUpperCase() ; 

小文字を大文字に変換します。

 rep_value = rep_value.replace('-', '-');

conversion_ProductNumber()のコードに、全角の「-」を半角の「-」に変換する処理を追加しました。


保存して、セルの値が更新されたらconversion_ProductNumber()が実行されるようにトリガーを設定します。編集 > 現在のプロジェクトのトリガー からトリガーの設定をします。

実行する関数を選択で、コピー&ペーストした関数[conversion_ProductNumber]を選択します。これで商品型番のセルに値を入力したら、スクリプトが実行されます。

[conversion_ProductNumber]を選択


商品型番の列に「-」付きで小文字の全角英数字を入力します。

小文字の全角英数字


「-」付きの大文字半角英数に変換されました。

大文字半角英数に変換

まとめ:Google Apps Scriptを使えばセルの入力値を変換できる

Google Apps Scriptを使ってGoogleスプレッドシートのセルに全角入力モードのまま半角英数字を入力する方法を紹介しました。

今回は全角英数を半角英数に変換するだけの処理でしたが、スクリプトの処理を増やせば、全角半角のカタカナや記号などを変換処理して、値を入力する事が可能になります。

セルの更新をトリガーにして、スクリプトで変換処理を実行、セルに値を入力する

応用次第で更に便利な入力作業の補助になるスクリプトが作成できそうです。









𝕏 ( 旧Twitter ) Facebook Pocket LINE はてブ

おすすめの関連記事



あわせてよく読まれている記事