JavaScriptとGoogle Apps Scriptを使用してスプレッドシートの日付のセルのデータの取得方法が知りたいです。

Question

実現したいこと

JavaScriptとGoogle Apps Scriptを使用して、スプレッドシートの1行分のデータを配列で取得してreturnで返り値を取る様に作成しましたが、
スプレッドシートのセル内に日付のデータが含まれていると、返り値がnullになるみたいなので対応策が有れば教えて下さい。

前提

javascriptとGoogle Apps Scriptを使用して、
スプレッドシートの1行分のデータを配列としてreturnの返り値になる様に作成しました。

発生している問題・エラーメッセージ

1行分のデータの内容によって、
返り値が配列で帰ってくる場合と、nullになる場合が有りました。
nullになる場合のデータをいくつか確認して共通点を探すと、
セル内のデータに2023/5/31という風に日付が入っていました。
return直前の1行分のデータをログで確認すると、以下の様になっていました。
恐らく2023/5/31の箇所が、シングルクォーテーションで囲っていない為に、
エラーになりnullで返ってきたのかな？と素人ながらに思っています。

[ 'aaa',
'bbb',
'',
'ddd',
Wed May 31 2023 00:00:00 GMT+0900 (日本標準時),
'' ]

Comments

[YellowGreen]

ご自身で作成したスクリプトの修正方法を回答に求めているのであれば、
質問は修正可能ですので、質問内容にご自身で作成したスクリプトを記入してください。

日付セルも文字列のセルも含め、1行分データを取得するときは、
const values = sheet.getRange(row, column, 1, numColumns).getValues()[0];
（row:取得対象の行番号、column:取得開始列、numColumns:取得する列数）
でvaluesに数値、文字、日付などのセルの値の配列が入ります。（最後の[0]は、.flat()とすることもあります。）
ご自身で確認したとおり、配列内の要素である日付をそのままログ表示するとWed May 31 2023 00:00:00 GMT+0900のような表示になります。
ですから、質問者様は、配列として日付の値は取得できていると思いますので、
1行の値を取得した配列をreturnしていて、
その配列がご提示のように要素が確認できるのにnullになるのは
その配列をどのように処理してnullを確認しているのでしょうか。

[TomJerry]

YellowGreenさん

お返事ありがとうございます。
社内のスクリプトなので抜粋部分のみでかつ一部修正したものになります。
以下のconsole.logは質問で記載したログの箇所になります。
nullを確認したのはJavaScript側で受け取った値をconsole.logで表示した時になります。

const DBA = sheetA.getRange(2,1,LastRow,LastColumn).getBalues();
const DBB = sheetB.getRange(2,1,LastRow,LastColumn).getBalues();
const DBC = sheetC.getRange(2,1,LastRow,LastColumn).getBalues();

for(let i=0;i<DBA.length;i++){
DBA[i].unshift('AAA');
}
for(let i=0;i<DBB.length;i++){
DBB[i].unshift('BBB')
}
for(let i=0;i<DBC.length;i++){
DBC[i].unshift('CCC')
}
const DB = DBA.concat(DBB).concat(DBC);

for(let i=0;i<DB.length;i++){
if(DB[i]==='一致'){
console.log(DB[i]);
return DB[i];
}
}

[YellowGreen]

加工しているためでしょうか？
if(DB[i]==='一致'){
console.log(DB[i]);
return DB[i];
}
の部分は、
DB[i]と文字列とを比較していますでしょうか？
DB[i]は次のような二次元配列DB
[
['AAA', A2セルの値, B2セルの値, C2セルの値, ...]
['AAA', A3セルの値, B3セルの値, C3セルの値, ...]
...
...
['BBB', A2セルの値, B2セルの値, C2セルの値, ...]
['BBB', A3セルの値, B3セルの値, C3セルの値, ...]
...
...
['CCC', A2セルの値, B2セルの値, C2セルの値, ...]
['CCC', A3セルの値, B3セルの値, C3セルの値, ...]
...
...
]
からiを0から1ずつ増やしながら各行の配列DB[i]を順に取り出していますので、
例えば i が3のときは、
['AAA', A5セルの値, B5セルの値, C5セルの値, ...]
がDB[i]の値になります。
これと文字列 '一致' とを比較してもfalseになルので
return文は実行されないと思うのですが、
こちらのconsole.logで上のようなDB[i]の値がログに表示されているとすれば、
if(DB[i] === '一致') {
ではなく
if(DB[i][8] === '一致') {// H列のセルの値が「一致」のとき
などのようにDBに[i]ともう一つ[...]がついているのでしょうか。

[TomJerry]

YellowGreenさん

すいません。
記述ミスです。

変数 iで回した後に、変数jでさらに回しています。
お手間を取らして申し訳ありません。
YellowGreenさんがおっしゃっている加工しているためでしょうか？という部分が気になりますので、
後程加工せずにgetValuesを行ってみて確認してみます。

for(let i=0;i<DB.length;i++){
if(DB[i]==='一致'){
for(let j=i;j<DB.length;j++){
if(DB[i][j]==='さらに一致'){
console.log(DB[i]);
return DB[i];
}
}
}
}

[YellowGreen]

DB[i]を直接文字列と比較しているのであれば、
最初のforループの中の
if(DB[i] === '一致') {
は、上で説明したように必ずfalseになりますので
次のforループは実行されませんよ。
DBに[...]が一つしかついていないと配列との比較になってしまいます。

仮にそのようなコードになっているとすれば、
retuenで値が帰らないので
戻り値を参照している関数の方の変数の値はundefinedになっていると思います。

[TomJerry]

YellowGreenさん

最初のforループの中のif文ですが、記述ミスしていました。
if(DB[i][1]==='一致')となっています。

[YellowGreen]

そうでしたか。
A列のセルの値と比較していたのですね。
それで2番目のforループでいずれかの列に'さらに一致'と一致するセルがあれば
その行のデータDB[i]を返しているのですね。

ところで、2番目のforループですが、
さらに一致をA列のセルの値が「一致」である行のうち
他のいずれかのセルの値が「さらに一致」である行を抽出したいのであれば、

for(let j=i;j<DB.length;j++){

は

for(let j = 2; j < DB[i].length; j++) {//B列以降の列の値を確認
（j = 2はB列以降、j < DB[i].lengthとすることでB列以降のすべての値を確認できます）
ではないでしょうか。
j < DB.lengthでは列の値を確認したいのに列数でなく行数だけ繰り返しという変なコードになります。

[TomJerry]

全てのスクリプトをペタッと貼り付けて説明すれば簡単なんですが、それが出来ず申し訳ありません。

A列で一致するもので、次はA列と同じものをB列を検索するという形にしているのです。
一応今動いているこのコードで、スプレッドシートのデータの1行のデータを引っ張ってこれるものは引っ張ってこれています。
セル内に日付のデータがある1行がnullになる対策が分かればいいなと思っています。

[YellowGreen]

配列として取得して配列のままリターンしているのであれば、
その要素が日付だとしても配列がnullになることはないので、
例えば、現在のシートの値から配列を返している関数名が
function xxxxXXXX() {
だとすると

スプレッドシートのスクリプトエディタで
新たに
function test01() {
const response = xxxxXXXX();
console.log(response);
}
というテスト用の関数を作成して
test01をエディタで実行してみて
日付が含まれている行を抽出したログがnullになっているのか
配列のまま帰ってきているのかを確認することができます。

配列が返ってきているのであれば、
少なくともそのスクリプトは正常に動作していることになります。

[YellowGreen]

それから、スクリプトをぺたっと貼り付けた上で、
秘匿したい部分をXXXXなどの伏字にするということは
できますか？
ロジックがどうなっているのかがわかるように。
出来るのであれば、質問を修正して質問欄にアップできるといいのですが。

[YellowGreen]

念のためですが、
nullとはログにnullと表示されるということですよね。
[]（空の配列）とかではないですよね。

[YellowGreen]

状況を把握できましたので、回答欄でお答えしました。長々とやり取りをお願いしてしまい恐縮です。

Answer 1

状況がわかりました。
コメント欄で何度もやり取りをお願いしてしまい恐縮です。

GASのコードの問題というよりは、受け渡しの方法のようでした。
JavaScript側で日付のデータが入っている配列をGASから受けてもnullとなってしまうのは、
要素が日付オブジェクトのままで受け渡しを行っているからのようです。

一つの方法としては、

return DB[i]

を

for (let value of DB[i]) {
  if (Object.prototype.toString.call(value) == "[object Date]") {
    value = Utilities.formatDate(value, 'JST', 'yyyy/MM/dd');
  }
}
return DB[i];

として日付を文字列にしてから渡す方法

別な方法としては、
セルの値を取得する

const DBA = sheetA.getRange(2,1,LastRow,LastColumn).getValues();

などを

const DBA = sheetA.getRange(2,1,LastRow,LastColumn).getDisplayValues();

などのようにして最初から全てのデータを文字列として取得する方法です。
全てが文字列で構わなければ、たぶんこれが一番簡単です。

JavaScript側では、受け取った配列（仮にitemsとします）を

for (let value of items) {
  if(typeof(value) == 'string' && value.match(/^\d{4}\/\d{2}\/\d{2}$/)) {
    value = new Date(value);
  }
}

として文字列から日付に戻します。
文字列のままで構わないのであればそのままで。

あと、

const jsonStr = JSON.stringify(DB[i]);
return jsonStr;

としてJSON文字列で渡してから、
JavaScript側で

items = JSON.parse(jsonStr);
for (let value of items) {
  if(typeof(value) == 'string' && value.match(/^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\.\d{3}Z$/)) {
    value = new Date(value);
  }
}

として配列に戻してから日付のJSON文字列を
日付オブジェクトに戻す方法もあります。

Comments

[TomJerry]

YellowGreenさん

おはようございます。
昨日遅くまで何度も投稿ありがとうございました。
今朝getValuesからgetDisplayValuesに変更する事で、
nullにはならずスプレッドシート1行まるまるのデータを取得する事が出来ました！

こんなメソッドがあるんですね。知りませんでした。

又、他の手段もいろいろと投稿して頂きありがとうございました。
次に生かせる貴重な資料として活用させて頂きます。

この度は何度もお付き合い頂き感謝致します！！

[YellowGreen]

お役に立てて良かったです。
状況把握に時間がかかってしまいお手数をおかけしました。