1. Overview

In a typical testing workflow, creating presentation slides for test reports (e.g., test summaries, module status, KPI reports) is often time-consuming due to:

• Manually copying slide templates
• Entering test data for each slide
• Adjusting layout and formatting

To solve this problem, I implemented an automated solution:

Generate Google Slides from Google Sheets using Google Apps Script with template and placeholder replacement

This approach allows:

• Generating 10+ slides automatically
• Synchronizing real test data from Google Sheets
• Reducing manual effort and human error

2. Example Data & Template (Real Case)

📊 Google Sheets (Data Source)

The dataset used:

TITLE	MODULE_NAME	TOTAL_CASES	PASSED	FAILED	TESTER_NAME	STATUS_COLOR
Login Security Test	Authentication	45	42	3	Nguyễn Văn A	Red
Dashboard UI Check	User Interface	30	30	0	Trần Thị B	Green
Checkout Flow Test	E-commerce	60	55	5	Lê Văn C	Red

👉 Each row represents one slide

---

🖥️ Google Slides Template

Slide 1 (Title slide)

{{TITLE}}{{MODULE_NAME}} | {{TESTER_NAME}}

Slide 2 (Detail slide)

{{MODULE_NAME}}Tổng số case: {{TOTAL_CASES}}  
Số case pass: {{PASSED}}  
Số case fail: {{FAILED}}

👉 Placeholders are customized based on real testing data instead of generic ones like {{POINT1}}.

---

3. Solution Architecture

• Google Sheets → Data source
• Google Slides Template → Predefined layout with placeholders
• Google Apps Script → Automation engine

---

4. Folder Setup (Reproducible)

Prepare:

Template Presentation ID: YOUR_TEMPLATE_ID  
Output Folder ID: YOUR_FOLDER_ID

👉 These IDs ensure the solution is reusable and scalable.

5. Full Apps Script Code

const CONFIG = {
  TEMPLATE_ID: '1PenP76oNotkbbZ58yJMrH-OebQ_8GJeh2XCAIx4ZZcE',
  OUTPUT_FOLDER_ID: '1cyXmz30_xRcbjo0ZQQozHWH79XD3WrIu',
  SHEET_NAME: 'Data' // Đảm bảo tên Sheet chứa dữ liệu của bạn đang là 'Data'
};

function generateBulkSlides() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(CONFIG.SHEET_NAME);
  
  const dataRange = sheet.getDataRange();
  const data = dataRange.getValues();
  data.shift(); 
  
  const outputFolder = DriveApp.getFolderById(CONFIG.OUTPUT_FOLDER_ID);
  const templateFile = DriveApp.getFileById(CONFIG.TEMPLATE_ID);

  let tempSheet = ss.getSheetByName("TEMP_CHART");
  if (!tempSheet) {
    tempSheet = ss.insertSheet("TEMP_CHART");
  }

  data.forEach((row, index) => {
    if (!row[0]) return; 

    const presentationName = `Report_${row[1]}_${new Date().toISOString().split('T')[0]}`;
    
    const newFile = templateFile.makeCopy(presentationName, outputFolder);
    const slideDeck = SlidesApp.openById(newFile.getId());

    // 👉 Mapping mới theo sheet của bạn
    const mapData = {
      '{{TITLE}}': row[0] || '',
      '{{MODULE_NAME}}': row[1] || '',
      '{{TOTAL_CASES}}': row[2] || 0,
      '{{PASSED}}': row[3] || 0,
      '{{FAILED}}': row[4] || 0,
      '{{TESTER_NAME}}': row[5] || '',
      '{{STATUS_COLOR}}': row[6] || ''
    };

    for (const [placeholder, value] of Object.entries(mapData)) {
      slideDeck.replaceAllText(placeholder, value.toString());
    }

    // 👉 Tạo chart mới (Passed vs Failed)
    tempSheet.clear();

    const chartData = [
      ["Status", "Count"],
      ["Passed", row[3] || 0],
      ["Failed", row[4] || 0],
    ];

    tempSheet.getRange(1, 1, chartData.length, 2).setValues(chartData);

    const chartBuilder = tempSheet.newChart()
      .setChartType(Charts.ChartType.PIE) // 👈 đổi sang pie cho dễ nhìn
      .addRange(tempSheet.getRange("A1:B3"))
      .setOption('title', `Test Result - ${row[1]}`)
      .setPosition(1, 1, 0, 0)
      .build();

    tempSheet.insertChart(chartBuilder);

    const tempCharts = tempSheet.getCharts();
    const currentChart = tempCharts[tempCharts.length - 1];

    const slides = slideDeck.getSlides();
    if (slides.length >= 2) { 
      const targetSlide = slides[1]; 
      
      targetSlide.insertImage(
        currentChart.getBlob(), 
        250, 50, 450, 300 
      );
    }

    tempSheet.removeChart(currentChart);

    slideDeck.saveAndClose();
    Logger.log(`Đã tạo thành công: ${presentationName}`);
  });
}

---

6. Key Design Considerations

🔹 Data Mapping Design

• Mapping is based on column headers:

{{TITLE}} → TITLE column  
{{MODULE_NAME}} → MODULE_NAME column  
{{PASSED}} → PASSED column  

👉 Advantage:

• No hardcoding
• Easily scalable

---

🔹 Placeholder Naming Rules

• Format: {{NAME}}
• Must exactly match column headers
• Case-sensitive

---

🔹 Handling Missing Data

const value = row[i] || "N/A";

👉 Ensures:

• No script failure
• Clear fallback value

---

7. Generating 10+ Slides

• Each row = 1 presentation
• With 10 rows → 10 slide files generated automatically

---

8. KPI Table Integration (Requirement)

In addition to text replacement, the script:

• Dynamically inserts a KPI table
• Uses real data (TOTAL_CASES, PASSED, FAILED)

👉 This satisfies:

---

9. Output Result

After execution:

• Files are generated in the output folder:

Test_Report_1
Test_Report_2
...

Each presentation includes:

• Slide 1: Title + Module + Tester
• Slide 2: KPI details + table

🎥 Demo Video: https://youtu.be/RJaaWxTkytM

---

10. Practical Use Case (Testing)

This solution can be applied to:

• Test summary reports
• Weekly QA reports
• Client demo presentations

Benefits:

• Saves significant time
• Reduces manual errors
• Improves consistency

---

11. Conclusion

By combining:

• Google Sheets
• Google Slides
• Google Apps Script

👉 The slide creation process can be fully automated.