It's life Jim, but not as we know it

2024-01-29

Turn multiple CSVs into a presentation with AI and minimal effort (Part 1)


You've got to create a presentation that uses data sourced from multiple CSV files, so you merge files into a spreadsheet, create charts and paste into your presentation. Horrendous waste of your time. Let's change that.

Here's a free, user-friendly, no-code approach. This is part 1 of a two-part series.





The Problem Simplified:
  • Multiple CSV files containing your data in a consistent format (I used stock data).
  • The goal: A comprehensive Google Spreadsheet with charts.
What You'll Need:

Step-by-Step Solution:

1. Collect your data:
  • Upload the CSV files to Google Drive.
  • Copy the Drive folder ID (The bit of URL obscured in the image).

2. Create your Spreadsheet:
  • Create and open a new Google Spreadsheet.
  • Navigate to "Extensions" → "Apps Script" to access the Google Apps Script editor.
  • Delete any code you see in the editor window. Keep the window open.
3. Generate code with Chat-GPT:
  • In another browser tab, Ask Chat-GPT: "Write an AppScript to merge files from a specific Google Drive folder into one Google Sheet."
  • Paste the provided code in the Apps Script editor you opened a few moments ago. 
  • Edit the script
    1. Replace 'YOUR_FOLDER_ID' with the Google drive folder ID.
    2. The script creates a new spreadsheet; look for "destinationSpreadsheet = SpreadsheetApp.create" and change the value in the brackets t change the name.
    3. To preserve the CSV files, look for DriveApp.getFileById(fileId).setTrashed(true); and change true to false


Example code

function mergeCSVFiles() {
  var folderId = 'YOUR_FOLDER_ID'; // Replace with the ID of the Google Drive folder
  var destinationSpreadsheet = SpreadsheetApp.create('prices');
  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(true);
  }

}


4. Save and run the script by clicking the play button (▶️).
5. Upon script execution, grant necessary Google Drive permissions

Note: To maintain privacy, you can remove the script or spreadsheet post-task.
The script will add a new spreadsheet (called 'prices' in my example) with each CSV file as a separate sheet.




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.