Scenario

You've got to create a presentation using data from multiple CSV files. Typically, this means merging files into a single spreadsheet, generating charts, and copying everything into your presentation—an absolute time sink! Let’s fix that.

Here’s a free, no-code approach using Google Workspace tools and a sprinkle of automation. This is Part 1 of a two-part series.

The Problem Simplified:

  • Multiple CSV files in a consistent format (e.g., stock data).
  • The goal: Combine into one spreadsheet for easy analysis and charting.
  • How: Instead of manual copy-pasting, we’ll automate the process using Google Apps Script and ChatGPT for code generation.

What You'll Need:

  • Google Apps account: A free Gmail account will work perfectly.
  • Access to Google Drive, Google Sheets, and Google Slides.
  • ChatGPT's free edition for code snippets.

Step-by-Step Solution:

1. Collect your data

  • Upload the CSV files to Google Drive.
  • Copy the Drive folder ID. The folder ID is the part of the URL that comes after `/folders/` (e.g., `https://drive.google.com/drive/folders/your-folder-id`).

2. Create your spreadsheet

  • Open a new Google Spreadsheet.
  • Navigate to "Extensions" → "Apps Script" to open the Google Apps Script editor.
  • Delete any code you see in the editor window. Keep the window open.

3. Generate code with ChatGPT

  • Open ChatGPT in another browser tab.
  • Ask: “Write an Apps Script to merge CSV files from a specific Google Drive folder into one Google Sheet.”
  • ChatGPT will generate the script for you. Copy this script and paste it into the Apps Script editor you opened earlier.

4. Edit the script

  • Replace `'YOUR_FOLDER_ID'` with the Google Drive folder ID.
  • Customize the name of your new spreadsheet by finding the line that says destinationSpreadsheet = SpreadsheetApp.create('prices'); and changing `'prices'` to your preferred name.
  • Preserve the original CSV files by changing setTrashed(true) to setTrashed(false).

Modified script for reference


function mergeCSVFiles() {
  var folderId = 'YOUR_FOLDER_ID'; // Replace with your folder ID
  var destinationSpreadsheet = SpreadsheetApp.create('Combined_Data'); // Change the name if needed
  var folder = DriveApp.getFolderById(folderId);
  var files = folder.getFilesByType('application/vnd.google-apps.spreadsheet');
  
  while (files.hasNext()) {
    var file = files.next();
    var filename = file.getName();
    var fileId = file.getId();
    
    var tempSpreadsheet = SpreadsheetApp.openById(fileId);
    var tempSheets = tempSpreadsheet.getSheets();
    
    for (var i = 0; i < tempSheets.length; i++) {
      var tempSheet = tempSheets[i];
      var tempData = tempSheet.getDataRange().getValues();
      
      var newSheet = destinationSpreadsheet.insertSheet(filename.replace('.csv', ''));
      newSheet.getRange(1, 1, tempData.length, tempData[0].length).setValues(tempData);
    }
    
    DriveApp.getFileById(fileId).setTrashed(false); // Keep original files
  }
}

5. Save and run the script

  • Click the play button (▶️) to execute the script.
  • The first time you run the script, you’ll be asked to grant permissions to access Google Drive files. Review and approve the permissions.

Important Note:

To maintain data privacy, you can delete the script or spreadsheet after completing your task.

Conclusion:

You should have all the data neatly organized and ready for analysis. In the next post, we will turn the spreadsheet into a presentation.