Skip the grunt work: Use AI to turn raw data into Slides (Part 2)

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"

Chat-GPT 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.");
}

Step 3: Set up and run the script. (covered in Part 1, included here)

Create the Google Slides Presentation

1. Create a New Google Slides Presentation:
   • Go to Google Slides and click Blank to create a new presentation.
   • Name your presentation (e.g., “Stock Data Presentation”).
2. Obtain the Presentation ID:
   • Look at the URL of the Google Slides presentation:

     https://docs.google.com/presentation/d/1ny5EzuY5kBGXrFG56kq5WvO8-Y4tQpculaMTReEcQoc/edit
     

   • Copy the part of the URL between /d/ and /edit. In this example, the Presentation ID is:   "1ny5EzuY5kBGXrFG56kq5WvO8-Y4tQpculaMTReEcQoc"

Prepare Your Google Spreadsheet

1. Create a Google Spreadsheet:

   • Go to Google Sheets and create a new spreadsheet.
   • Use the spreadsheet created in Part 1, or create a new one by uploading the CSV files to separate sheets as demonstrated in the first blog post.

2. Obtain the Spreadsheet ID:

   • Look at the URL of the Google Spreadsheet:

     https://docs.google.com/spreadsheets/d/1vt852T-ZvRF5zd0gp1xdLtkt0pCK-Zhs7B7kf_uYkhU/edit
     

   • Copy the part of the URL between /d/ and /edit. In this example, the Spreadsheet ID is: "1vt852T-ZvRF5zd0gp1xdLtkt0pCK-Zhs7B7kf_uYkhU"

Setting Up the Script

Now that you have your Google Spreadsheet and Google Slides Presentation IDs, it’s time to set up the script:

1. Open the Apps Script Editor:

   • In your Google Spreadsheet, go to Extensions → Apps Script.
   • This will open a new tab with the Apps Script editor.

2. Create a New Script:

   • Delete any existing code in the editor.
   • Copy and paste the complete code (provided in the final version above) into the script editor.

3. Set the Spreadsheet and Presentation IDs:

   • Replace the placeholders in the script with your actual Spreadsheet ID and Presentation ID:

     var spreadsheetId = '1vt852T-ZvRF5zd0gp1xdLtkt0pCK-Zhs7B7kf_uYkhU'; // Replace with your Spreadsheet ID
     var presentationId = '1ny5EzuY5kBGXrFG56kq5WvO8-Y4tQpculaMTReEcQoc'; // Replace with your Presentation ID
     

4. Save the Script:

   • Click the floppy disk icon or press Ctrl + S to save the script.
   • Give it a name like "CreateChartsAndSlides".

Run the Script

1. Run the Script:
   • Click the Run button (▶️) in the Apps Script editor.
2. Grant Permissions:
   • The first time you run the script, Google will prompt you to grant permissions to access your Google Sheets and Google Slides. Review the permissions and click Allow.
3. Monitor the Logs:
   • You can monitor the script execution by clicking on View → Logs in the Apps Script editor.
   • If the script encounters any errors, check the log messages for details.

Review the Presentation

1. Open Your Google Slides Presentation:

• Navigate back to the Google Slides presentation you created earlier. You should have one slide per sheet in your spreadsheet.
  
  
  
  
  

Conclusion

This two-part series has covered how to automate the process of creating a Google Slides presentation from multiple CSV files using Google Apps Script. With a few lines of code, we’ve transformed CSV data into an informative presentation, ready for analysis or sharing.

• In Part 1, we showed you how to merge 50 CSV files into a single Google Spreadsheet.
• In Part 2, we converted each sheet’s data into a chart and seamlessly integrated those charts into a professional Google Slides presentation.