LANG SELRCT

Apps Script Reference  (Create: Create new Spreadsheet | Create new Apps Script

Wednesday, July 13, 2016

行・列を非表示にする

行と列を非表示にするコードを書いてみました。

1行目を非表示にする


コード.gs
function hiderow() {
    var sh = SpreadsheetApp.getActiveSheet();
    sh.hideRows(1);
}


1列目を非表示にする


コード.gs
function hidecol() {
    var sh = SpreadsheetApp.getActiveSheet();
    sh.hideColumns(1);
}


行と列の非表示を解除する


ここではすべての行と列を取得しました。
var last_row = sh.getRange("A:A").getLastRow();
var last_col = sh.getRange("1:1").getLastColumn();
var range = sh.getRange(1, 1, last_row, last_col);

コード.gs
function unhide() {
    var sh = SpreadsheetApp.getActiveSheet();
    var last_row = sh.getRange("A:A").getLastRow();
    var last_col = sh.getRange("1:1").getLastColumn();
    var range = sh.getRange(1, 1, last_row, last_col);
    sh.unhideRow(range);
    sh.unhideColumn(range);
}



A列で0の行だけを非表示にする


valuesを数値にして、厳密等価演算子 === で0と比較しました。
(等価演算子 == だと空欄もtureを返してしまうため)
if (parseInt(values[i], 10) === 0)

コード.gs
function hiderow_0() {
    var col = "A";
    var sh = SpreadsheetApp.getActiveSheet();
    var range = sh.getRange(col + ":" + col);
    var values = range.getValues();
    var last_row = sh.getLastRow();
    for (var i = 0; i < last_row; i++) {
        if (parseInt(values[i], 10) === 0) {
            sh.hideRows(i + 1);
        }
    }
}


1行目が0の列を非表示にする


hiderow_0と同様、厳密等価演算子 === で0と比較しました。
ここではvalues[i]では動かないため、values[0][i]にしました。
if (parseInt(values[0][i], 10) === 0)

コード.gs
function hidecol_0() {
    var row = 1;
    var sh = SpreadsheetApp.getActiveSheet();
    var range = sh.getRange(row + ":" + row);
    var values = range.getValues();
    var last_col = sh.getLastColumn();
    for (var i = 0; i < last_col; i++) {
        if (parseInt(values[0][i], 10) === 0) {
            sh.hideColumns(i + 1);
        }
    }
}


Sunday, July 10, 2016

split関数のfalseオプション

Google Apps Scriptではありませんが、スプレッドシートのsplitについて今日知ったことです。

区切り文字が2文字以上の場合、falseをつけないと1文字ごとに分割される。

=split("文字列", "区切り文字", false)


公式のヘルプページに書いてありました。
  • デフォルトでは、区切り文字の各文字が個々に検証されます。たとえば、区切り文字に "the" を指定した場合、テキストは "t""h""e" の文字で分割されます。各文字での分割を FALSE に指定すると、この動作をオフにできます。

参考サイト
https://support.google.com/docs/answer/3094136?hl=ja


Thursday, July 7, 2016

折り返しを設定するrange.setWrap(true)


Google Apps Scriptで折り返し設定をしたくて調べて書いてみたコードです。




コード.gs
function myFunction() {
 var sheet = SpreadsheetApp.getActiveSheet();
 var range = sheet.getActiveRange();
 range.setValue("テキストが長い場合に折り返して表示することができるようになる。").setWrap(true);
}



英数のみでスペースがないとうまく折り返しができなかった。。
例えば"abcdefghijklmnopqrstuvwxyz"というのを最初に例として書いてみましたが、うまく行かなかったので、全角の日本語で"テキストが長い場合に折り返して表示することができるようになる。"というテキストを例にしました。

ちなみにtrueがあればfalseもあり、falseにすると折り返しをしなくなります。

折り返しあり .setWrap(true) 
折り返しなし .setWrap(false)

というわけで今回は折り返しの設定をする.setWrap(true)を書いてみました。



Tuesday, June 28, 2016

シートのURLとシート名を取得する

前回書いた「スクリプトからmailを送信する」の続きです。
maiを送信する際に、どのスプレッドシートから送信されたのかがわかるように、シートのURLやシート名などを取得して、件名と本文に記載するということをやってみました。

「スプレッドシート」と「シート」という表現がちょっとわかりにくいかもしれませんが、「スプレッドシート」は開いているファイルそのもので、「シート」はその中にタブ区切りである「シート1」「シート2」などのことです。

前回は件名も本文もないメールを送信しましたが、今回は件名にスプレッドシート名とシート名を記載して、本文にシートのURLを記載するというコードを書きました。

シートのURLについては、.getUrl()だけでは取得できなかったので、.getUrl()でスプレッドシートのURLを取得した後に、.getSheetId()で開いているシートのIDを取得して、2つをくっつけました。
実際のシートのURLを見てみると、スプレッドシートのURLとシートIDとの間に「#gid=」が入っているので、文字列としてそれも間に入れました。
他にも方法があるかもしれませんが、ひとまずこれでシートからスクリプトで送ったメールに、そのシートをワンクリックで開くリンクを記載することができました。


ポイント
1. スプレッドシート名を取得します。
var ss_name = ss.getName();

2. 開いているシート名を取得します。
var sh_name = sh.getSheetName();

3. スプレッドシートのURLを取得します。
var ss_url = ss.getUrl();

4. 開いているシートのIDを取得します。
var sh_id = sh.getSheetId();

5. シートのURLを指定するために、スプレッドシートのURLとシートIDを"#gid="でくっつけます。
 ss_url + "#gid=" + sh_id;


コード.gs
function send_email() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getActiveSheet();
    var ss_name = ss.getName();
    var sh_name = sh.getSheetName();
    var ss_url = ss.getUrl();
    var sh_id = sh.getSheetId();

    var mail_address = Session.getActiveUser().getEmail();
    var subject = ss_name + "/" + sh_name + "のURLです。";
    var message = ss_url + "#gid=" + sh_id;
    MailApp.sendEmail(mail_address, subject, message);
}


コードを実行する

実行する手順については前回の「スクリプトからmailを送信する」を御覧ください。

実行すると、
件名:「スプレッドシート名/シート名のURLです。」
本文: シートのURL
のメールが送信されます。

Monday, June 27, 2016

スクリプトからmailを送信する

Gmailを起動せずに、スクリプトから直接メールを送信するというシンプルなコードをひとつ書いてみました。
例では自分宛て(スクリプトを実行しているユーザ)に自分(スクリプトを実行しているユーザ)からメールが送信されます。

*送信する際、特に確認を求められずにそのまま送信されるので、送り先は十分確認して実行することをおすすめします。

今回のようにただ特に内容のないメールを送るというだけなら、たった2行で書くことができました。まずは基本として最小限のコードで見やすいものをと思い下のようなコードで試してみました。
実際に使うときは、件名や本文は別途変数に代入されたものを指定したり、シートからいろいろと情報を取得したりするなどで、もっとコードは長くなっていくと思います。
まずは基本として最小限のコードで見やすいものをと思い下のようなコードで試してみました。

スクリプトを実行したスプレッドシートのURLを取得し、メールの本文に記載してクリック一発でシートを開けるようにしたり、スプレッドシート名を取得して件名に表示したりなどは、次回以降で書いていきたいと思います。


ポイント
1. 現在アクセスしている自分のEメールアドレスを取得します。
Session.getActiveUser().getEmail();

2. そのアドレスにメールを送信します。
MailApp.sendEmail(mail_address, "件名", "本文")

"件名"と"本文"は任意で変更してください。


コード.gs
function myFunction() {
    var mail_address = Session.getActiveUser().getEmail();
    MailApp.sendEmail(mail_address, "件名", "本文");
}


参考サイト
Class MailApp
https://developers.google.com/apps-script/reference/mail/mail-app#sendemailmessage

Sunday, June 26, 2016

スクリプトの処理を途中で止めて、再実行時に続きから実行できるようにする

Google Apps Scriptを書き始めた頃、何もわからずにいろいろなコードを書いていて、ある日かなり時間のかかるコードを実行したところ、処理が最後まで実行できないということが起こりました。
原因を見ると「起動時間の最大値を超えました」というエラーが出ていました。
こんな表示です↓


調べてみると起動時間の最大値は5分(現在は6分)らしいということを知りました。
https://developers.google.com/apps-script/guides/services/quotas

時間内に終わるようにスクリプトを書き直せればそれで良いのですが、時間内に終わらない場合は諦めるか、どうにか制限を越える方法を見つけるか。

そこで「制限を越える方法」について、制限時間を越える前に一度処理を止めて再実行するという方法を試してみました。

設定した時間が経過する前に処理を止めて時間制限を回避して、トリガーで再実行した時に前回実行した続きから処理を再開すようにするということをやってみます。

使用するデータとコードは前回書いたものを再利用します。
スクリプトの実行時間を取得してシートに書き出す


ポイント

1. スクリプトが実行されてからの経過時間を取得します。
var start = new Date();
〜実行する処理がここにあります〜
var end = new Date();
var time_past = (end - start) / 1000;

2. スクリプト実行から特定の時間(秒)が経過したらfor文から抜けて処理を止めます。
6分の場合は360秒ですが、今回の例ではデータが少ないので実行後すぐ止まることを確認できるよう2秒に設定しています。
if (time_past > 2) {
    break;
}

3. トリガーの実行タイミングを設定します。
今回は1分後に設定します。(1分毎に処理が実行される)

4. 再度実行された時に前回の続きから実行できるようする。
今回の例では、B列にデータを入れていく処理なので、「B列でデータが入っている最終行」の行数を取得して、再実行の際にはその次の行から実行されるようにします。


コード.gs
function setFormula_getValue_time_past() {
    var site = 'https://www.google.co.jp/#q=';
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getActiveSheet();
    var row = 1;
    var col = 1;
    var len = sh.getRange("A:A").getValues().filter(String).length;
    var row = sh.getRange("B:B").getValues().filter(String).length;
    var start = new Date();

    for (var i = row + 1; i < len + 1; i++) {
        var text = sh.getRange(i, col).getValue();
        var hyperlink = '=Hyperlink("' + site + text + '","' + text + '")';
        var end = new Date();
        var time_past = (end - start) / 1000;
        if (time_past > 2) {
            break;
        } else {
            sh.getRange(i, col + 1).setFormula(hyperlink);
            sh.getRange(i, col + 2).setValue(time_past);
            row++;
        }
    }
}


コードを実行してみる
1. コード.gsにコードを書いて保存します。

2. 編集メニューから「現在のプロジェクトのトリガー」を選択します。


3. 時間主導型の特定の日時で、分タイマーを選択します。

4. そのまま1分ごとを選択してトリガーを保存します。

5. スプレッドシートに戻って1分待ちます。
すると、スクリプトが自動で実行されて、2秒経過する手前で処理が止まります。

その後も1分毎に処理が実行され、最後の行まで処理が実行されます。

これと同じ要領で、今回2秒に設定した数値を360秒(6分)に設定し、トリガーの分タイマーも今回の1分から5分へ変更すると、時間制限にぶつかることなく最後まで処理を実行することができるようになります。

Saturday, June 25, 2016

スクリプトの実行時間を取得してシートに書き出す

スクリプトを実行してから処理が終わるまでの時間を計測したいと思った時に書いてみたコードです。
計測するだけならGoogle Apps Scriptと言うより、new Date()を使ったJavaScriptの処理になりますが、せっかくスプレッドシート上でGoogle Apps Scriptを使うので、取得した計測時間をシートに入力して時間の経過を見えるようにしてみます。

今回の例では、A列のテキストを使ってそのテキストをGoogle検索するためのリンクをB列に作成するということをやり、その処理を実行するのにかかった時間をC列に入力していくということをやっています。

A列のテキストはある程度数があったほうがわかりやすいので、例では47都道府県を入力しました。同じことを試す場合は下の方にスクリプトを実行する前の都道府県データ表を貼り付けたので、スプレッドシートにコピペして試してみてください。

Google検索するためのリンクの作成については、以前このブログで書いた「シートにHyperlink関数を入力する2(複数のテキストにリンクを作成する)」も御覧ください。
今回のコードは、その時に書いたコードに時間の計測をするコードを追加したものになります。


ポイント
1. 処理を開始した日時を取得してstartに入れます
var start = new Date();

2. 処理が終わった日時を取得してendに入れます
var end = new Date();

3. 経過した時間(秒)をtime_pastに入れます
var time_past = (end - start) / 1000;

4. それをC列に入力します
sh.getRange(i, col + 2).setValue(time_past);


コード.gs
function setFormula_getValue_time_past() {
    var site = 'https://www.google.co.jp/#q=';
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getActiveSheet();
    var row = 1;
    var col = 1;
    var len = sh.getRange("A:A").getValues().filter(String).length;
    var row = sh.getRange("B:B").getValues().filter(String).length;
    var start = new Date();

    for (var i = row + 1; i < len + 1; i++) {
        var text = sh.getRange(i, col).getValue();
        var hyperlink = '=Hyperlink("' + site + text + '","' + text + '")';
        sh.getRange(i, col + 1).setFormula(hyperlink);

        var end = new Date();
       var time_past = (end - start) / 1000;
        sh.getRange(i, col + 2).setValue(time_past);
        row++;
    }
}


コードを実行してみる

スクリプトを実行する前のデータはこうなっています。


スクリプトを実行すると、B列にリンクが自動で作成されていき、C列に処理時間が入力されていきます。


実行結果

今回僕が実行した時に最後の沖縄県までのGoogle検索リンクを作成するのにかかった時間は5.891秒でした。
スクリプトの処理にかかる時間はその時の通信状態や環境によって多少の誤差はあると思いますが、今回のような単純なリンクの作成程度ならばこのくらいの時間で実行できるということがわかりました。

スクリプトの実行にかかる時間を計測することで、実はひとつ大きな制限を越えることができます。
Google Apps Scriptでいろいろなコードを書いていると、そのうち幾つもの処理を一つのスクリプトの中で実行したくなってくると思います。万が一そうなってこなくても、ひとつのスクリプトの中で重たい処理をする場合、おそらく一度はひっかかるGoogle Apps Scriptの5分の制限というものがあります。
せっかく書いた長いコードが5分以上かかって実行しきれずに使えないというのは報われないので、制限を越えて実行できる方法を僕なりに試行錯誤したものを次回以降に書いていこうと思います。


今回使ったデータ
検索ワードリンク処理時間
北海道
青森県
岩手県
宮城県
秋田県
山形県
福島県
茨城県
栃木県
群馬県
埼玉県
千葉県
東京都
神奈川県
新潟県
富山県
石川県
福井県
山梨県
長野県
岐阜県
静岡県
愛知県
三重県
滋賀県
京都府
大阪府
兵庫県
奈良県
和歌山県
鳥取県
島根県
岡山県
広島県
山口県
徳島県
香川県
愛媛県
高知県
福岡県
佐賀県
長崎県
熊本県
大分県
宮崎県
鹿児島県
沖縄県



Friday, June 24, 2016

ヘッダのテキストに一致する列に入力する



以下のようなデータがあるとき
カナの列に「サシスセソ」を入力する
ということをやった時に書いたコードです





コード.gs
var sh = SpreadsheetApp.getActiveSheet();

function set_value() {
  var header = "カナ";
  var value = "サシスセソ";
  var headers = get_headers()[0];
  var row = sh.getLastRow() + 1;
  var col = headers.indexOf(header) + 1;
  sh.getRange(row, col).setValue(value);
}

function get_headers() {
  var last_col = sh.getLastColumn();
  var range = sh.getRange(1, 1, 1, last_col);
  var headers = range.getValues();
  return headers;
}
意訳.gs
今開いているシートを取得する

この処理は以下を実行する
入力したい列の値
入力したい値
1行目のすべての値を取得して
入力したい行(最終行の次の行)を取得して
入力したい列をみつけて
入力したい値を入力する


この処理は以下を実行する
値が入っている最終列を取得して
1行目の1列目から1行分、last_col列までの
値を取得して
返す




おまけ


var headers = get_headers()[0];
の[0]について

function get_headers() {}内でreturnしているheadersの中身を
ログに出して見てみるとこのようになっている


get_headers()で返ってくるのが [[かな, カナ, 半角カナ]]なので
get_headers()[0]とすると[かな, カナ, 半角カナ]を取得できる


ログに出すコード.gs
function get_headers() {
var last_col = sh.getLastColumn();
var range = sh.getRange(1, 1, 1, last_col);
var headers = range.getValues();
Logger.log(headers);
return headers;
}



参考
https://developers.google.com/apps-script/reference/spreadsheet/sheet

Thursday, June 23, 2016

編集した日時を自動で入力する

シートを編集した日時を知るには、ファイルメニューの変更履歴の表示を見ることで確認することもできますが、わざわざそれを開かずに、常にシート上に表示して見えるようにしておきたいと思い、Google Apps Scriptを使って書いてみたコードです。

A列の任意のセルを編集した時、B列に自動で編集した日時を入力するということをやっています。
※日時を入力する列にすでにデータが入っている場合は上書きされるので、データが入っていない列を指定して下さい。



ポイント

1. A列が編集されたら、B列に編集した日時を入力する。
  if(col == 1){
    var date = new Date();
    sh.getRange(row, 2).setValue(date);
  }



コード.gs
function onEdit() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getActiveSheet();
    var range = sh.getActiveRange();
    var row = range.getRow();
    var col = range.getColumn();
 
  if(col == 1){
    var date = new Date();
    sh.getRange(row, 2).setValue(date);
  }
}



A列を編集すると、B列に編集した日時が入力されます。

再度同じA列のセルを編集すると、B列の日時が更新されます。



if文を消すと、どの行を編集してもB列に編集日時が更新されるようになります。

コード.gs
function onEdit() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getActiveSheet();
    var range = sh.getActiveRange();
    var row = range.getRow();
    var col = range.getColumn();
 
  //if(col == 1){
    var date = new Date();
    sh.getRange(row, 2).setValue(date);
  //}
}


追記:もっと短く書いてみる
編集した行の10列目に日時を入力する

コード.gs
function onEdit() {
  const col = 10;
  const sheet = SpreadsheetApp.getActiveSheet();
  const row = sheet.getActiveRange().getRow();
  sheet.getRange(row, col).setValue(new Date());
}



指定したシートの指定した行以降のセルが編集された時に実行する

上の例では一行目を編集すると常にB列に日付の更新が入るため、一行目をヘッダにしている場合は二行目からを対象にしたいと思うはず。
そして複数のシートがある場合は、B列を自動で更新されたくないシートもあるはず。
ということで、指定したシートの2行目以降のセルが編集されたら、B列に更新日時を自動入力するというコードも書いてみました。

コード.gs
function onEdit() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getActiveSheet();
    var sh_id = sh.getSheetId();
    var range = sh.getActiveRange();
    var row = range.getRow();
    if (sh_id == 0) {
        if (row > 1) {
            var date = new Date();
            sh.getRange(row, 2).setValue(date);
        }
    }
}


Wednesday, June 22, 2016

シートのデータを昇順・降順でソートする

シートのデータを昇順・降順で並べ替えたい時は、フィルタ機能の「A→Zで並べ替え」「Z→Aで並べ替え」で実行できますが、それをコードから実行するということをやってみました。

事前準備

A列からC列に任意のデータを入力しておきます。
例として下記のようなデータを入力してみます。


ポイント
1. ソートしたい範囲を指定します。
var range = sh.getRange("A2:C");

2. 基準にする列を2(B列)に指定して、昇順に設定(ascending: true)します。
range.sort([{column: 2, ascending: true}]);


コード.gs
function sort(){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getActiveSheet();
    var range = sh.getRange("A2:C");
    range.sort([{column: 2, ascending: true}]);
}


コードを実行するまでの手順

1. コード.gsにコードを書いて保存します。

2. sortを実行します。

3. 許可を確認をクリックします。

4. 許可をクリックします。



B列を基準に、A列からC列のデータが昇順に並び変えられました。


おまけ



ソートする範囲を "A2:C" のような決め打ちではなく
シート内でデータが入っている範囲を自動で指定したくて
以下のようなコードを追加で書きました


このコードでは行が増えても列が増えても
A2から最終行、最終列を対象範囲にしてソートが行われます


"A2:C" の方が見た目で範囲がわかりやすいですが
こんな方法でもソートできます


コード.gs
function sort() {
  var target_col = 1;
  var asc = true;
  var start_row = 2;
  var start_col = 1;
  
  var sh = SpreadsheetApp.getActiveSheet();
  var last_col = sh.getLastColumn();
  var last_row = sh.getLastRow();
  var num_rows = last_row - (start_row - 1);
  var num_cols = last_col - (start_col - 1);
  var range = sh.getRange(start_row, start_col, num_rows, num_cols);
  range.sort([{
    column: target_col,
    ascending: asc
  }]);
}
意訳
この処理は以下を実行する
ソートする基準の列を指定して
昇順にしたければtrue, 降順にしたければfalseにして
ソートしたい範囲の先頭行を指定し(例では2行目)
ソートしたい範囲の先頭列を指定し(例では1列目(A列))

対象のシートを指定して(例では今開いているシート)
データが入っている最終列を取得し
データが入っている最終行を取得し
対象の行の数を取得し
対象の列の数を取得し
2行目の、1列目から、対象の行数、対象の列数を範囲に指定して
ソートする
基準にする列はtarget_colで指定した列
昇順はascで指定したとおり


Tuesday, June 21, 2016

選択した範囲のデータを一列にして書き出す

前回の 選択した列のデータを配列にして取得する では対象の列に入力されているデータすべてを取得しましたが、今回は指定した範囲内のデータだけを取得するということをやってみます。そしてそれを一列にしてシートに入力するということもやってみました。

事前準備
今回のコードを実行するため、例としてA〜C列にa1からc6までのデータを入力しておきます。



まずは選択した範囲のデータを配列で取得するコードを書きました。

ポイント

選択範囲のデータを取得するコードを書きます。
var value = range.getA1Notation();
var values = sh.getRange(value).getValues();


コード.gs
function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var range = sh.getActiveRange();
  var value = range.getA1Notation();
  var values = sh.getRange(value).getValues();
  Browser.msgBox("[" + values + "]");
}


コードを実行する

A2からC4を範囲選択して、myFunctionを実行してみると、

選択した範囲のデータが配列で取得できます。



次に、取得した配列を一列にしてシートに入力するということをやってみます。

ポイント

1. 例では、選択した隣の列に書き出します。
 var col = values[0].length + 1;

2. 行の数だけ、列の数を繰り返します。
for (var i = 0; i < values.length; i++) {
        for (var j = 0; j < values[i].length; j++) {
            sh.getRange(row, col).setValue(values[i][j]);
            row++;
        }
    }


コード.gs
function set_range_values() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getActiveSheet();
    var range = sh.getActiveRange();
    var value = range.getA1Notation();
    var values = sh.getRange(value).getValues();
    var col = values[0].length + 1;
    var row = 1;
    for (var i = 0; i < values.length; i++) {
        for (var j = 0; j < values[i].length; j++) {
            sh.getRange(row, col).setValue(values[i][j]);
            row++;
        }
    }
}


コードを実行する

A2からC4を範囲選択して、set_range_valuesを実行してみると、


A2からC4のデータが、D列に入力されます。


おまけ

例では配列の順番がa2, b2, c2となっています。
これを a2, a3, a4としたいと思い、そのコードも書いてみました。

ポイント
列の数だけ行の数を繰り返すという風にコードを書き換えます。 
for (var i = 0; i < values[0].length; i++) {
        for (var j = 0; j < values.length; j++) {
            sh.getRange(row, col).setValue(values[j][i]);
            row++;
        }
    }


コード.gs
function set_range_values2() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getActiveSheet();
    var range = sh.getActiveRange();
    var value = range.getA1Notation();
    var values = sh.getRange(value).getValues();
    var col = values[0].length + 1;
    var row = 1;
    for (var i = 0; i < values[0].length; i++) {
        for (var j = 0; j < values.length; j++) {
            sh.getRange(row, col).setValue(values[j][i]);
            row++;
        }
    }
}


コードを実行する

A2からC4を範囲選択して、set_range_valuesを実行してみると、

期待通りの結果が得られました。




空白セルを含む場合に空白を除いてくっつける


A, B, Cそれぞれの列で値の数が違う場合


空白セルの分を詰めたい

ifで囲んで空白セルは書き出さないようにする

if(values[j][i] != ""){
  sh.getRange(row, col).setValue(values[j][i]);
  row++;
}



コード.gs
function set_range_values2() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getActiveSheet();
    var range = sh.getActiveRange();
    var value = range.getA1Notation();
    var values = sh.getRange(value).getValues();
    var col = values[0].length + 1;
    var row = 1;
    for (var i = 0; i < values[0].length; i++) {
        for (var j = 0; j < values.length; j++) {
          if(values[j][i] != ""){
            sh.getRange(row, col).setValue(values[j][i]);
            row++;
          }
        }
    }
}



Monday, June 20, 2016

選択した列のデータを配列にして取得する


A列に a1 a2 a3 というデータが入ったシートから



[a1, a2, a3]

という1次元配列を作りたい




コード.gs
function get_array() {
  var sh = SpreadsheetApp.getActiveSheet();
  var last_row = sh.getLastRow();
  var range = sh.getRange("A1:A" + last_row)
  var values = range.getValues();
  var array = [];
  for(var i = 0; i < values.length; i++){
    array.push(values[i][0]);
  }
  Logger.log(array);
}
意訳
この機能がやること
対象のシートを取得して
シート内でデータが入っている最終行を取得して
A1から最終行までの範囲を指定して
範囲内のすべての値を取得して
配列の入れ物を用意して
値の数だけ以下を繰り返す
値を1つずつ取り出してarrayに追加する

最終的にできたarrayをログに出す





get_array() を実行すると以下のように1次元配列でログが出ます






列ごとにデータが入っている行数が異なる場合
1次元配列で取得したい列を指定してその最終行を取得してやる


コード.gs
function get_array() {
  var sh = SpreadsheetApp.getActiveSheet();
  var col = "A";
  var last_row = get_last_row(sh, col);
  var start_row = 1;
  var range = sh.getRange(col + start_row + ":" + col + last_row)
  var values = range.getValues();
  var array = [];
  for(var i = 0; i < values.length; i++){
    array.push(values[i][0]);
  }
  Logger.log(array);
}

function get_last_row(sh, col) {
  var values = sh.getRange(col + ":" + col).getValues();
  for (var i = values.length - 1; i >= 0; i--) {
    if (values[i] != "") {
      break;
    }
  }
  var last_row = i + 1;
  return last_row;
}
意訳
この機能がやること
対象のシートを取得して
列を指定して
その列でデータが入っている最終行を取得して
先頭の行を指定して
範囲を指定して
範囲内のすべての値を取得して
配列の入れ物を用意して
値の数だけ以下を繰り返す
値を1つずつ取り出してarrayに追加する

最終的にできたarrayをログに出す


この機能がやること
受け取ったshシート内のcol列の値(空欄を含む)をすべて取得して
値がなくなるまで以下を繰り返す
もし値が空でなければ
繰り返しの処理から抜ける


iは0から始まっているので i に1を足したものを行数として
返す




補足


以下のように普通に range.getValues() で取得すると
2次元配列が返ってくるのですが
1次元配列で取得したくて上記のようなコードを書いてみました


コード.gs
function get_array() {
  var sh = SpreadsheetApp.getActiveSheet()
  var last_row = sh.getLastRow();
  var range = sh.getRange("A1:A" + last_row)
  var values = range.getValues();
  Logger.log(values);
}





関連記事

データが入力されている最終行を取得する

Sunday, June 19, 2016

Google Apps ScriptでGoolge翻訳をしてみる

Google Apps ScriptでGoogle翻訳を利用するコードをひとつ書いてみました。

設定したテキストを翻訳してメッセージボックスに表示するという簡単な例です。

ポイント
今回はシンプルに、「Hello」という英語を日本語に翻訳するということをやってみます。
"Hello"を"英語"から"日本語"に翻訳するというコードは下のようになります。

LanguageApp.translate("Hello", "en", "ja");

英語は"en" で、日本語は"ja" で指定します。


コード.gs
function myFunction() {
    var translate = LanguageApp.translate("Hello", "en", "ja");
    Browser.msgBox(translate);
}


メッセージボックスに翻訳結果が表示されます


コードを実行するまでの手順

1. コード.gsにコードを書いて保存します。

2. 実行メニューからnyFunctionを選択します。

3. 許可を確認をクリックします。

4. 許可をクリックします。

シートに戻ると、翻訳された日本語がメッセージボックスに表示されます。



おまけ

スプレッドシートの関数でも翻訳することができます。
A1に「Hello」を入力して、B1に下の関数を入力してみてください。

=GoogleTranslate(A1, "en","ja")

すると、B1セルにはLoading...表示の後、「こんにちは」と翻訳されます。

その他の言語とコードの例
言語コード
英語en
スペイン語es
フランス語fr
イタリア語it
ドイツ語de
日本語ja
韓国語ko
中国語zh-CN


参考

Class LanguageApp
https://developers.google.com/apps-script/reference/language/language-app

Language support
https://cloud.google.com/translate/docs/languages

Latest post

Extracting data from Google Sheets with regular expressions

Introduction Regular expressions are a powerful tool that can be used to extract data from text.  In Google Sheets, regular expressions ca...