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:
- A Google Apps account (your free Gmail account suffices).
- Access to Google Drive, Google Sheets, and Google Slides.
- Chat-GPT's free edition for code generation.
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
- Replace 'YOUR_FOLDER_ID' with the Google drive folder ID.
- The script creates a new spreadsheet; look for "destinationSpreadsheet = SpreadsheetApp.create" and change the value in the brackets t change the name.
- 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
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.
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.
In the next post we will turn the spreadsheet into a presentation.