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:

  1. Use ChatGPT to generate the Apps Script code for creating a line chart for each sheet in the Google Spreadsheet.
  2. Use ChatGPT again to create the code for inserting these charts into a Google Slides presentation.
  3. 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 the Date, Open, and Close columns. The Date column should be on the x-axis, and Open and Close should 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.