Update Metrics from Google Spreadsheets

If you already are tracking business results in a Google Spreadsheet, you can use our pre-built connector to automatically update a WorkBoard metric from a value in that spreadsheet.

To get started with updating a Workboard KR from a value in a Google spreadsheet, you can either ask WorkBoard's Integrations team to set up the integration for you, or you can set up the integration yourself.

To request assistance from our Integrations team, you will first need to grant integration@workboard.com permission to access your Google spreadsheet. Then, send an email to integration@workboard.com with the URL of your Google sheet, and the location of the cell data that should be integrated to WorkBoard.

To set up your own integration, click here to go to WorkBoard's pre-built connector catalogue, and click the Google Spreadsheet button.

1. Select the WorkBoard metric you want to update from the value in your Google spreadsheet.

Selecting a metric to update

2. Copy the code that is generated (see example at right).

3. Also make a note of the "named range" value that will appear in the instructions just below the code (it will look like "WoBo_Metric_{metric_id}", where metric_id is the unique WorkBoard id belonging to the metric you have chosen to update).

4. Go to your Google Spreadsheet, right-click on the cell you want to use as your data source, and select "Define named range." Change the default value to "WoBo_Metric_{metric_id}" (the value you copied in step 3), and click the "Done" button. This will allow you to add rows or columns to your spreadsheet, without disrupting the flow of data into WorkBoard.

Defining the named range

4. On your Google Spreadsheet, click on Tools, then Script Editor.

Opening the Google Spreadsheet Script Editor

5. Paste the code (copied in step 2) into the script editor, and press the Save button.

Code Pasted in Script Editor

6. On the same script editor window, click on "Resources", then Current project's triggers.

Current project's triggers

7. Add a new trigger: for Run, select "updateMetric_{metric_id}". For Events, choose "From spreadsheet" and "On change."

Setting new trigger

8. And you are all set! Now whenever there is any update to the cell with the range named "WoBo_Metric_{metric_id}," your changes will be reflected automatically in WorkBoard in realtime.

Watch the video for a demonstration of the connector in action.



# Example webhook code snippet
function updateMetric_26569()  {
  var headers =
    {
      "method" : "post",
      "contentType" : "application/json"
    };
  var activeSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = activeSpreadSheet.getRangeByName("WoBo_Metric_{metric_id}")
  var cell = range.getCell(1, 1);
  var metricUpdateValue = cell.getValue();
  UrlFetchApp.fetch("https://myworkboard.com/wb/apis/hook/{webhook_hash}/{metric_id}/"+metricUpdateValue, headers);
}