CSVファイルを、GASを使って一括でスプシ書き込む｜make活用

ふじわらこうすけ

2023年8月27日 17:04

CSVファイルをGoogle Spleadsheetなどで取り扱おうとした場合、インポート機能を使えば取り込むことができますよね！

ただし、iPaaSツールなどを使って取り込む場合には少し工夫が必要です。
まともにやろうとすると、コストが膨大になってしまうこともあるので、ぜひ試してみてください。

CSVファイルをスプレッドシートへそのまま書き込んでみる

サンプルファイルをChatGPT先輩に作っていただきました。
こういうサンプルデータを用意する時にも活用できてめちゃ便利ですね。

ついでにCode Interpreterを使って、CSVファイルとして保存してもらいました。

Google DriveにCSVファイルを置いて、スプレッドシートへ突っ込んでみる。

GoogleDriveからCSVファイルを落として、スプレッドシートのA1のセルへ突っ込んでみましょう。

当たり前ですが、A1のセルに全てのデータが入ってしまいました。

CSVファイルを改行ごとに分解し、1行ずつ書き込む

正攻法としては、CSVファイルを改行ごとに分解し、１行ずつスプレッドシートに書き込む方法があります。

CSVファイルを分解し（今回のファイルであれば30行分あるので30行に分解）、1行ずつ書き込むのを30回繰り返す仕様になっています。

結果としては成功ですね！
しっかりと1行、1列ずつにデータが格納されています。

ただ、一つ問題があります。
makeのオペレーション数が、CSVファイルの行数に依存してしまうことです。
今回であれば、書き込む作業だけで31オペレーションを使ってしまっています。
まだ30ほどであれば可愛らしいものですが、10000行あるCSVファイルとかだと毎日使うにしては高いですよね。

GASを使って分解する

今日の本題はこっちです！
使用するシナリオはこちら。

そうです。最初と一緒です。
遠回りさせてごめんなさい。

ChatGPTにスクリプトを書いてもらう

プロンプトはシンプルにこんな感じ。

function parseCsvInA1() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // A1セルの内容を取得
  var csvData = sheet.getRange("A1").getValue();
  
  // CSVデータを行に分割
  var rows = csvData.split("\n");
  
  // 各行を列に分割して2次元配列を作成
  var data = [];
  for (var i = 0; i < rows.length; i++) {
    data.push(rows[i].split(","));
  }
  
  // 2次元配列のデータをシートに書き込む
  sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}

GASにスクリプトを貼り付ける

トリガーを設定

※詳細の進め方についてつまづくことがあれば、こちらの記事を参照してください。

シナリオを実行してみる

GIFで実際の動きをご覧ください。

一瞬、A1のセルに全てのデータが入りますが、その後、分解されて各行列に書き込まれているのがわかります。（無編集です）

スクリプトの解説

関数の定義:

function parseCsvInA1() {

`parseCsvInA1`という名前の関数を定義しています。

アクティブなシートの取得:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

Google Apps Scriptの`SpreadsheetApp`クラスを使用して、現在アクティブなスプレッドシートのアクティブなシートを取得しています。

A1セルの内容の取得:

var csvData = sheet.getRange("A1").getValue();

A1セルの内容（ここではCSVデータと想定）を取得しています。

CSVデータを行に分割:

var rows = csvData.split("\n");

取得したCSVデータを、改行コード(`\n`)で分割して、各行を要素とする配列`rows`を作成しています。

各行を列に分割して2次元配列を作成:

var data = [];
for (var i = 0; i < rows.length; i++) {
  data.push(rows[i].split(","));
}

`rows`配列の各行をカンマ(`,`)で分割して、2次元配列`data`を作成しています。これにより、CSVの各セルのデータが`data`の各要素として格納されます。

2次元配列のデータをシートに書き込む:

sheet.getRange(1, 1, data.length, data[0].length).setValues(data);

2次元配列`data`の内容を、Google スプレッドシートの適切なセル範囲に書き込んでいます。`getRange`メソッドは、開始行、開始列、行数、列数を引数として取り、指定された範囲のセルを返します。`setValues`メソッドは、その範囲のセルに2次元配列の内容を書き込みます。