スプレッドシートとCSVファイルについて

## スプレッドシートでGoogle DriveのCSVファイルを取得・表示する

### 概要

このGASコードは、Google Drive内の全てのCSVファイルを検索し、その内容をスプレッドシートに表示します。ボタンクリックとトリガーの両方に対応しています。

### コード

js

  

/**

 * Google Drive内のCSVファイルを取得し、スプレッドシートに表示します。

 */

function displayCSVFiles() {

  // スプレッドシートを取得

  const ss = SpreadsheetApp.getActiveSpreadsheet();



  // Google Driveを検索

  const files = DriveApp.searchFiles('mimeType = "text/csv"');



  // ファイルが見つかった場合

  if (files.hasNext()) {

    // シートを取得または作成

    let sheet = ss.getSheetByName('CSV Data');

    if (sheet) {

      sheet.clearContents();

    } else {

      sheet = ss.insertSheet('CSV Data');

    }



    // ヘッダー行を追加

    sheet.appendRow(['ファイル名', 'データ']);



    // 各CSVファイルの内容を取得・表示

    let rowNum = 2;

    while (files.hasNext()) {

      const file = files.next();

      const fileName = file.getName();

      const csvData = Utilities.parseCsv(file.getBlob().getDataAsString());



      // 各行をシートに追加

      csvData.forEach(rowData => {

        sheet.getRange(rowNum, 1).setValue(fileName);

        sheet.getRange(rowNum, 2, 1, rowData.length).setValues([rowData]);

        rowNum++;

      });

    }

  } else {

    // ファイルが見つからなかった場合

    SpreadsheetApp.getUi().alert('CSVファイルが見つかりませんでした。');

  }

}



/**

 * ボタンクリック時に実行する関数

 */

function onOpen() {

  const ui = SpreadsheetApp.getUi();

  ui.createMenu('CSVファイル')

    .addItem('CSVファイルを表示', 'displayCSVFiles')

    .addToUi();

}



/**

 * トリガーを設定する関数

 */

function setupTrigger() {

  // 既存のトリガーを削除

  const triggers = ScriptApp.getProjectTriggers();

  triggers.forEach(trigger => {

    if (trigger.getHandlerFunction() === 'displayCSVFiles') {

      ScriptApp.deleteTrigger(trigger);

    }

  });



  // 新しいトリガーを作成

  ScriptApp.newTrigger('displayCSVFiles')

    .timeBased()

    .everyHours(1) // 1時間ごとに実行

    .create();

}

### 説明

1. **`displayCSVFiles()`関数:**
- アクティブなスプレッドシートを取得します。
- Google DriveでMIMEタイプが"text/csv"のファイルを検索します。
- ファイルが見つかった場合:
- "CSV Data"という名前のシートを取得または作成します。
- ヘッダー行("ファイル名", "データ")を追加します。
- 各CSVファイルの内容を`Utilities.parseCsv()`でパースし、シートに追加します。
- ファイルが見つからなかった場合:
- アラートメッセージを表示します。

2. **`onOpen()`関数:**
- スプレッドシートを開いたときに、"CSVファイル"というメニューを作成し、"CSVファイルを表示"という項目を追加します。
- この項目をクリックすると、`displayCSVFiles()`関数が実行されます。

3. **`setupTrigger()`関数:**
- 既存の`displayCSVFiles()`のトリガーを削除します。
- 1時間ごとに`displayCSVFiles()`関数を実行する新しいトリガーを作成します。

### 利用方法

1. スプレッドシートを開き、[ツール] > [スクリプトエディタ] をクリックします。
2. 上記のコードをスクリプトエディタにコピー＆ペーストします。
3. `setupTrigger()`関数を実行してトリガーを設定します。
4. スプレッドシートに"CSVファイル"メニューが表示され、"CSVファイルを表示"をクリックするとGoogle Drive内のCSVファイルの内容が表示されます。

### 注意点

- このコードはGoogle Drive内の**全ての**CSVファイルを取得します。特定のフォルダ内のファイルのみを取得したい場合は、`DriveApp.searchFiles()`のクエリを変更する必要があります。
- トリガーの実行間隔は`setupTrigger()`関数内の`.everyHours(1)`の部分で調整できます。
- スプレッドシートに大量のデータを表示する場合は、処理に時間がかかる場合があります。

javacsript

 

/**

 * 指定フォルダ内のCSVファイルを全て取得し、スプレッドシートに表示する

 * 

 * @param {string} folderId - CSVファイルが格納されているフォルダのID

 */

function importCsvsFromFolder(folderId) {

  // スプレッドシートを取得

  let ss = SpreadsheetApp.getActiveSpreadsheet();



  // フォルダを取得

  let folder = DriveApp.getFolderById(folderId);



  // フォルダ内のCSVファイルを全て取得

  let files = folder.getFilesByType(MimeType.CSV);



  // ファイルを一つずつ処理

  while (files.hasNext()) {

    let file = files.next();

    let fileContent = file.getBlob().getDataAsString();



    // CSVデータを二次元配列に変換

    let csvData = Utilities.parseCsv(fileContent);



    // 新しいシートを作成

    let sheet = ss.insertSheet(file.getName());



    // データをシートに書き込み

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

  }

}

**使用方法:**

1. スクリプトエディタ(`ツール` > `スクリプトエディタ`)を開きます。
2. 上記のコードをコピーして貼り付けます。
3. `folderId` を、CSVファイルが格納されているフォルダのIDに置き換えます。
- フォルダのIDは、フォルダのURLの `https://drive.google.com/drive/folders/` の後に続く文字列です。
4. スクリプトを実行します(`実行` > `importCsvsFromFolder`)。
- 初回実行時は、スクリプトがアカウントへのアクセス許可を求める場合があります。

**注意事項:**

* このスクリプトは、指定されたフォルダ内の **CSVファイルのみ** を処理します。
* 新しいシート名は、CSVファイル名と同じになります。
* 既存のシートと同じ名前のファイルがある場合、新しいシートが作成され、既存のシートは上書きされません。
* 大量のCSVファイルを処理する場合、処理に時間がかかることがあります。

**機能拡張:**

* 特定のシートにデータをまとめたい場合は、`insertSheet()` の代わりに `getSheetByName()` を使用して、既存のシートを取得できます。
* ヘッダー行の有無や、データの開始行を調整する必要がある場合は、`Utilities.parseCsv()` のオプションを使用できます。
* エラー処理を追加して、スクリプトが予期せず終了しないようにすることができます。

文字化けの原因は、CSVファイルの文字コードと、GASがデータを読み込む際の文字コードが一致していないためと考えられます。

下記のようにコードを修正することで文字化けを解消できます。

js

  

function importCsvsFromFolder() {



  // スプレッドシートを取得

  let ss = SpreadsheetApp.getActiveSpreadsheet();



  // フォルダを取得

  // フォルダIDは実際のものを指定してください

  let folder = DriveApp.getFolderById('test'); 



  // フォルダ内のCSVファイルを全て取得

  let files = folder.getFilesByType(MimeType.CSV);



  // ファイルを一つずつ処理

  while (files.hasNext()) {

    let file = files.next();



    // Blobを取得

    let blob = file.getBlob();



    // 文字コードをShift-JISに変換 (CSVファイルの文字コードに合わせてください)

    blob.setContentType('application/vnd.ms-excel;charset=Shift_JIS'); 



    // 文字コードを指定してCSVデータを取得

    let fileContent = blob.getDataAsString('Shift_JIS'); 



    // CSVデータを二次元配列に変換

    let csvData = Utilities.parseCsv(fileContent, ','); 



    // 新しいシートを作成

    let sheet = ss.insertSheet(file.getName());



    // データをシートに書き込み

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

  }

}

主な変更点：

* `file.getBlob().getDataAsString()` を `blob.getDataAsString('Shift_JIS')` に変更し、文字コードを指定してデータを取得するようにしました。
* `Utilities.parseCsv()` の第二引数に区切り文字 ',' を指定しました。

**注意点:**

* CSVファイルの文字コードがShift-JIS以外の場合は、`blob.setContentType()` と `blob.getDataAsString()` の文字コードを適切なものに変更してください。
* 上記のコードはカンマ区切りのCSVファイルを想定しています。タブ区切りの場合は `Utilities.parseCsv(fileContent, 't')` のように区切り文字を変更してください。


これで文字化けが解消されるはずです。もし、まだ文字化けが発生する場合は、CSVファイルの文字コードを再度確認してみてください。

javacsript

function importCsvsFromFolder() {



  // スプレッドシートを取得

  let ss = SpreadsheetApp.getActiveSpreadsheet();



  // フォルダを取得

  let folder = DriveApp.getFolderById('test'); // フォルダIDは適宜変更



  // フォルダ内のCSVファイルを全て取得

  let files = folder.getFilesByType(MimeType.CSV);



  // ファイルを一つずつ処理

  while (files.hasNext()) {

    let file = files.next();

    let fileName = file.getName();



    // スプレッドシート内のシートをループ

    let sheets = ss.getSheets();

    for (let i = 0; i < sheets.length; i++) {

      let sheet = sheets[i];

      let sheetName = sheet.getName();



      // ファイル名とシート名が部分一致する場合

      if (fileName.indexOf(sheetName) !== -1) { 

        let blob = file.getBlob();



        // 文字コードをShift-JISに変換 (CSVファイルの文字コードに合わせてください)

        blob.setContentType('application/vnd.ms-excel;charset=Shift_JIS'); 



        // 文字コードを指定してCSVデータを取得

        let fileContent = blob.getDataAsString('Shift_JIS'); 



        // CSVデータを二次元配列に変換

        let csvData = Utilities.parseCsv(fileContent, ','); 



        // 既存のデータを全て削除

        sheet.clearContents();



        // データをシートに書き込み

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



        // 一致するシートが見つかったらループを抜ける

        break;

      }

    }

  }

}

**変更点:**

1. **シート名との部分一致判定を追加:**
- `fileName.indexOf(sheetName) !== -1` でファイル名にシート名が含まれているかを確認しています。
2. **既存データの削除:**
- `sheet.clearContents();` でシート内の既存データを削除してからCSVデータを追加するように変更しました。
3. **シートループ:**
- スプレッドシート内の全てのシートに対して、ファイル名との一致を確認するようにループ処理を追加しました。
4. **一致後の処理:**
- 一致するシートが見つかったら、`break;` でシートのループを抜け、次のファイルの処理に移るようにしました。


**注意点:**

- `folder.getFolderById('test')` の `'test'` 部分は、CSVファイルが格納されているフォルダのIDに置き換えてください。
- `blob.setContentType('application/vnd.ms-excel;charset=Shift_JIS')` の `'Shift_JIS'` 部分は、CSVファイルの文字コードに合わせて適宜変更してください。


このコードを実行すると、指定したフォルダ内のCSVファイルが、シート名と部分一致するシートに読み込まれ、既存のデータは上書きされます。

## GASの実行時間と終了時間をスプレッドシートに記録する方法

GASでスクリプトの実行時間と終了時間を計測し、スプレッドシートの指定セルに記録するには、以下の手順で実現できます。

**1. スプレッドシートの準備**

* 実行時間と終了時間を記録するスプレッドシートを作成します。
* 実行時間用にセルA1、終了時間用にセルB1を割り当てます。（任意のセルで構いません）

**2. GASコードの記述**

javacsript

function myFunction() {

  // --- 時間計測開始 ---

  const startTime = new Date(); 



  // スプレッドシートとシートを取得

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const sheet = ss.getSheetByName('シート名'); // シート名を変更



  // 実行時間を記録

  sheet.getRange('A1').setValue(startTime);



  // --- スクリプトの処理内容 ---

  // ここに処理内容を記述



  // --- 時間計測終了 ---

  const endTime = new Date();



  // 終了時間を記録

  sheet.getRange('B1').setValue(endTime);

}

**解説**

* **スプレッドシートとシートの取得:** `SpreadsheetApp.getActiveSpreadsheet()`で現在開いているスプレッドシートを取得し、`getSheetByName('シート名')`で指定したシートを取得します。シート名は実際のシート名に変更してください。
* **時間計測:** `new Date()`で現在時刻を取得します。開始時に`startTime`に、終了時に`endTime`にそれぞれ格納します。
* **セルへの書き込み:** `sheet.getRange('セル').setValue(値)`で指定したセルに値を書き込みます。

**実行結果**

スクリプトを実行すると、指定したスプレッドシートのセルA1にスクリプトの実行開始日時が、セルB1に終了日時がそれぞれ「年月日時分秒」で記録されます。

**その他**

* 実行時間だけを計測したい場合は、`endTime`から`startTime`を引いた値を計算し、任意の形式で表示することができます。
* スクリプトの実行時間が長くなる場合は、トリガーを使って定期的に実行することを検討してください。

可能です。IMPORTRANGE関数と、Googleスプレッドシートの他の関数と組み合わせることで、 同じ商品名だけを合計することができます。 具体的な手順は以下の通りです。

**1. データの構造を確認する**

まず、両方のスプレッドシートの商品名と数量がどの列に記載されているかを確認してください。 例えば、シートAのA列に商品名、B列に数量、シートBのA列に商品名、B列に数量があるとします。

**2. IMPORTRANGE関数でデータを取得する**

シートAで、シートBのデータを取得したいセル範囲に、以下の関数を入力します。


=IMPORTRANGE("シートBのURL", "シート名!A:B")


* "シートBのURL"は、データを取得したいシートBのURLに置き換えてください。
* "シート名!A:B" は、シートBの商品名と数量を含む列範囲に置き換えてください。

**3. QUERY関数で商品名でグループ化して合計値を計算する**

シートAで、合計値を表示したいセルに以下の関数を入力します。


=QUERY(IMPORTRANGE("シートBのURL", "シート名!A:B"), "SELECT Col1, SUM(Col2) WHERE Col1 <> '' GROUP BY Col1 LABEL SUM(Col2) '合計'", 1)


* "Col1"はシートBの商品名がある列番号、 "Col2"は数量がある列番号に置き換えてください。 上記の例では、商品名がA列、数量がB列なのでそのまま利用します。
* この関数は、シートBから取得したデータに対して、商品名（Col1）でグループ化を行い、各商品名の数量（Col2）を合計します。

**4. SUMIF関数を使ってシートAのデータと合計する**

シートAで、最終的な合計値を表示したいセルに以下の関数を入力します。


=SUMIF(A:A,C:C,B:B) + SUMIF(C:C,A:A,D:D)


* この関数は、シートAの商品名とシートBから取得した商品名を照らし合わせ、一致する商品名の数量を合計します。
* C列はIMPORTRANGE関数で取得したシートBの商品名、D列はQUERY関数で計算したシートBの商品ごとの合計値が格納されているとします。

**注意点**

* スプレッドシートのURLやシート名、列番号などは、実際のデータに合わせて適宜変更してください。
* IMPORTRANGE関数を使うには、事前にアクセス許可を与える必要があります。


これらの手順で、IMPORTRANGE関数を使って異なるスプレッドシートから同じ商品名の数量を合計することができます。

javacsript

 

/**

 * 指定したスプレッドシートの特定のシートの特定の列の値を全て取得します。

 *

 * @param {string} spreadsheetId - データを取得するスプレッドシートのID

 * @param {string} sheetName - データを取得するシートの名前

 * @param {number} columnIndex - データを取得する列のインデックス (1始まり)

 * @return {Array} 指定された列の値の配列

 */

function getColumnValues(spreadsheetId, sheetName, columnIndex) {

  // スプレッドシートを取得

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const targetSpreadsheet = SpreadsheetApp.openById(spreadsheetId);



  // シートを取得

  const sheet = targetSpreadsheet.getSheetByName(sheetName);



  // 最終行を取得

  const lastRow = sheet.getLastRow();



  // データを取得

  const values = sheet.getRange(1, columnIndex, lastRow).getValues();



  // 1次元配列に変換

  const flatValues = values.flat();



  return flatValues;

}

**使い方:**

1. `spreadsheetId` にデータを取得したいスプレッドシートのIDを指定します。
2. `sheetName` にデータを取得したいシートの名前を指定します。
3. `columnIndex` にデータを取得したい列のインデックスを指定します（A列は1、B列は2、...）。

**例:**

javacsript

// IDが "your-spreadsheet-id" のスプレッドシートの "Sheet1" の A列の値を全て取得

const columnValues = getColumnValues("your-spreadsheet-id", "Sheet1", 1); 

console.log(columnValues); // A列の値の配列が出力される

**注意事項:**

* この関数は、指定されたスプレッドシートの編集権限が必要です。
* 空白セルは、配列の要素として空文字列("")として扱われます。
* この関数は、一度に取得できるデータ量に制限があります。大量のデータを取得する場合は、適宜範囲を分割するなど工夫が必要です。

IMPORTRANGE関数は、特定の範囲のデータを別のスプレッドシートからインポートするため、列全体を取得するのには最適ではありません。

別のスプレッドシートの列全体を取得するには、Google Apps Script (GAS) を使用するのがおすすめです。以下に具体的な手順とサンプルコードを示します。

**手順**

1. **スクリプトエディタを開く:** 取得先のスプレッドシートを開き、「拡張機能」>「Apps Script」の順にクリックします。

2. **コードを貼り付ける:** スクリプトエディタに以下のサンプルコードを貼り付けます。

js

  

function getEntireColumn(spreadsheetId, sheetName, columnIndex) {

  // スプレッドシートを取得

  var ss = SpreadsheetApp.openById(spreadsheetId);



  // シートを取得

  var sheet = ss.getSheetByName(sheetName);



  // 列全体を取得 (2行目から最終行まで)

  var columnData = sheet.getRange(2, columnIndex, sheet.getLastRow() - 1, 1).getValues();



  // 1次元配列に変換

  columnData = columnData.map(function(row) { return row[0]; });



  return columnData;

}

3. **パラメータを変更する:**
* `spreadsheetId`: データを取得したいスプレッドシートのIDに置き換えます。
* `sheetName`: データを取得したいシート名に置き換えます。
* `columnIndex`: 取得したい列の番号に置き換えます (A列は1、B列は2、など)。

4. **関数を呼び出す:** 別の関数からこの `getEntireColumn` 関数を呼び出し、取得したデータを使用します。

**サンプルコード (関数呼び出しを含む):**

js

function myFunction() {

  // データを取得したいスプレッドシートの情報

  var sourceSpreadsheetId = 'your_source_spreadsheet_id';

  var sourceSheetName = 'Sheet1';

  var sourceColumnIndex = 1; // A列を取得



  // 列全体を取得

  var columnData = getEntireColumn(sourceSpreadsheetId, sourceSheetName, sourceColumnIndex);



  // 取得したデータを表示 (例)

  Logger.log(columnData); 

}

**注意点:**

* スクリプトを実行するには、スクリプトにスプレッドシートへのアクセス許可を与える必要があります。
* 取得元のスプレッドシートが非常に大きい場合、処理に時間がかかることがあります。

この方法を使用すると、GASを利用して別のスプレッドシートの列全体を取得し、自身のスプレッドシートで自由に利用することができます。

IMPORTRANGEで取得した商品名と個数を集計するには、QUERY関数を使うのが便利です。

**具体的な手順例**

1. **IMPORTRANGEでデータを取得**

例えば、シートIDが "your_spreadsheet_id"、シート名が "Sheet1"、取得範囲が "A:B" の場合、以下のようになります。


=IMPORTRANGE("your_spreadsheet_id", "Sheet1!A:B")


2. **QUERY関数で集計**

取得したデータをQUERY関数で処理し、商品名でグループ化して個数を合計します。


=QUERY(IMPORTRANGE("your_spreadsheet_id", "Sheet1!A:B"), "SELECT Col1, SUM(Col2) WHERE Col1 IS NOT NULL GROUP BY Col1 LABEL SUM(Col2) '合計'", 1)


* `SELECT Col1, SUM(Col2)`: 商品名(Col1)と個数の合計(SUM(Col2))を選択します。
* `WHERE Col1 IS NOT NULL`: 商品名が空白でないデータのみを抽出します。
* `GROUP BY Col1`: 商品名でグループ化します。
* `LABEL SUM(Col2) '合計'`: 合計列のヘッダーを「合計」に変更します。
* `1`: 最初の行に見出し行があるとみなします。

**例**

| 商品名 | 個数 |
|---|---|
| りんご | 2 |
| みかん | 3 |
| りんご | 1 |
| ぶどう | 4 |
| みかん | 2 |

上記のようなデータの場合、以下の数式で集計できます。


=QUERY(IMPORTRANGE("your_spreadsheet_id", "Sheet1!A:B"), "SELECT Col1, SUM(Col2) WHERE Col1 IS NOT NULL GROUP BY Col1 LABEL SUM(Col2) '合計'", 1)


**結果**

| 商品名 | 合計 |
|---|---|
| りんご | 3 |
| みかん | 5 |
| ぶどう | 4 |

これで、IMPORTRANGEで取得したデータを集計することができます.

IMPORTRANGEで取得した商品名と個数を集計するには、QUERY関数を使うのが便利です。

**手順例**

1. **IMPORTRANGEでデータを取得**

既にIMPORTRANGE関数で商品名(C列)と個数(J列)を取得できていると仮定します。例として、取得元のシートIDが "spreadsheetId"、シート名が "Sheet1" で、データが A1 から始まっているとします。


=IMPORTRANGE("spreadsheetId", "Sheet1!A1:J")


2. **QUERY関数で集計**

取得したデータに対して、QUERY関数を使って商品名でグループ化し、個数を合計します。


=QUERY(IMPORTRANGE("spreadsheetId", "Sheet1!A1:J"), "select Col3, sum(Col10) where Col3 is not null group by Col3 label sum(Col10) '合計'", 1)


* `Col3`, `Col10` はそれぞれ C列(商品名), J列(個数) を表します。
* `where Col3 is not null` は商品名が入力されている行のみを集計対象にします。
* `group by Col3` は商品名でグループ化します。
* `label sum(Col10) '合計'` は合計列に "合計" というラベルを設定します。

**具体的な例**

A1セルに上記のQUERY関数を入力すると、以下の様な結果が得られます。

| 商品名 | 合計 |
|---|---|
| 商品A | 10 |
| 商品B | 5 |
| 商品C | 15 |

**補足**

* IMPORTRANGE関数は初回実行時にアクセス許可を求められる場合があります。
* データ量が多い場合は、QUERY関数の処理に時間がかかることがあります。
* スプレッドシートの関数やQUERY関数の詳細については、Googleのヘルプなどを参照してください。

上記の手順で、IMPORTRANGEで取得した商品名を一つにまとめ、個数を合計することができます。不明点等あれば、お気軽にご質問ください。

IMPORTRANGEで取得したデータを集計し、商品名でまとめた個数の合計を求めるには、以下の2つの方法があります。

**方法1: QUERY関数を使う**

QUERY関数は、データベースのようなクエリ操作をスプレッドシート上で実現できる強力な関数です。

1. **IMPORTRANGEとQUERYを組み合わせる**


=QUERY(
IMPORTRANGE("スプレッドシートURL", "シート名!M:R"),
"select Col1, sum(Col4) where Col1 is not null group by Col1 label sum(Col4) '合計'",
1
)


- `IMPORTRANGE("スプレッドシートURL", "シート名!M:R")`: 別のシートからM列とR列のデータを取得します。
- `"select Col1, sum(Col4) ..."`: 取得したデータに対して、商品名(Col1)でグループ化し、個数(Col4)の合計を計算するクエリです。
- `label sum(Col4) '合計'`: 合計列に「合計」というラベルを設定します。
- `1`: ヘッダー行が1行目にあることを示します。

2. **結果の表示**

この数式を入力したセルに、商品名と合計個数が表示されます。

**方法2: スクリプト(GAS)を使う**

GASを使うと、より柔軟な処理が可能になります。

1. **スクリプトエディタを開く**

スプレッドシートを開き、「ツール」>「スクリプトエディタ」を選択します。

2. **以下のコードを入力**

javacsript

   function 集計() {

     // スプレッドシートの情報

     var ss = SpreadsheetApp.getActiveSpreadsheet();

     var sheet = ss.getActiveSheet(); 

     var targetSheet = ss.getSheetByName('集計結果'); // 集計結果を表示するシート名



     // 別のシートからデータを取得

     var data = SpreadsheetApp.openById('スプレッドシートID').getSheetByName('シート名').getRange('M:R').getValues();



     // 商品名と個数を格納するオブジェクト

     var products = {};



     // データをループ処理

     for (var i = 0; i < data.length; i++) {

       var productName = data[i][0]; // 商品名

       var quantity = data[i][3];   // 個数



       // 商品名が既に存在する場合は個数を加算、存在しない場合は新規に追加

       if (products[productName]) {

         products[productName] += quantity;

       } else {

         products[productName] = quantity;

       }

     }



     // 集計結果を表示する配列

     var result = [];

     for (var productName in products) {

       result.push([productName, products[productName]]);

     }



     // 集計結果をシートに出力

     targetSheet.clearContents();

     targetSheet.getRange(1, 1, result.length, result[0].length).setValues(result);

   }

   

3. **コードの説明**

- `スプレッドシートID`、`シート名`、`集計結果`は、実際の値に置き換えてください。
- このコードは、取得したデータをループ処理し、商品名ごとに個数を合計して、指定したシートに結果を表示します。

4. **実行**

スクリプトエディタで「実行」ボタンを押し、「集計」を選択します。

どちらの方法も、IMPORTRANGEで取得したデータを商品名でまとめて個数を合計できます。QUERY関数は簡潔に記述できますが、GASを使う方が柔軟性が高く、より複雑な処理も可能です。

QUERY関数内で配列を作成し、空白行を挿入することで商品名と合計のずれを修正できます。以下修正案です。


=QUERY(IMPORTRANGE("test-Ws","product!M2:R"),
"select Col1, '' , sum(Col4) where Col1 is not null group by Col1 label sum(Col4) '合計'",
1
)


変更点：

* `select Col1, sum(Col4)` を `select Col1, '' , sum(Col4)` に変更しました。
* 2番目の列に空文字 `''` を挿入することで、商品名と合計の間に空白列が作成されます。

これで、商品名と合計がずれることなく表示されるはずです。

## IMPORTRANGE と QUERY 関数の #VALUE! エラーについて

IMPORTRANGE と QUERY 関数を組み合わせて #VALUE! エラーが発生する原因として、下記の点が考えられます。

**1. スプレッドシートのアクセス権限**

* IMPORTRANGE 関数を使用するには、参照元のスプレッドシートへのアクセス権限が必要です。
* 参照元のスプレッドシートを開き、「共有」設定を確認し、必要であればアクセス権限を付与してください。

**2. IMPORTRANGE 関数の範囲指定**

* IMPORTRANGE 関数の第2引数には、**参照元のシート名を含めた**範囲を指定する必要があります。
* 例えば、「シート1」の A1:C10 セルを参照する場合は、 `"シート1!A1:C10"`のように指定します。

**3. IMPORTRANGE 関数の結果が返ってくるまでの時間**

* IMPORTRANGE 関数は、他のスプレッドシートからデータを取得するため、結果が返ってくるまでに時間がかかる場合があります。
* QUERY 関数を実行する前に、IMPORTRANGE 関数の結果が完全に返されるのを待機する必要があります。

**4. QUERY 関数の構文**

* QUERY 関数の構文に誤りがあると、#VALUE! エラーが発生します。
* 具体的には、列の参照方法（Col1, Col11, Col17）、日付型の扱い方などに注意が必要です。

**解決策**

1. 上記の点を確認し、問題があれば修正してください。
2. QUERY 関数内で日付型の列を扱う場合は、`DATE` 関数を使用して日付型に変換する必要があるかもしれません。

**修正例**


=QUERY(
IMPORTRANGE("スプレッドシートID", "シート名!A:Q"),
"select Col11, Col17, Col1
where Col1 is not null
group by Col1
label count(Col1) '合計'",
1
)


**注意点**

* 上記の修正例は、あくまでも一例です。
* 実際の状況に合わせて、適宜修正してください。


問題が解決しない場合は、下記の情報も提供して頂けると、より的確なアドバイスが可能です。

* エラーが発生する数式の全文
* 参照元のスプレッドシートの URL
* 参照元のシート名
* 参照元のデータ範囲

IMPORTRANGEしたデータをQUERYで整形し、二つのカラムをgroup byして合計数を表示させるには、以下の手順で行います。

**1. IMPORTRANGEでデータを取得**

まず、IMPORTRANGE関数を使用して、目的のスプレッドシートからデータを取得します。


=IMPORTRANGE("スプレッドシートURL", "シート名!範囲")


* `"スプレッドシートURL"` は、データを取得するスプレッドシートのURLに置き換えてください。
* `"シート名!範囲"` は、データが存在するシート名と範囲に置き換えてください。


**2. QUERY関数で整形と集計**

次に、QUERY関数を使用して、取得したデータを整形し、グループ化して合計数を表示します。


=QUERY(IMPORTRANGE("スプレッドシートURL", "シート名!範囲"), "select Col1, Col2, count(Col1) group by Col1, Col2", 1)


* `"Col1"` は、グループ化する最初のカラム（商品名）に置き換えてください。
* `"Col2"` は、グループ化する二番目のカラム（消費期限）に置き換えてください。
* `count(Col1)` は、各グループの数をカウントします。`Col1` の代わりに他のカラムを使用することもできます。
* `group by Col1, Col2` は、`Col1` と `Col2` を使用してデータをグループ化することを指定します。
* 最後の `1` は、ヘッダー行が1行目にあることを示します。


**例：**

商品名と消費期限がそれぞれA列とB列にあるとします。


=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/your-spreadsheet-id", "Sheet1!A:B"), "select Col1, Col2, count(Col1) group by Col1, Col2", 1)


この式は、指定されたスプレッドシートからデータを取得し、商品名と消費期限でグループ化し、各グループの商品数をカウントします。


**注意点：**

* IMPORTRANGE関数を使用するには、最初にアクセス許可を与える必要があります。
* QUERY関数の構文はSQLに似ています。詳細については、Google スプレッドシートのQUERY関数に関するドキュメントを参照してください。

上記の手順と例を参考に、必要に応じてカラム名や範囲を調整して、ご自身のスプレッドシートに適用してみてください。

IMPORTRANGEとQUERYを使って、別のシートからデータを取得し、商品名ごとの会社別の合計金額を計算する方法は以下のとおりです。

**1. データ取得元のシート情報**

* スプレッドシートID： `[スプレッドシートID]`
* シート名： `[シート名]`
* データ範囲： `[データ範囲]` (例: A1:C100)

**2. 集計結果出力先のシート準備**

* 集計結果を出力するシートを作成します。

**3. QUERY関数とIMPORTRANGE関数を組み合わせた数式**

以下の数式を集計結果出力先のシートのA1セルに入力します。


=QUERY(
IMPORTRANGE("[スプレッドシートID]", "'[シート名]'![データ範囲]"),
"SELECT Col2, SUM(Col3)
WHERE Col1 IS NOT NULL
GROUP BY Col2
PIVOT Col1",
1
)


**解説:**

* `IMPORTRANGE("[スプレッドシートID]", "'[シート名]'![データ範囲]")`: 指定したスプレッドシートの指定範囲からデータを取得します。
* `QUERY(...)`: 取得したデータに対してクエリを実行します。
* `SELECT Col2, SUM(Col3)`: 2列目(商品名)と3列目(金額)の合計を抽出します。
* `WHERE Col1 IS NOT NULL`: 1列目(会社名)が空白でないデータのみを対象にします。
* `GROUP BY Col2`: 商品名(2列目)でグループ化します。
* `PIVOT Col1`: 会社名(1列目)をピボットテーブルの列ヘッダーとして使用します。
* 最後の `1` はヘッダー行が1行目にあることを示しています。

**4. アクセス許可**

数式を入力すると、`#REF!` エラーが表示される場合があります。これは、データ取得元のシートへのアクセス許可がないためです。エラーメッセージをクリックし、アクセス許可を求める手順に従ってください。

**数式の適用例**

* データ取得元のシートID： `abcdefg1234567890hijklmnopqrstuvwxyz`
* シート名： `データシート`
* データ範囲： `A1:C100`

この場合、数式は以下のようになります。


=QUERY(
IMPORTRANGE("abcdefg1234567890hijklmnopqrstuvwxyz", "'データシート'!A1:C100"),
"SELECT Col2, SUM(Col3)
WHERE Col1 IS NOT NULL
GROUP BY Col2
PIVOT Col1",
1
)


**注意点:**

* データ取得元シートのデータ範囲に空白行や空白列を含めないでください。
* 会社名、商品名、金額の列番号は実際のデータに合わせて適宜変更してください。

これで、別のシートからデータを取得し、商品名ごとの会社別の合計金額を集計することができます。

はい、その通りです。

`setContentType('application/vnd.ms-excel;charset=Shift_JIS');` と指定した場合、Google Apps Script はアップロードされた CSV ファイルを Shift_JIS エンコーディングで解釈しようとします。

もし実際の CSV ファイルが ANSI (Windows 環境では通常 Shift_JIS と同一視されますが、Macintosh 環境などでは異なるエンコーディングを指す場合があります) でエンコードされている場合は、文字化けが発生する可能性があります。

具体的には、

* **全角英語**: 問題なく表示されることが多いですが、環境によっては文字化けする可能性があります。
* **「㈱」などの機種依存文字**: ほぼ確実に文字化けが発生します。

**解決策**

1. **CSV ファイルのエンコーディングを UTF-8 に変更する**:
UTF-8 は広く使われているエンコーディングで、文字化けのリスクが低いです。CSV ファイルを編集できる環境があれば、UTF-8 で保存し直すことを推奨します。

2. **GAS でエンコーディングを指定する**:
アップロードされたファイルが ANSI であることが分かっている場合は、GAS 側でエンコーディングを指定できます。

javacsript

   // Blob から文字列を取得する際にエンコーディングを指定

   const csvData = Utilities.convertBlobToString(blob, 'Shift_JIS'); 

   

3. **ファイル入力時にエンコーディングを指定**:
HTML Service を利用してファイルを選択する UI を作成している場合は、`<input type="file">` タグに `accept=".csv"` 属性を追加し、ファイル選択時に CSV ファイルのみを受け入れるように制限することで、ブラウザ側で適切なエンコーディングを指定するように促すことができます。

**注意点**

* ANSI は曖昧な表現で、環境によって異なるエンコーディングを指す可能性があります。
* 可能であれば、UTF-8 を利用することを強く推奨します。

上記を参考に、状況に応じて適切な対応策を選択してください。

## GASのsetContentTypeメソッドで設定可能な文字コードとサンプルコード

スプレッドシートをExcelファイルとして出力する際の文字コード指定について、Google Apps Script (GAS) の `setContentType` メソッドを用いたサンプルコードを、可能な限り多くの文字コードと共にご紹介します。

**注意:**
* すべての文字コードがExcelで正しく表示されるとは限りません。
* 特定の文字コードがExcelでサポートされているかどうかは、Excelのバージョンや環境に依存します。

### サンプルコード (JavaScript - GAS)

javacsript

 

function exportSpreadsheet(sheetName, charset) {

  // スプレッドシートとシートを取得

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getSheetByName(sheetName);



  // Excelファイルとして出力

  var url = 'https://docs.google.com/spreadsheets/d/' + ss.getId() + '/export?format=xlsx&gid=' + sheet.getSheetId();

  var response = UrlFetchApp.fetch(url, {

    headers: {

      'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()

    }

  });



  // 文字コードを設定

  var blob = response.getBlob().setContentType('application/vnd.ms-excel;charset=' + charset);



  // ファイル名を設定

  blob.setName(sheetName + '_' + new Date().getTime() + '.xlsx');



  // ファイルをドライブに保存

  DriveApp.createFile(blob);

}

### 文字コード例

上記の `charset` に以下の文字コードを指定することで、Excelファイルの文字コードを変更できます。

**代表的な文字コード:**

* `"Shift_JIS"`: 日本語 (Shift-JIS)
* `"UTF-8"`: Unicode (UTF-8) - 推奨
* `"ISO-8859-1"`: 西ヨーロッパ言語 (Latin-1)
* `"US-ASCII"`: 英語 (ASCII)

**その他の文字コード:**

* `"Big5"`: 繁体字中国語
* `"GB2312"`: 簡体字中国語
* `"EUC-JP"`: 日本語 (EUC-JP)
* `"EUC-KR"`: 韓国語 (EUC-KR)
* `"ISO-2022-JP"`: 日本語 (JIS)
* `"Windows-1252"`: 西ヨーロッパ言語 (Windows-1252)
* `"Windows-1251"`: キリル文字 (Windows-1251)

**利用方法:**

1. 上記サンプルコードをGASのエディタにコピー＆ペーストします。
2. `exportSpreadsheet` 関数の第一引数にシート名、第二引数に希望の文字コードを指定します。
3. スクリプトを実行すると、指定した文字コードでExcelファイルが出力され、Googleドライブに保存されます。

**例:** `exportSpreadsheet("シート1", "UTF-8");` は、「シート1」というシートをUTF-8の文字コードでExcelファイルとして出力します。

**注意点:**

* 上記の文字コードが全てExcelで完全にサポートされているわけではありません。
* 文字化けが発生する場合は、別の文字コードを試してみてください。
* Excelのバージョンや環境によっては、特定の文字コードが正しく表示されない場合があります。

この情報が、GASを用いたスプレッドシートシステム開発に役立つことを願っています。

## Excelを使わずにCSVをスプレッドシートに反映する方法

おっしゃる通り、 `setContentType('application/vnd.ms-excel;charset=Shift_JIS')` はExcelファイルを扱うための設定なので、CSVファイルには適切ではありません。

以下に、Excelを使わずにCSVファイルをGoogleスプレッドシートに反映する手順とGASコード例を示します。

**1. CSVファイルをプラットフォームからGoogleドライブにダウンロード**

これは既に実現できているとのことなので、ここでは詳細を割愛します。

**2. GASでCSVファイルを読み込み、スプレッドシートに反映**

js

  

function importCsvFromDrive(folderId, fileName) {

  // Google Drive上のCSVファイルを取得

  var folder = DriveApp.getFolderById(folderId);

  var file = folder.getFilesByName(fileName).next();



  // CSVファイルの内容を取得

  var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());



  // 新しいスプレッドシートを作成 

  // (既存のシートに追記する場合は適宜変更)

  var ss = SpreadsheetApp.create(fileName); 

  var sheet = ss.getActiveSheet();



  // CSVデータを行列ごとにシートに反映

  for (var row = 0; row < csvData.length; row++) {

    for (var col = 0; col < csvData[row].length; col++) {

      sheet.getRange(row + 1, col + 1).setValue(csvData[row][col]);

    }

  }



  Logger.log('CSVファイルの内容をスプレッドシートに反映しました。');

}  

**使い方:**

1. `folderId` にCSVファイルが保存されているGoogleドライブのフォルダIDを指定します。
2. `fileName` にダウンロードしたCSVファイル名を指定します。
3. スクリプトを実行します。

**解説:**

* `Utilities.parseCsv()` は、CSVデータを二次元配列に変換する関数です。
* `getBlob().getDataAsString()` で、ファイルのコンテンツを文字列として取得しています。
* スプレッドシートへのデータ反映は、 `getRange()` と `setValue()` を使用して行っています。

**注意点:**

* スクリプトの実行には、Googleドライブへのアクセス権限が必要です。
* CSVファイルの文字コードがShift_JIS以外の場合は、 `Utilities.parseCsv()` の第二引数に文字コードを指定する必要があります。

**拡張:**

* 既存のスプレッドシートにCSVデータを追加する場合は、 `SpreadsheetApp.openById()` や `SpreadsheetApp.getActiveSpreadsheet()` を使用してシートを取得し、 `getLastRow()` などで最終行を取得して、追記するように変更します。
* ヘッダー行の有無やデータの整形など、必要に応じてコードを修正してください。


上記を参考に、ご自身の環境に合わせてコードを修正して活用してください。