質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
Google Apps Script

Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

Q&A

解決済

1回答

2203閲覧

Google Spread Sheetの特定の値を置換するコードを教えてください

inokawa_k

総合スコア11

Google Apps Script

Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

0グッド

0クリップ

投稿2018/02/18 22:01

編集2018/04/29 02:40

Google Spread SheetのE,F,G列に存在する-1という値を0か空白に置換するコードを教えていただけませんでしょうか?
「ツール」の中の「スクリプトエディター」に書き込むコードです。
いまは、「編集」タブの中の「検索と置換」を利用してE,F,G列を選択し置換する作業を手作業で行なっていますが、これを自動化したいのです。
シロウトの質問で申し訳ありませんが、どなたかよろしくお願いします。

ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

GAS入門を読んで、自分なりにコードを書いてみました。
なんとか動いたのですが、実行にすごく時間がかかります。replaceメソッドというのを使った方がよいのでしょうか?
あいかわらず、不躾な質問ですみません。

function sample() {
var sheet = SpreadsheetApp.getActiveSheet()
var lastRow
lastRow = sheet.getLastRow()
for (var i=1; i<=lastRow; i++) {
E列 = sheet.getRange(i, 5).getValue()
F列 = sheet.getRange(i, 6).getValue()
G列 = sheet.getRange(i, 7).getValue()
if (E列 == -1) {
sheet.getRange(i, 5).setValue("")
}
if (F列 == -1) {
sheet.getRange(i, 6).setValue("")
}
if (G列 == -1) {
sheet.getRange(i, 7).setValue("")
}
}
}

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

jinshan

2018/02/19 01:30

こちらのGAS入門の第10回まで半日程度で読めば容易にできると思います。まずは頑張ってスクリプトを自力で書いてみましょう。それでスクリプトの内容でつまずいたら再質問するとどなたかが答えてくれると思います。 http://excel-ubara.com/apps_script1/
inokawa_k

2018/02/19 21:42

アドバイスありがとうございます。さっそくGAS入門をやって自分でコードを書いてみます。その上でまたご助言いただければ幸いです。
guest

回答1

0

ベストアンサー

↓こんな感じでどうでしょうか?

javascript

1function sample() { 2var sheet = SpreadsheetApp.getActiveSheet() 3 var values = sheet.getDataRange().getValues(); 4 5 for(var idx in values){ 6 if(values[idx][4] === 0) {values[idx][4] = "";} 7 if(values[idx][5] === 0) {values[idx][5] = "";} 8 if(values[idx][6] === 0) {values[idx][6] = "";} 9 } 10 sheet.getDataRange().setValues(values); 11}

勉強する方のようなので説明を。

  • 遅いこと

GoogleAppScriptは、APIコールが遅いです("APIコール"は、SpreadsheetAppみたいにxxxAppを使うものです。getRangeとか、getValueとかも、sheet由来なのでAPIです)。
これはそういうものなので、回避策として、最初に欲しいデータをごっそり取ってきて、それを加工して、まとめて戻す、という方法を使います。
例えば、あなた自身がトライした方法でいうと、行数かける3倍から6倍の回数分だけ、APIコールをしていますが、↑であげたのだと行数に関係なくgetValuesとsetValuesを1回ずつしかしていません。
なので、スプレッドシートを使うなら、javascriptの配列と仲良くなってください。

  • あまり役に立たないかもしれないけど解説

** var values = sheet.getDataRange().getValues();
データが入っている領域を表としてまとめてとってくる処理です。Lastとかを考えなくてもやってくれる便利機能です。
表なので縦の行数x横の列数の2次元配列になっています。

** for(var idx in values)
行数が何番目かを取り出してます。ちなみに配列は0番目からはじまります。何からはじまるかとか考えるとミスのもとなので、inで取り出してます(for(var i = 0; i < values.length ; i++)とはしなかったということです。var i = 1とか、i <= values.lengthとかしちゃうとバグります)

** if(values[idx][4] === 0) {values[idx][4] = "";}
idx行目の4列目(0はじまりなので、Eは4列目になります)が何かを判定して→書き込みの流れ。
"4"とか書くのはよくないですが、var e = values[idx][4]; if(e === -1) e = "";ってやってもだめです。valuesを書き換える必要があります。
(var ColE = 4;values[idx][ColE] = "";とかするとマシかもしれない)

** sheet.getDataRange().setValues(values);
まとめて戻しています。ただし、表のかたちが同じ(二次元配列であること。表のサイズが違ってたらどうなるか未確認)でないといけません。ここでは取り出したvaluesを戻しているので、かたちは同じはず。
形がそろってれば、大きな表でも一気にセットできる便利機能です。

** replaceについて
どの機能のことかわかりませんでした。文字列のreplaceの話であればここではあまり速度には貢献しないと思います。


追記

javascript

1function sample() { 2var sheet = SpreadsheetApp.getActiveSheet() 3 var values = sheet.getRange(1, 5, 100,3).getValues(); 4 5 for(var idx in values){ 6 if(values[idx][0] == -1) {values[idx][0] = "";} 7 if(values[idx][1] == -1) {values[idx][1] = "";} 8 if(values[idx][2] == -1) {values[idx][2] = "";} 9 } 10 sheet.getRange(1, 5, 100,3).setValues(values); 11}

投稿2018/05/02 17:47

編集2018/05/06 02:58
papinianus

総合スコア12705

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

inokawa_k

2018/05/03 22:41

解決案およびていねいな解説ありがとうございました。 さっそく if(values[idx][4] === 0) {values[idx][4] = "";}のところを if(values[idx][4] === -1) {values[idx][4] = "";}としてやってみました。 すると一瞬で-1が入ったセルが空白になりましたので問題解決かと思ったのですが 今度はシートの他の部分に埋め込んだ計算式(関数と言うのでしょうか、単純にセル同士の掛け合わせや引き算などです)が消えてしまったのです。 今回のコードはデータが入っている表の全体に作用ものだということですので、それが原因でしょうか? たびたび恐れ入りますが、ご教授ねがえればありがたいです。
papinianus

2018/05/06 02:42 編集

それは大変危険なソースを提示してしまいました。すみません。 「.getDataRange()」は使われている領域を全部取ってきますので、やりかたによっては本来書き換えたくない場所まで波及する危険があります。 ただ、いちおう私も確認しているのですが、D列とかH列に入った値は消えない(無関係なデータは触らないようにしてます)ようにしておりました。関数だけが消えたのでしょうか?関数はどこの列に入っていたものでしょうか?
papinianus

2018/05/06 03:02

確認しました。getValuesをするとシート関数が計算されて結果の値だけになってしまいますね。 E1からG100までだけ置換するような方法を追記しました。 getRangeはつかっておられるので、なんとなく分かると思います。3つめ4つめに、終わりのセルの位置を(相対距離で)書くと、四角形の領域をとることができます。 また、こう変えたことによって、E列はvalues配列の0番目になりますので、値のチェックと代入は0、1、2の番地に対して行うことになります、ご注意ください。
inokawa_k

2018/05/08 07:20

ご連絡が遅くなりました。教えていただいた方法で無事、解決しました。 E〜G列は現在740行までありますので、var values = sheet.getRange(1,5,740,3).getValues(); としました。これは今後行が増えていったときその都度、修正するということですね。 お忙しい中、ていねいに教えてくださり、ありがとうございました。助かりました。
papinianus

2018/05/08 11:47

増えるのが縦方向だけなら、var values = sheet.getRange(1, 5, sheet.getLastRow(), 3).getValues()とすると都度修正しなくていいかもです。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問