ノンプロ研GAS中級講座　第1回　スコープと関数　その2

前回のnote

演習１－06と演習１－07　関数化のメリット

演習１－06

function myFunction1_06() {
 const taxRate = 0.1;
 const price = 100;
 console.log(Math.floor(price*(1+taxRate)));
 
 const strDate = '2020/09/11';
 console.log(strDate.split('/')[0]);
 }

演習１－07

function myFunction1_07() {
 const getTaxIncluded = price => {
   const taxRate = 0.1;
   return Math.floor(price * (1 + taxRate));
 };
 const getYear = strDate => strDate.split('/')[0];
 console.log(getTaxIncluded(100));
 console.log(getYear('2020/09/11'));
}

演習１－06と演習１－07は、やってることは同じだが、書き方が違う。
7では関数化している。

関数化のメリット
・処理の分離：主となる処理以外を意識から外せる
・関数名とブロックによる可読性
・再利用性・メンテナンス性

う～ん、メリットとしては分かるんだけど、でも、やっぱ初学者としては演習１－06のような直球な書き方のほうが理解しやすい、と思うんだよな～。関数化されるとちょっと読み解くのにｳｯってなる。抽象度が高くなってるからでしょうか？

演習１－08　★宿題

Spreadsheetオブジェクトを渡すと、残り使用可能なセル数を返す関数を作成しましょう。
（スプレッドシートの使用可能なセル数は500万セル）

草案1

は～ん、何言ってんだこれは、うーん。
とりあえず、ざっくりとした形としてはこうなるのか？な？

// 草案1
function getRestCell() {
 console.log(restCell(Spreadsheetオブジェクト));
}

function restCell(ss) {
 return 残り使用可能なセル数;
}

う～ん、これをどう仕上げていけば？

草案2

まずは、Spreadsheetをconstしないとかな～。

で、最大数500万は決まっているので、使っているセルをどうにか数えて、残りを出す、こんな感じか？

// 草案2
const ss = SpreadsheetApp.getActiveSpreadsheet();//Spreadsheetオブジェクト
function getRestCell() {
 console.log(restCell(ss));
}
function restCell() {
 return 500万-現在使用してるセル数＝残り使用可能なセル数;
}

まずはSpreadsheetオブジェクトを取ってくるところから。

草案3

う～んreturn　のところをうまく書きたいですね。
MAX500万セルなので、これも書けそう。

// 草案3
const ss = SpreadsheetApp.getActiveSpreadsheet();//Spreadsheetオブジェクト
let restCell = 5000000;
function getRestCell() {
 console.log(returnRestCell(ss));
}
function returnRestCell() {
 return restCell-現在使用してるセル数;//＝残り使用可能なセル数
}

草案4

とりあえず使用済を1と仮置きして、計算してみる。ロジックとしては合ってそうなので、あとは現在使用しているセル数usedCellをどう取ってくるかです。

これまでを踏まえると、DataRangeでvalueをガッと取ってくればいいんでしょうか？getMaxRows()メソッドというのもあるようです。
このへんは皆さんの回答見ながら考えていました。

まずはDataRangeでvalueをガッと取ってその数を取る（length？）方式で考えてみます。

アクティブなスプレッドシートを指定して、シート数、範囲、値というように大項目から小項目へ、大から小に徐々に絞り込んでいく形を念頭に置きつつ。

「使用しているセル」の認識の仕方

ここでちょっと躓いた。どういうことか。
結論から言うと「使用しているセル」の認識の仕方だった。
下図で言うと、私は使用しているセル＝4と思っていたが、実際にはこれは9になる。図の場合、C1セルやA2、A3,、B3、C2は空白だけど、使用されているとみなすってことになる。スプレッドシートの場合、A1と最も右端かつ下にある数字を基点として範囲を取るようです。このへんのことって、どこに書いてあるんだ？みんなどこで、これを知るの？

そういうわけで、セルのカウントでうんうん悩んでましたが、空白も使われているとみなすなら話は早い、単純な縦横の掛け算で数が出せそうです。

草案5

上記のカウントであ～これだと数が合わないんじゃ？などと色々悩んでいたのでconsole.logで色々確かめている。これを整えていきたい。

// 草案5
function getRestCell() {
 const ss = SpreadsheetApp.getActiveSpreadsheet();//Spreadsheetオブジェクト
 // console.log(ss.getName());//Spreadsheetファイル名確認
 const sheets = ss.getSheets();
 // console.log(sheets.length);//シート数確認
 // for(sheet of sheets){
 //   console.log(sheet.getName());//シート名確認
 // }
 for (const sheet of sheets) {
   const values = sheet.getDataRange().getValues();
   // console.log(values);//values数確認
   // console.log(values.length);//values数確認
   // console.log(values[0].length);//values数確認
   // console.log(values[1].length);//values数確認
   // const flatValues = values.flat();
   // console.log(flatValues);
   const usedCellCounts = values.length * values[0].length;
   console.log(usedCellCounts);//values数確認
   // usableCellCounts -= usedCellCounts;
 }

 // console.log(returnRestCell(ss));
}
// function returnRestCell() {
//   const maxCell = 5000000;
//   const usedCell = 1;
//   return maxCell - usedCell;//＝残り使用可能なセル数
// }

く

草案6

// 草案6　

const SS = SpreadsheetApp.getActiveSpreadsheet();//Spreadsheetオブジェクト

function getRestCell() {
 console.log(returnRestCell(SS));
}

function returnRestCell() {
 const maxCell = 5000000;
 let usedCell = 0;
 const sheets = SS.getSheets();

 for (const sheet of sheets) {//シートの枚数分処理
   const values = sheet.getDataRange().getValues();
   usedCell += values.length * values[0].length;
 }
 return maxCell - usedCell;//＝残り使用可能なセル数
}

const SS はグローバル領域に置いた。そのために名称もアッパースネーク（大文字で繋げる。例　APPLE_PEN）としている。

getMaxRows()メソッド、getMaxColumns()メソッド、でも考えてみたいと思いつつ、予定が重なってできてない。気力があれば次のnoteでやってみたいと思いますー。

Quotas for Google Services　Googleサービスの割り当て

セル数最大500万ってどこに書いてあんの？ここ？

Quotas for Google Services  |  Apps Script  |  Google for Developers

こっちか？

Google ドライブに保管可能なファイル - Google ドライブ ヘルプ

公式ソースではないが、ここ↓で検証されている。

【Googleスプレッドシート】最大行数は最大どこまでいけるのか？を検証してみました | EGUWEB【Googleスプレッドシート】最大行数は最大どこまでいけるのか？を検証してみました

続く。続きたい。