フォーマットクリアー・計算式とテキストを残して数値をクリアーする【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　となっていても、この条件であれば値が１→　trueとなり、次のclearContent()　で消去されてしまいます。
なので、次の①が必要になります。

①　range.getFormula() == ""

　　getFormula は、そのセルの計算式を取得する　という命令文です。
　　=SUM(D1:D3) という セルであれば SUM(D1:D3) が取得されます。
　　「売上」などの文字列や「123」などの数字であれば、「」が返ります。=""は、「計算式ではないとき」になります。＝”” でも =0 でも機能するようです。
①かつ③で、「計算式ではない数値は消す」となりました。

まとめ

getvalue() で取得したセル情報は値。値でも数値・テキストなどのデータ型を扱うということと、計算式を判別するためにgetFormula()
より細かな設定のために必要な知識になってきました。

発展（配列活用）

すべてのセルを一つ一つのぞいているのですごく重いです。
実用化のために配列版も掲載します

１．計算式のみ残すスクリプト：テキストも消えます

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)
}

２．計算式・テキストを残す方法

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