Generate Google Slides presentations from Big Data in Node.js

In this codelab, you'll learn how you can use Google Slides as a custom presentation tool for an analysis of the most common software licenses. You'll be querying all open source code on GitHub using the BigQuery API and creating a slide deck using Google Slides API to present your results. The sample application is built using Node.js, but the same basic principles are applicable to any architecture.

What you'll learn

Creating presentations using the Slides API
Using BigQuery to gain insights into a large dataset
Copying a file using the Google Drive API

What you'll need

Node.js installed
Access to the internet and a web browser
A Google account
A Google Cloud Platform Project

You can either download all the sample code to your computer...

Download Zip

...or clone the GitHub repository from the command line.

git clone https://github.com/googlecodelabs/slides-api.git

The repository contains a set of directories representing each step along the process, in case you need to reference a working version.

You'll be working off the copy located in the start directory, but you can refer to, or copy files from, the others as needed.

First, let's get the Node script up and running. With the code downloaded, follow the instructions below to install and start the Node.js application:

Open a command line terminal on your computer and navigate to the codelab's start directory.
Enter the following command to install the Node.js dependencies.

npm install

Enter the following command to run the script:

node .

Observe the greeting that shows the steps for this project.

-- Start generating slides. --
TODO: Get Client Secrets
TODO: Authorize
TODO: Get Data from BigQuery
TODO: Create Slides
TODO: Open Slides
-- Finished generating slides. --

You can see our list of TODOs in slides.js, license.js, and auth.js. Note that we use JavaScript Promises to chain the steps needed to complete the app since each step is dependant on the previous step being completed.

If you're not familiar with promises, don't worry, we'll provide all the code you'll need. In short, promises give us a way to handle asynchronous processing in a more synchronous fashion.

To use the Slides, Bigquery, and Drive APIs, we'll create an OAuth Client and a Service Account.

Setup Google Developers Console

Use this wizard to create or select a project in the Google Developers Console and automatically turn on the API. Click Continue, then Go to credentials.
On the Add credentials to your project page, click the Cancel button.
At the top of the page, select the OAuth consent screen tab. Select an Email address, enter Product name Slides API Codelab, and click the Save button.

Enable BigQuery, Drive, and Slides APIs

Select the Dashboard tab, click the Enable API button and enable the following 3 APIs:

BigQuery API
Google Drive API
Google Slides API

Download OAuth Client Secret (for Slides and Drive)

Select the Credentials tab, click the Create credentials button and select OAuth client ID.
Select the application type Other, enter the name Google Slides API Codelab, and click the Create button.Click OK to dismiss the resulting dialog.

Click the file_download (Download JSON) button to the right of the client ID.
Rename your secret file to client_secret.json and copy it into both the start/ and finish/ directories.

Download Service Account Secret (for BigQuery)

Select the Credentials tab, click the Create credentials button and select Service account key.
In the dropdown, select New Service Account. Choose name Slides API Codelab Service for your service. Then click Role and scroll to BigQuery and select both BigQuery Data Viewer and BigQuery Job User.
For the Key type, select JSON.
Click Create. The key file will be automatically downloaded to your computer. Click Close to exit the dialog that appears.
Rename your secret file to service_account_secret.json and copy it into both the start/ and finish/ directories.

Get Client Secrets

In start/auth.js, let's fill out the method getClientSecrets.

auth.js

const fs = require('fs');

/**
 * Loads client secrets from a local file.
 * @return {Promise} A promise to return the secrets.
 */
module.exports.getClientSecrets = () => {
  return new Promise((resolve, reject) => {
    fs.readFile('client_secret.json', (err, content) => {
      if (err) return reject('Error loading client secret file: ' + err);
      console.log('loaded secrets...');
      resolve(JSON.parse(content));
    });
  });
}

We now have loaded the client secrets. The credentials will be passed to the next promise. Run the project with node . to make sure there are no errors.

To create slides, let's add authentication to Google APIs by adding the following code to our auth.js file. This authentication will request access to your Google Account to read and write files in Google Drive, create presentations in Google Slides, and execute read-only queries from Google BigQuery. (Note: We didn't change getClientSecrets)

auth.js

const fs = require('fs');
const readline = require('readline');
const openurl = require('openurl');
const googleAuth = require('google-auth-library');
const TOKEN_DIR = (process.env.HOME || process.env.HOMEPATH ||
      process.env.USERPROFILE) + '/.credentials/';
const TOKEN_PATH = TOKEN_DIR + 'slides.googleapis.com-nodejs-quickstart.json';

// If modifying these scopes, delete your previously saved credentials
// at ~/.credentials/slides.googleapis.com-nodejs-quickstart.json
const SCOPES = [
  'https://www.googleapis.com/auth/presentations', // needed to create slides
  'https://www.googleapis.com/auth/drive', // read and write files
  'https://www.googleapis.com/auth/bigquery.readonly' // needed for bigquery
];

/**
 * Loads client secrets from a local file.
 * @return {Promise} A promise to return the secrets.
 */
module.exports.getClientSecrets = () => {
  return new Promise((resolve, reject) => {
    fs.readFile('client_secret.json', (err, content) => {
      if (err) return reject('Error loading client secret file: ' + err);
      console.log('loaded secrets...');
      resolve(JSON.parse(content));
    });
  });
}

/**
 * Create an OAuth2 client promise with the given credentials.
 * @param {Object} credentials The authorization client credentials.
 * @param {function} callback The callback for the authorized client.
 * @return {Promise} A promise to return the OAuth client.
 */
module.exports.authorize = (credentials) => {
  return new Promise((resolve, reject) => {
    console.log('authorizing...');
    const clientSecret = credentials.installed.client_secret;
    const clientId = credentials.installed.client_id;
    const redirectUrl = credentials.installed.redirect_uris[0];
    const auth = new googleAuth();
    const oauth2Client = new auth.OAuth2(clientId, clientSecret, redirectUrl);

    // Check if we have previously stored a token.
    fs.readFile(TOKEN_PATH, (err, token) => {
      if (err) {
        getNewToken(oauth2Client).then(() => {
          resolve(oauth2Client);
        });
      } else {
        oauth2Client.credentials = JSON.parse(token);
        resolve(oauth2Client);
      }
    });
  });
}

/**
 * Get and store new token after prompting for user authorization, and then
 * fulfills the promise. Modifies the `oauth2Client` object.
 * @param {google.auth.OAuth2} oauth2Client The OAuth2 client to get token for.
 * @return {Promise} A promise to modify the oauth2Client credentials.
 */
function getNewToken(oauth2Client) {
  console.log('getting new auth token...');
  openurl.open(oauth2Client.generateAuthUrl({
    access_type: 'offline',
    scope: SCOPES
  }));

  console.log(''); // \n
  return new Promise((resolve, reject) => {
    const rl = readline.createInterface({
      input: process.stdin,
      output: process.stdout
    });
    rl.question('Enter the code from that page here: ', (code) => {
      rl.close();
      oauth2Client.getToken(code, (err, token) => {
        if (err) return reject(err);
        oauth2Client.credentials = token;
        let storeTokenErr = storeToken(token);
        if (storeTokenErr) return reject(storeTokenErr);
        resolve();
      });
    });
  });
}

/**
 * Store token to disk be used in later program executions.
 * @param {Object} token The token to store to disk.
 * @return {Error?} Returns an error or undefined if there is no error.
 */
function storeToken(token) {
  try {
    fs.mkdirSync(TOKEN_DIR);
    fs.writeFileSync(TOKEN_PATH, JSON.stringify(token));
  } catch (err) {
    if (err.code != 'EEXIST') return err;
  }
  console.log('Token stored to ' + TOKEN_PATH);
}

Explore BigQuery (Optional)

BigQuery allows us to query massive datasets in seconds. Let's use the web interface before querying programmatically. If you've never set up BigQuery before, follow the steps here.

Go to bigquery.cloud.google.com. Click Compose Query on the top left to bring up the New Query view.

Before we run a query, let's use Standard SQL rather than Legacy SQL by clicking Show Options and unchecking SQL Dialect: Use Legacy SQL.

Let's find out the most popular software licences on GitHub by writing this query and pressing "Run Query".

bigquery.sql

WITH AllLicenses AS (
  SELECT * FROM `bigquery-public-data.github_repos.licenses`
)
SELECT
  license,
  COUNT(*) AS count,
  ROUND((COUNT(*) / (SELECT COUNT(*) FROM AllLicenses)) * 100, 2) AS percent
FROM `bigquery-public-data.github_repos.licenses`
GROUP BY license
ORDER BY count DESC
LIMIT 10

We just analyzed millions of public repos on GitHub and found out the most popular licenses. Cool! Now let's setup running the same query, but this time programmatically.

Setup BigQuery

Replace the code in the file license.js. The function bigquery.query will return a promise.

license.js

const google = require('googleapis');
const read = require('read-file');
const BigQuery = require('@google-cloud/bigquery');
const bigquery = BigQuery({
  credentials: require('./service_account_secret.json')
});

// See codelab for other queries.
const query = `
WITH AllLicenses AS (
  SELECT * FROM \`bigquery-public-data.github_repos.licenses\`
)
SELECT
  license,
  COUNT(*) AS count,
  ROUND((COUNT(*) / (SELECT COUNT(*) FROM AllLicenses)) * 100, 2) AS percent
FROM \`bigquery-public-data.github_repos.licenses\`
GROUP BY license
ORDER BY count DESC
LIMIT 10
`;

/**
 * Get the license data from BigQuery and our license data.
 * @return {Promise} A promise to return an object of licenses keyed by name.
 */
module.exports.getLicenseData = (auth) => {
  console.log('querying BigQuery...');
  return bigquery.query({
    query,
    useLegacySql: false,
    useQueryCache: true,
  }).then(bqData => Promise.all(bqData[0].map(getLicenseText)))
    .then(licenseData => new Promise((resolve, reject) => {
      resolve([auth, licenseData]);
    }))
    .catch((err) => console.error('BigQuery error:', err));
}

/**
 * Gets a promise to get the license text about a license
 * @param {object} licenseDatum An object with the license's
 *   `license`, `count`, and `percent`
 * @return {Promise} A promise to return license data with license text.
 */
function getLicenseText(licenseDatum) {
  const licenseName = licenseDatum.license;
  return new Promise((resolve, reject) => {
    read(`licenses/${licenseName}.txt`, 'utf8', (err, buffer) => {
      if (err) return reject(err);
      resolve({
        licenseName,
        count: licenseDatum.count,
        percent: licenseDatum.percent,
        license: buffer.substring(0, 1200) // first 1200 characters
      });
    });
  });
}

Try to console.log some of the data inside our Promise's callback to understand the structure of our objects and see the code work in action.

Now for the fun part! Let's create slides by calling the Slides API's create and batchUpdate methods. Our file should be replaced with the following:

slides.js

const google = require('googleapis');
const slides = google.slides('v1');
const drive = google.drive('v3');
const openurl = require('openurl');
const commaNumber = require('comma-number');

const SLIDE_TITLE_TEXT = 'Open Source Licenses Analysis';

/**
 * Get a single slide json request
 * @param {object} licenseData data about the license
 * @param {object} index the slide index
 * @return {object} The json for the Slides API
 * @example licenseData: {
 *            "licenseName": "mit",
 *            "percent": "12.5",
 *            "count": "1667029"
 *            license:"<body>"
 *          }
 * @example index: 3
 */
function createSlideJSON(licenseData, index) {
  // Then update the slides.
  const ID_TITLE_SLIDE = 'id_title_slide';
  const ID_TITLE_SLIDE_TITLE = 'id_title_slide_title';
  const ID_TITLE_SLIDE_BODY = 'id_title_slide_body';

  return [{
    // Creates a "TITLE_AND_BODY" slide with objectId references
    createSlide: {
      objectId: `${ID_TITLE_SLIDE}_${index}`,
      slideLayoutReference: {
        predefinedLayout: 'TITLE_AND_BODY'
      },
      placeholderIdMappings: [{
        layoutPlaceholder: {
          type: 'TITLE'
        },
        objectId: `${ID_TITLE_SLIDE_TITLE}_${index}`
      }, {
        layoutPlaceholder: {
          type: 'BODY'
        },
        objectId: `${ID_TITLE_SLIDE_BODY}_${index}`
      }]
    }
  }, {
    // Inserts the license name, percent, and count in the title
    insertText: {
      objectId: `${ID_TITLE_SLIDE_TITLE}_${index}`,
      text: `#${index + 1} ${licenseData.licenseName}  — ~${licenseData.percent}% (${commaNumber(licenseData.count)} repos)`
    }
  }, {
    // Inserts the license in the text body paragraph
    insertText: {
      objectId: `${ID_TITLE_SLIDE_BODY}_${index}`,
      text: licenseData.license
    }
  }, {
    // Formats the slide paragraph's font
    updateParagraphStyle: {
      objectId: `${ID_TITLE_SLIDE_BODY}_${index}`,
      fields: '*',
      style: {
        lineSpacing: 10,
        spaceAbove: {magnitude: 0, unit: 'PT'},
        spaceBelow: {magnitude: 0, unit: 'PT'},
      }
    }
  }, {
    // Formats the slide text style
    updateTextStyle: {
      objectId: `${ID_TITLE_SLIDE_BODY}_${index}`,
      style: {
        bold: true,
        italic: true,
        fontSize: {
          magnitude: 10,
          unit: 'PT'
        }
      },
      fields: '*',
    }
  }];
}

/**
 * Creates slides for our presentation.
 * @param {authAndGHData} An array with our Auth object and the GitHub data.
 * @return {Promise} A promise to return a new presentation.
 * @see https://developers.google.com/apis-explorer/#p/slides/v1/
 */
module.exports.createSlides = (authAndGHData) => new Promise((resolve, reject) => {
  console.log('creating slides...');
  const [auth, ghData] = authAndGHData;

  // First copy the template slide from drive.
  drive.files.copy({
    auth: auth,
    fileId: '1toV2zL0PrXJOfFJU-NYDKbPx9W0C4I-I8iT85TS0fik',
    fields: 'id,name,webViewLink',
    resource: {
      name: SLIDE_TITLE_TEXT
    }
  }, (err, presentation) => {
    if (err) return reject(err);

    const allSlides = ghData.map((data, index) => createSlideJSON(data, index));
    slideRequests = [].concat.apply([], allSlides); // flatten the slide requests
    slideRequests.push({
      replaceAllText: {
        replaceText: SLIDE_TITLE_TEXT,
        containsText: { text: '{{TITLE}}' }
      }
    })

    // Execute the requests
    slides.presentations.batchUpdate({
      auth: auth,
      presentationId: presentation.id,
      resource: {
        requests: slideRequests
      }
    }, (err, res) => {
      if (err) {
        reject(err);
      } else {
        resolve(presentation);
      }
    });
  });
});

Lastly, let's open the presentation in the browser. Update the following method in slides.js.

slides.js

/**
 * Opens a presentation in a browser.
 * @param {String} presentation The presentation object.
 */
module.exports.openSlidesInBrowser = (presentation) => {
  console.log('Presentation URL:', presentation.webViewLink);
  openurl.open(presentation.webViewLink);
}

Run your project one last time to show the final result.

You've successfully generated Google Slides from data analyzed using BigQuery. Your script creates a presentation using the Google Slides API and BigQuery to report an analysis of the most common software licenses.

Possible Improvements

Here are some additional ideas for making an even more compelling integration:

Add images to each slide
Share your slides via email using the Gmail API
Customize the template slide as a command line argument

Learn More

Read the Google Slides API developer documentation.
Post questions and find answers on Stack Overflow under the google-slides-api tag.