Introduction

Following on from my previous post about interacting with presentations using the Google Slides API in .NET

In this article we are going to take an existing chart in a spreadsheet and use it in an existing presentation.

Installation

I will be using a new console application using .NET 6 and will install the following packages.

dotnet new console -f net6.0 -lang F# -n SheetsChartExample

dotnet add package Google.Apis
dotnet add package Google.Apis.Auth
dotnet add package Google.Apis.Slides.v1
dotnet add package Google.Apis.Sheets.v4

Please refer to my previous post on how to set up the Google Cloud, authorise the app and create a Client Service.

Note that when adding the scopes to the OAuth and your scopes array you will need the following scopes https://www.googleapis.com/auth/presentations and https://www.googleapis.com/auth/spreadsheets.

Uploads

To start with we are going to need a presentation and a spreadsheet with a chart in it. You can create your own like shown below or you can go to the sample repo and I have created both so that you can upload to Drive and then convert to the relevant Slides and Sheets documents.

google-sheets
google-slides

We will need their IDs later, which can be found by opening the file and looking at the URL in the browser.

file-id

Interacting with Sheets

let sheetService = new SheetsService(clientService)

let spreadsheetId = "<your spreadsheet id goes here>"

let spreadsheet =
    task {
        return! sheetService.Spreadsheets.Get(spreadsheetId).ExecuteAsync()
    }
    |> Async.AwaitTask
    |> Async.RunSynchronously

let chartId = spreadsheet.Sheets[0].Charts[0].ChartId

First we create a SheetsService so that we can get the spreadsheet and get a chart ID that we can use later on.

Interacting with Slides

let slidesService = new SlidesService(clientService)

let updates = BatchUpdatePresentationRequest(
    Requests = ResizeArray<Request>())

Next create a SlidesService that we can use to make our request later on. Then we initialise a list of requests that we can send to the batch update API to avoid making multiple separate calls.

Replace the chart

The main thing here is that we will want to replace any text that has the placeholder value of {{example-chart}} and replace it with our chart from our spreadsheet using the IDs from earlier. Also note the linking mode. This can be configured to live update when the chart data from the spreadsheet is updated. I have left it with static data for this example.

let replaceText = SubstringMatchCriteria(
    Text = "{{example-chart}}",
    MatchCase = true)

let chartRequest = ReplaceAllShapesWithSheetsChartRequest(
    ContainsText = replaceText,
    SpreadsheetId = spreadsheetId,
    ChartId = chartId,
    LinkingMode = "NOT_LINKED_IMAGE")

let request = Request(
    ReplaceAllShapesWithSheetsChart = chartRequest)

Making the request

And finally, we make the request and log the result to the console.

updates.Requests.Add(request)

let updateResponse =
    task {
        return! slideService.Presentations.BatchUpdate(updates, "<your presentation id here>").ExecuteAsync()
    }
    |> Async.AwaitTask
    |> Async.RunSynchronously

printfn $"Processed {chartReplacements} chart replacement"

When the app has finished running you should see Processed 1 chart replacement in your console output and you will have a nice chart in your presentation.

slides-chart-result

Summary

So by using custom data in spreadsheets, which you can also create using the Google APIs, we can create even better presentations.

A copy of this code is available at https://github.com/martinbryant/google-apis-example/tree/main/examples/SheetsChartExample.

Adding charts to a presentation using Google Apis in Fsharp