Q&A
CSVが本来7列なのであれば、
元のCSVファイルのどれかの中に、カンマが含まれる値が入っているものはありませんか？
values.shift();
の後に、
if (values.some(v => v.length > 7)) {
console.log(file.getName());
}
の3行を挿入して実行すると、そのようなファイルがあった場合に実行ログでエラーの前にファイル名が表示されると思います。
実現したいこと
GASで下記の動きをさせたいです。
・特定のドライブに格納された複数のcsvデータを全て繋げてC列の日付順に昇順ソートをかけ、指定のスプレッドシートに転記したい。
前提
・格納するcsvは全て4行目までがタイトル行なので5行目以降（最終行はファイルにより異なる）のデータを転記したい。
・転記先のスプレッドシートは4行目までがタイトル行で、5行目からcsvデータを追加していきたい（上書きNG）
・処理が終わったcsvは処理済みのフォルダを格納するドライブフォルダに格納。
聞きたいこと
初心者なりにネットで調べながらスクリプトを作ってみました（なので下記コードの細かい意味を理解できていない部分もあります）
このスクリプトを動かすと、「Exception: データの列数が範囲の列数と一致しません。データは 10 列ですが、範囲は 7.列です。」というエラーが出ます。
エラーを直しつつ、実現したい動きをさせるにはどのように修正すればよろしいでしょうか？
該当のソースコード
GoogleAppsScript
1function EXCELtoSSAdd() { 2 3 const folder = DriveApp.getFolderById('xxxxxxxxxx');//Excelを格納したフォルダのID 4 const moveDir = DriveApp.getFolderById('xxxxxxxxxx');//使用済みフォルダ（上記フォルダ内） 5 const files = folder.getFiles(); 6 7 let allValues = []; 8 9 while (files.hasNext()) { 10 11 const file = files.next(); 12 const fileId = file.getId(); 13 14 const blob = DriveApp.getFileById(fileId).getBlob(); 15 const csv = blob.getDataAsString(); 16 const values = Utilities.parseCsv(csv); 17 values.shift(); 18 19 allValues = allValues.concat(values);//変数allValuesに各csvデータを追加 20 21 } 22 23 const sheet = SpreadsheetApp.openById('xxxxxxxxxx').getSheetByName('シート名'); 24 sheet.getRange(5, 1, allValues.length, allValues[0].length).setValues(allValues);//5行目A列からデータの最後尾行データの最後尾列にデータをセット 25 26 //シート内でソートしたいセル範囲をgetRangeで指定する 27 let data = sheet.getRange(5, 1, allValues.length, allValues[0].length); 28 //列Cを基準に降順でソートする 29 data.sort({column:3, ascending:true});//(ソートする列番号,降順か昇順か)true昇順、false降順 30 31 const moveFiles = folder.getFiles(); //フォルダ内のファイルを一括取得 32 33 for (let i = 0; moveFiles.hasNext(); i++) { 34 let moveFile = moveFiles.next(); 35 36 moveFile.moveTo(moveDir);//転記済みのファイルを処理済みフォルダに移動 37 38 } 39}
それと、
const file = files.next();
const fileId = file.getId();
const blob = DriveApp.getFileById(fileId).getBlob();
のところは、多分元はスプレッドシートをSpreadsheetAppで取得してそれをDriveAppで再取得していたコードの名残だと思いますが、最初からDriveAppでファイルを取得しているので、
const file = files.next();
const blob = file.getBlob();
でよろしいかと思います。
先日に引き続きありがとうございます。
確認したところ、元ファイルにカンマの含まれる列がありました。
転記する過程でcsvのカンマを消すことはできるのでしょうか？
それとも、csvにカンマが含まれている時点でエラーとなって処理が進まないのでしょうか？
例えば、
元のデータが
|A|B,C,D,E|F|G|H|I|J| の7列だったとしても
|A|B,C|D|E,F,G|H|I|J| の7列だったとしても
CSVにしたときに
A, B, C, D, E, F, G, H, I, J
と同じ10列になるので
CSVのうち、どの組み合わせが同じ列だったかは、
CSVだけを見ても判別できないですので、
カンマが含まれている列は元データの特定の列かどうか。
カンマが含まれる列のカンマの数は同じ数なのかどうか。
（例えば金額だと100と1,000と1,000,000ではカンマの数が違う）
カンマは削除するのかそのままで一つの要素にまとめるのか。
など、CSVにする前のデータの具体的な構造がわからないとなんとも言えないです。
この質問で続けるのであれば、
元データの構造などを具体的に記した質問に編集し直すか、
あるいは、
同様に具体的な内容を記して新たな質問としてみることをお勧めします。
通常は、CSVにする段階でカンマを処理すると思いますが、
それは検討できないのでしょうか？
詳しくありがとうございます。
こちらのcsvですが、ウェブ上のサービスでダウンロードできる集計結果でして、今回のツールを使用する方としては、手動で加工することなくダウンロードしてきたものをドライブに入れるだけにしたいとのことでした。
カンマは特定列だけで、桁数は変動ありなのでカンマの数はデータごとに異なります。
転記さえしてしまえばいいので、削除してもそのままでも問題ないです。
取り急ぎご回答いたしましたが、後ほど質問を編集しようかと思います！
> こちらのcsvですが、ウェブ上のサービスでダウンロードできる集計結果
そのようなCSVがカンマのために要素数が変わってしまうということもおかしいですね。
カンマ付きのデータが引用符で囲まれていたりはしないですか？
あるいは、元々10列のデータと7列のデータがあって、それらがあっていないだけだったというとはないのですね。
カンマの含まれる列があるというCSVは
1,2,3,"1,000,000",4,5,6
といった感じでダブルコーテーションで囲まれていたりするんですか？
それとも
1,2,3,1,000,000,4,5,6
というCSVだけど1,000,000は同じ列と言っているだけなのですか？
> そのようなCSVがカンマのために要素数が変わってしまうということもおかしいですね。
テスト用にcsvをいただくことができず、代わりに実施者がダウンロードしてきたcsvの内容をサンプルデータとしてスプレッドシートに貼られたものを私の方でcsvに貼り付けしてテストを実施していました。
ですので実施者が転記する際に、スプレッドシートのその列を数字表記にしてカンマがついてしまった可能性もありますので確認してみます。
> カンマ付きのデータが引用符で囲まれていたりはしないですか？
引用符などはないです。参考になるかわかりませんが、ファイルの種類はComma Separated SpreadSheet(.csv)です。
> 1,2,3,1,000,000,4,5,6
というCSVだけど1,000,000は同じ列と言っているだけなのですか？
ダブルクォーテーションはなく、後者になります。
Utilities.parseCSVは、引用符で囲まれると一つの要素にするので、多分バラバラになっているかと。
失礼しました！
他の形式で保存して確認してみたところ、該当列のデータは"10,000"のように囲まれておりました。
質問の意味を理解できておらずすみません。
> サンプルデータとしてスプレッドシートに貼られたものを私の方でcsvに貼り付けして
スプレッドシートからCSV形式でダウンロードするとカンマ付きの要素は引用符で囲まれるので、一つの列として扱われますよ。
今さらですが、2023/10/14 19:16の私のコメントは、YAmaGNZ様へのコメントでした。
> スプレッドシートからCSV形式でダウンロードするとカンマ付きの要素は引用符で囲まれるので、一つの列として扱われますよ。
こちら参考にさせていただきます。
カンマのエラーについては１つのファイルのみで起こる事象でしたので、実施者と相談してみます。
大変お手数ですが、もしわかる方いらっしゃいましたら、元質問のコードで思い通りの動作にするためのアドバイス等ありましたらご教示いただけますと幸いです。
参考までですが、
私のところでは動作しますが、
このスクリプトが希望どおりに動作しなくても
フォローアップは期待しないでください。
元の関数の
const valuea = Utilities.parseCsv(csv);
を
//次の行は、元の配列が7列で4列目がカンマ付きの列を１つにまとめるものです。
const values = repairParsedCsv(Utilities.parseCsv(csv), 7, 4 - 1);
としておき
次の新たな関数を保存します。
//カンマ付きデータで列数が増えている場合、その列の値をまとめる
//ただし、元のデータに空白を含む要素があると誤動作するので注意
//引数
// parsedCsv: 解析後の配列
// numColumns: 本来の列数
// index: カンマ付き列のインデックス（0始まり）
function repairParsedCsv(parsedCsv, numColumns, index) {
values = parsedCsv.map(v => {
let length = v.indexOf('');
length = length < 0 ? v.length : length;
const odd = length - numColumns;
if (odd) {
const result = [];
//カンマ付きの列の前まではそのまま格納
for (let i = 0; i < index; i++) {
result.push(v[i]);
}
//カンマ付きの列の最初の要素
let str = v[index];
//カンマ付きの列の残りの要素
for (let i = index + 1; i <= index + odd; i++) {
str += ',' + v[i];
}
//まとめた要素を格納
result.push(str);
if (index + odd < length) {
for (let i = index + odd + 1; i < length; i++) {
result.push(v[i]);
}
}
return result;
} else {
return v;
}
});
return values;
}
新たな関数は、
function EXCELtoSSAdd() {
...
...
}
の外側（最終行に追加するなど）にペーストしてください。
repairParsedCsv(Utilities.parseCsv(csv), 7, 4 - 1);
は、カンマがある列が2列目なら、
repairParsedCsv(Utilities.parseCsv(csv), 7, 2 - 1);
とします。
回答1件
0
ベストアンサー
コメントだとコピペしたときに、
スクリプトの改行がなくなってしまうようなので、
こちらにCSVを解析した配列のカンマ付きの要素をまとめるコードを含めたスクリプト全体を記載しておきます。
//次の行は、元の配列が7列で4列目がカンマ付きの列をまとめるものです。
const values = repairParsedCsv(Utilities.parseCsv(csv), 7, 4 - 1);
のところで、4 - 1の4のところでカンマ付きの列を指定します。
3列目なら3 - 1とします。
JavaScript
1function csvToSpreadsheet() { 2 //カンマ付きデータの対応 3 const folder = DriveApp.getFolderById(FOLDER_ID);//CSVを格納したフォルダのID 4 const moveDir = DriveApp.getFolderById(FOLDER_ID);//使用済みフォルダ（上記フォルダ内） 5 const files = folder.getFiles(); 6 let allValues = []; 7 while (files.hasNext()) { 8 const file = files.next(); 9 const blob = file.getBlob(); 10 const csv = blob.getDataAsString(); 11 12 //次の行は、元の配列が7列で4列目がカンマ付きの列をまとめるものです。 13 const values = repairParsedCsv(Utilities.parseCsv(csv), 7, 4 - 1); 14 15 values.splice(0, 4);//見出し行として4行を除外 16 allValues = allValues.concat(values);//変数allValuesに各csvデータを追加 17 } 18 const sheet = SpreadsheetApp.openById(SS_ID).getSheetByName(SHEET_NAME); 19 //スプレッドシートにデータを追記 20 sheet.getRange(sheet.getLastRow() + 1, 1, allValues.length, allValues[0].length).setValues(allValues); 21 //見出し行を除くシート内データを範囲指定する。 22 let data = sheet.getRange(5, 1, sheet.getLastRow() - 5 + 1, sheet.getLastColumn()); 23 //列Cを基準に降順でソートする 24 data.sort({ column: 3, ascending: true });//(ソートする列番号,降順か昇順か)true昇順、false降順 25 const moveFiles = folder.getFiles(); //フォルダ内のファイルを一括取得 26 for (let i = 0; moveFiles.hasNext(); i++) { 27 let moveFile = moveFiles.next(); 28 moveFile.moveTo(moveDir);//転記済みのファイルを処理済みフォルダに移動 29 } 30} 31//カンマ付きデータで列数が増えている場合、その列の値をまとめる 32//ただし、元のデータに空白を含む要素があると誤動作するので注意 33//引数 34// parsedCsv: 解析後の配列 35// numColumns: 本来の列数 36// index: カンマ付き列のインデックス（0始まり） 37function repairParsedCsv(parsedCsv, numColumns, index) { 38 values = parsedCsv.map(v => { 39 let length = v.indexOf(''); 40 length = length < 0 ? v.length : length; 41 const odd = length - numColumns; 42 if (odd) { 43 const result = []; 44 //カンマ付きの列の前まではそのまま格納 45 for (let i = 0; i < index; i++) { 46 result.push(v[i]); 47 } 48 //カンマ付きの列の最初の要素 49 let str = v[index]; 50 //カンマ付きの列の残りの要素 51 for (let i = index + 1; i <= index + odd; i++) { 52 str += ',' + v[i]; 53 } 54 //まとめた要素を格納 55 result.push(str); 56 //残りの要素を格納 57 if (index + odd < length) { 58 for (let i = index + odd + 1; i < length; i++) { 59 result.push(v[i]); 60 } 61 } 62 return result; 63 } else { 64 //先頭からnumColumn個の要素を格納 65 const result = []; 66 for (let i = 0; i < numColumns; i++) { 67 result.push(v[i]); 68 } 69 return result; 70 } 71 }); 72 return values; 73}
投稿2023/10/15 02:20
編集2023/10/15 05:00総合スコア861
改めてありがとうございます。
実行すると以下のエラーが出て、2つのcsvをドライブに入れて試したうちの1つのデータがSS５行目から転記されましたが、csvの削除したかったタイトル行の2~4行目も転記されており、もう1つのデータは1つ目のデータから4行空白行を挟んでタイトル行の2~3行目のみ転記されてデータは転記されておりませんでした。
転記したいcsvのタイトル行削除の部分がうまくいっていないのでしょうか？
Exception: The number of columns in the data does not match the number of columns in the range. The data has 10 but the range has 7.
まず、
現在のスクリプトは、
以前からCSVの見出し行は1行となっていることを前提としています。
（私は、説明が違っているだけかと思っていました。）
上のコードの15行目で見出し行を除外する部分が、
values.shift();//見出し行を除外
となっているのを
values.splice(0, 5);//見出し行として5行を除外
に修正すると5行削除します。
次に、
エラーの件ですが、
やはりCSVから解析した配列を修復してもなお10列のままの部分があるということですね。
転記されたものがあるのであれば、
転記されなかった方のCSVの中身をご確認ください。
転記されたCSVと何か違いがあれば、お教えください。
こちらで色々なデータで試してみて一部動作を修正してみました。
回答が修正後のスクリプトになっていますので、お手数ですがもう一度お試しください。
（追記）
見出し行は4行でしたのでそこを再修正しました。
さらに、デバッグ用のコードも取り除きました。
何から何までありがとうございます！
転記されたデータと転記されなかったデータの違いはデータ数（行数）と、それぞれのデータの値が違うくらいです。
修正版を試してみたところ、転記に至るまでの動作はエラーも出ず完璧でした。
あと気になる点として１つあるのが、転記された内容は問題ないのですが、貼り付け先スプレッドシートのデータが上書きで転記される部分だけ直したいです。
貼り付け先は5行目以降にデータが入っており、どんどん継ぎ足していく形で転記したいと思っています。
追記後のB列でのソートは、追記分だけですか。
それとも
5行目以降のデータ全てですか。
追記分も含め5行目以降のデータ全てをソートいたします。
IDや列番号を修正する方が、
コードを修正するより間違いないと思うので、
回答を修正しました。
ありがとうございます！
おかげさまで全て思い通りの動きにすることができました！
長い時間、ご丁寧にお付き合いいただき大変感謝しております。
こちら解決報告をした後ですので、もしご回答していただければで構いません。
32行目に、空白がある場合誤動作をすると書かれていますが、これはなぜ起こるのでしょうか？
実施者から空白が含まれる場合もあると言われまして、ここも修正していこうと思い、質問させていただきました。
もう少し改良することは可能ですが、
例えば7列の要素が、
|A|B|C|1,000,000,000円|E|F|G|のとき、
Utilities.ParseCsvで解析後の配列は、
[A, B, C, 1, 000, 000, 000円, E, F, G]
と10の要素に分かれますが、
|A|B|C|1,000,000円|E|F|G|のときは、
[A, B, C, 1, 000, 000円, E, F, G, '']
|A|B|C|1,000円|E|F|G|のときは、
[A, B, C, 1, 000円, E, F, G, '', '']
と全ての要素の数を最大の要素数である10に揃えるために
空白を補いますので、
提示してあるスクリプトでは、
配列を先頭から検査して空白が出現した時点で
本来の要素数を判断しています。
このため、途中に空白があると要素数を誤認してしまうのです。
もう少しの改善とは、39-40行目の
let length = v.indexOf('');
length = length < 0 ? v.length : length;
を
let length = v.findLastIndex(i => i != '');
length = length < 0 ? v.length : length + 1;
として最後から検査して空白以外が出現した時点で
データの数を判断するように変更することで
一部を回避できますが、
カンマが1つ含まれていて最後が空白となっているデータ
|A|B|C|1,000円|E|F|''|は、
[A, B, C, 1, 000円, E, F, '', '', '']となり、
カンマも空白も含まれていない通常のデータ
|A|B|C|100円|E|F|G|も
[A, B, C, 100円, E, F, 'G', '', '', '']となり
どちらも空白以外の最後の要素のインデックスが6となるので
区別がつかなくなり、誤動作します。
最後の空白がないということなら、
上記の修正で回避は可能です。
詳しく説明していただきありがとうございます！
理解できました！
空白は最後につくこともあるし、複数の列で存在することもあるので、少しの改良で全てはカバーできそうにないですね…
やはりカンマ付きのデータを引用符で囲むように加工するのが間違いないと思います。
そうなりますよね…
これはスクリプト内に組み込むのではなく手動でやるしかない部分でしょうか？
CSVの作成時の処理ですから、そこをGASで処理できるなら可能性はあると思います。
CSVの作成についてGASでの処理が可能で、新たに質問できるのであれば、適切な回答が得られるかもしれません。
ご回答ありがとうございました！
諸々検討してみます！
あなたの回答
tips
プレビュー
