フォーマットクリアー・計算式とテキストを残して数値をクリアーする【GAS】【Googleスプレッドシート】【関数】
はじめに
関数中心にスプレッドシートを活用しているときでも使える内容です。
次のようなフォーマットをもとに、それぞれ内容を入力するケースがあります。もともとフォーマットとして作っていったものから、成長して、項目や計算式が追加されることもあると思います。
今度は、それをフォーマットとして使いたいときに、残したい部分を一括してクリアーするスクリプトを考えてみます。
一番単純なのは、セルの色による識別かもしれない。
こちらの方法は次回案内しようと思いますが、一番単純なのは「残したいセルは色を付けておく」という方法かもしれません。
スクリプトです
では解説です。
function format() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var rangeall = sheet.getDataRange()
var lastrow = rangeall.getLastRow()
var lastcol = rangeall.getLastColumn()
for (var col = 0; col < lastcol; col++) {
for (var row = 0; row < lastrow; row++) {
var range = sheet.getRange(row+1,col+1,1,1)
if (range.getFormula() == "" && isFinite(range.getValue() ) == true) {
range.clearContent();
}}}}
範囲の取得
var rangeall = sheet.getDataRange() var lastrow = rangeall.getLastRow() var lastcol = rangeall.getLastColumn()
→ データの入力されているセルの範囲を取得します。
一番最後の行・列を取得できるので、捜索する範囲を自動で指定できます。
「この列に」ではなく、シート範囲全体ですので、そこだけ注意が必要です。
繰り返し
【外】for (var row = 0; row < lastrow; row++) {
【中】for (var col = 0; col < lastcol; col++) {
【中】→【外】の順で表全体にいきわたるよう繰り返します。
繰り返す内容=各セルでやりたいこと
各セルの場所を指定します。
var range = sheet.getRange(row+1,col+1,1,1)
ここからが各セルに対して指示する内容です。
if (range.getFormula() == "" && isFinite(range.getValue() ) == true)
{ range.clearContent(); }
やりたいことを日本語で表現すると
それぞれのセルを見て、
計算式 → そのまま
項目=テキスト → そのまま
数字 → 消す
であれば、表が元に戻りそうです。
それをスクリプトで書くと次の通りです。
if (① range.getFormula() == "" ②&& ③ isFinite(range.getValue() ) == true)
{ ④range.clearContent(); }
4つに分けて解説します
④range.clearContent();
if 条件の結果です。clearContent() を実行すると、値が空白になります。キーボードで言うとDeleteと同じです。セルの色・罫線などはそのままです。一番単純なクリアーの命令文です。
②&&
こちらは条件文 IF のなかで、〇&&△ と記載した場合
「〇かつ△」、「〇であり△である」どちらも兼ねそろえた場合という指定になります。
〇と△に該当するのが今回のケースは①と③です。
③isFinite( ) == true
中に入っている range.getValue() は基本形です。セルの値の取得です。
isFinite( ) というのは、中身が数値であれば true 数値でなければ falseを返します。
この場合は「セルの値が数値の時」です。
注意しないといけないのは、
1 がそのまま入っていても、sum(範囲)で 1 となっていても、この条件であれば値が1→ trueとなり、次のclearContent() で消去されてしまいます。
なので、次の①が必要になります。
① range.getFormula() == ""
getFormula は、そのセルの計算式を取得する という命令文です。
=SUM(D1:D3) という セルであれば SUM(D1:D3) が取得されます。
「売上」などの文字列や「123」などの数字であれば、「」が返ります。=""は、「計算式ではないとき」になります。=”” でも =0 でも機能するようです。
①かつ③で、「計算式ではない数値は消す」となりました。
まとめ
getvalue() で取得したセル情報は値。値でも数値・テキストなどのデータ型を扱うということと、計算式を判別するためにgetFormula()
より細かな設定のために必要な知識になってきました。
発展(配列活用)
すべてのセルを一つ一つのぞいているのですごく重いです。
実用化のために配列版も掲載します
1.計算式のみ残すスクリプト:テキストも消えます
function formatxxx() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = sheet.getDataRange()
var formula = sheet.getDataRange().getFormulas()
for (var col = 0; col < formula[0].length; col++) {
for (var row = 0; row < formula.length; row++) {
if (formula[row][col].length !== 0) {
formula[row][col] = formula[row][col]
Logger.log(formula[row][col])
}
}
}
range.setFormulas(formula)
}
2.計算式・テキストを残す方法
function format2xxx() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = sheet.getDataRange()
var value = sheet.getDataRange().getValues()
var formula = sheet.getDataRange().getFormulas()
for (var col = 0; col < formula[0].length; col++) {
for (var row = 0; row < formula.length; row++) {
if (formula[row][col].length == 0 && isFinite(value[row][col] ) == true) {
sheet.getRange(row+1,col+1,1,1).clearContent();
Logger.log(formula[row][col])
}
}}}
配列を使わないパターンと合わせて読み込んでみてください。
今回のシートです。
https://docs.google.com/spreadsheets/d/1tDkwq8chQ8AW8sjdXXubUYfNOB179j92S8uGuzf9Pvo/edit#gid=0
どんな記事ならサポートしてみようと思えるか、ご要望ありましたら教えてくださいね。