Case Study: How We Improved Productivity with Google Apps Script

In our collaborative work, requires us to complete tasks as well as generate reports in a relatively short time. It’s important for us to prepare clear and readable reports to make it easier for other teams to get the information they need. And a readable and collaborative report for technology teams, product teams, and business teams, is Google Slides reports.

Challenge

In every sprint, we have to make at least 3 slide reports: Solution Forum, Alignment Sprint, and Retrospective. In every report, we do 2 steps: prepare the template, then fill it out, and in each report consists of 8 squads. Every sprint, same template.

For the Retrospective report case, at least it takes about 2 minutes 55 seconds just to prepare the template, like in the video below:

We know, create a readable and clear reports takes time and focus, and often we don’t have enough time to do it. So we thought about how to do this more seamlessly. So that we can spend more time on more valuable things.

Approach

We decided to build a slide template generator using a Google Apps Script. We specifically focused on the Retrospective report as the first pilot project. There’s a few things we have to do to implement this:

1. prepare slide templates
2. write code using google apps scripts to duplicate that template and fill in the sprint numbers according to user input
3. don’t forget to hide the last report

Here are some of the codes:

function setupCoverSlideBatman(slide, sprintNumber){
    slide.duplicate();
    var pageElements = slide.getPageElements();
    // REPLACE CONTENT
    pageElements[2].asShape().getText().setText("Sprint "+sprintNumber);
    slide.setSkipped(false);
    slide.move(positionCoverBatman);
}
function setupHighlightBatman(slide, sprintNumber){
    slide.duplicate();
    var pageElements = slide.getPageElements();
    // REPLACE CONTENT
    pageElements[1].asShape().getText().setText("Batman Sprint "+sprintNumber);
    slide.setSkipped(false);
    slide.move(positionHighlightBatman);
}
function setupMVPBatman(slide, sprintNumber){
    slide.duplicate();
    var pageElements = slide.getPageElements();
    // REPLACE CONTENT
    pageElements[7].asShape().getText().setText("Edisi Sprint "+sprintNumber);
    slide.setSkipped(false);
    slide.move(positionMVPBatman);
}

Results

On the first try of running this code on a Retrospective report, we got only about 37 seconds.

from 2 minutes 55 seconds → become 37 seconds

Like a magic 🪄🪄🪄

Like in the video below:

Conclusion

Sometimes, taking a break from work to think of a better way, is a worthwhile investment.