In Part 1 of this series, I showed you how to merge multiple CSV files into a single Google Spreadsheet, with each CSV file stored as a separate sheet. Now, let’s leverage ChatGPT to turn these sheets into a visually engaging Google Slides presentation.
The goal of this post is to:
- Use ChatGPT to generate the Apps Script code for creating a line chart for each sheet in the Google Spreadsheet.
- Use ChatGPT again to create the code for inserting these charts into a Google Slides presentation.
- Format and position the charts within the slides to ensure a polished look.
We’ll tackle this in three steps:
Step 1: Get ChatGPT to Generate the Code to Read and Create the Charts
When you have multiple sheets in a Google Spreadsheet and want to create a chart from specific columns, it can be tedious to set up each chart manually. We can use ChatGPT to automate this process.
Prompt to ChatGPT
"Write an Apps Script function that reads all sheets in a Google Spreadsheet. For each sheet, create a line chart using only theDate,Open, andClosecolumns. TheDatecolumn should be on the x-axis, andOpenandCloseshould be two separate series. Generate the chart so that it can be used in a presentation."
ChatGPT Output
function createLineChartFromSheet(sheet) {
try {
var range = sheet.getDataRange(); // Get all the data in the sheet
var data = range.getValues(); // Get data as a 2D array
// Find indices of the required columns: 'Date', 'Open', and 'Close'
var headers = data[0];
var dateIndex = headers.indexOf('Date');
var openIndex = headers.indexOf('Open');
var closeIndex = headers.indexOf('Close');
// Check if the necessary columns exist
if (dateIndex === -1 || openIndex === -1 || closeIndex === -1) {
Logger.log("Required columns 'Date', 'Open', or 'Close' not found in sheet: " + sheet.getName());
return null;
}
// Create a new array with only the Date, Open, and Close columns
var filteredData = data.map(function(row) {
return [row[dateIndex], row[openIndex], row[closeIndex]];
});
// Remove the first row (headers) from the filtered data
filteredData.shift();
// Set the range for the chart based on the filtered data
var chartRange = sheet.getRange(2, dateIndex + 1, sheet.getLastRow() - 1, 3); // Skip headers, use Date, Open, and Close columns
// Create a line chart with Date as the x-axis and Open/Close as series
var chart = sheet.newChart()
.setChartType(Charts.ChartType.LINE)
.addRange(chartRange)
.setPosition(1, 1, 0, 0) // Temporary position in the sheet
.setOption('hAxis', { title: 'Date' }) // Set Date as x-axis title
.setOption('vAxis', { title: 'Price' }) // Set y-axis title
.setOption('legend', { position: 'bottom' }) // Position legend at the bottom
.setOption('series', { 0: { labelInLegend: 'Open' }, 1: { labelInLegend: 'Close' } }) // Add legend labels
.build();
// Insert the chart into the sheet temporarily to convert it into an image
sheet.insertChart(chart);
// Retrieve the chart as an image blob
var imageBlob = chart.getAs('image/png'); // Convert chart to image
// Remove the chart from the sheet after retrieving it as an image
// comment out this line if you want to keep the chart in the sheet
sheet.removeChart(chart);
return imageBlob;
} catch (e) {
Logger.log("Failed to create chart for sheet: " + sheet.getName());
Logger.log(e.message);
return null;
}
}
Step 2: Get ChatGPT to Create the Code to Write the Charts to the Presentation
Now that we have a function to generate the charts as images, we need to insert these images into a Google Slides presentation. We can again ask ChatGPT to help us with this part of the code.
Prompt to ChatGPT
"Write an Apps Script function that takes a chart image blob and inserts it into a Google Slides presentation. For each sheet, create a new slide titled 'Open and Close Prices for [Sheet Name]' and place the image at the top left of the slide. Resize the image to fill the width of the slide without exceeding the slide’s height."
ChatGPT Output
function createChartsAndEmbedInSlides() {
var spreadsheetId = 'YOUR_SPREADSHEET_ID'; // Replace with your Google Spreadsheet ID
var presentationId = 'YOUR_PRESENTATION_ID'; // Replace with your Google Slides Presentation ID
var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
var sheets = spreadsheet.getSheets(); // Get all sheets in the spreadsheet
var presentation = SlidesApp.openById(presentationId);
for (var i = 0; i < sheets.length; i++) {
var sheet = sheets[i];
var sheetName = sheet.getName();
var chartBlob = createLineChartFromSheet(sheet);
if (chartBlob) {
var slide = presentation.appendSlide(SlidesApp.PredefinedLayout.BLANK);
slide.insertTextBox("Open and Close Prices for " + sheetName, 10, 10, 600, 50); // Add slide title
var image = slide.insertImage(chartBlob);
var slideWidth = presentation.getPageWidth();
var slideHeight = presentation.getPageHeight();
var aspectRatio = image.getWidth() / image.getHeight();
image.setLeft(0);
image.setTop(60); // Position below title
var maxImageHeight = slideHeight - 80; // Available height with padding
var maxImageWidth = slideWidth;
// Adjust image size to fit within slide boundaries
if (slideWidth / aspectRatio > maxImageHeight) {
image.setHeight(maxImageHeight);
image.setWidth(maxImageHeight * aspectRatio);
} else {
image.setWidth(maxImageWidth);
image.setHeight(maxImageWidth / aspectRatio);
}
}
}
Logger.log("Process completed! Check your presentation for the updated slides.");
}
Almost there!
Just run the script and your slides will be generated with charts showing data from the spreadsheet.