In this article we will explain how to connect to Google Sheets API within Teneo Studio, which will allow you to read information and update data stored in Google Sheets during the runtime of your chatbot.
Prepare your Google Cloud Service account
To connect to a spreadsheet in Google Sheets, first you should have an account in Google Cloud. After creating your account, a new project called “My First Project” will be created automatically for you. Now you need to click on the icon at the top left and choose IAM & Admin > Service Accounts.
Create a new Service account by clicking Create Service Account on the top of the page. You only need to provide a Service account name as the Service account ID will be automatically generated. The second and the third steps are optional, and you do not need to change anything there. Just click Create and Continue in Step 1 and click Done.
Now you will see the account you just created in the Service account list. Click on the account email to open it and choose Keys > Add Key > Create new key.
The Key type will be JSON by default. If not, please choose JSON as the Key type manually. Now click Create and a JSON file containing your credentials will automatically download to your computer. Rename it as credentials.json. Please DO NOT rename it with any other file name. Later it will be imported to your solution and used in the code below.
Prepare your spreadsheets
Now you need to create a spreadsheet to be linked to your solution in Google Sheets, or use what you already have there. Then click on the Share button at the top right
and add the email of the Service account you just created. Make sure the service account appears in the access list as below:
Set up the code and dependencies in Teneo Studio
Now you can start to set up Google Sheet connection in Teneo Studio. First of all, you need to import the following files into Resources > File with Published Location /script_lib.
- The JSON file of the Key of your Service account, renamed as credentials.json
- google-api-client-1.23.0.jar (or later version, download from here)
- google-api-services-sheets-v4-rev531-1.23.0.jar (or later version, download from here)
- google-http-client-1.23.0.jar (or later version, download from here)
- google-http-client-jackson2-1.23.0.jar (or later version, download from here)
- google-oauth-client-1.23.0.jar (or later version, download from here)
- jackson-core-2.1.3.jar (or later version, download from here)
Second, you need to add the following code to Globals > Scripts > Solution loaded, or create a groovy file named GoogleSheets.groovy containing the code below and import it into Resources > File with Published Location /script_lib as well.
import com.google.api.client.googleapis.auth.oauth2.GoogleCredential
import com.google.api.client.googleapis.javanet.GoogleNetHttpTransport
import com.google.api.client.http.HttpTransport
import com.google.api.client.json.JsonFactory
import com.google.api.client.json.jackson2.JacksonFactory
import com.google.api.services.sheets.v4.Sheets
import com.google.api.services.sheets.v4.SheetsScopes
import com.google.api.services.sheets.v4.model.ClearValuesRequest
import com.google.api.services.sheets.v4.model.ClearValuesResponse
import com.google.api.services.sheets.v4.model.ValueRange
class GoogleSheets {
/**Initialize*/
static final String APPLICATION_NAME = "Teneo Google Sheets Connector"
static HttpTransport httpTransport = GoogleNetHttpTransport.newTrustedTransport()
static final JsonFactory jsonFactory = JacksonFactory.getDefaultInstance()
static final GoogleCredential credential
static Sheets sheets
/**Create credentials*/
static {
String sFile = "credentials.json"
URL url = GoogleSheets.class.getClassLoader().getResource(sFile)
if (url == null) throw new RuntimeException("Failure to read file [" + sFile + "] as resource, no URL could be constructed for it")
File file = new File(url.toURI())
credential = GoogleCredential.fromStream(new FileInputStream(file))
.createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS))
/**Create Sheets Service*/
sheets = new Sheets.Builder(httpTransport, jsonFactory, credential)
.setApplicationName(APPLICATION_NAME)
.build()
}
/**Methods*/
/**Get a range of data from a Spreadsheet
* @param {String} sheetId The id of the sheet, this can be taken from the sheet's URL.
* @param {String} range The range of data to get, using A1 notation.
* @param {Boolean} Optional parameter to get the data ordered by columns rather than rows.
* */
static List getRange(String sheetId, String range, Boolean inColumns = null) {
def dataRequest = sheets.spreadsheets().values().get(sheetId, range)
if (inColumns) {
dataRequest.setMajorDimension("COLUMNS")
} else {
dataRequest.setMajorDimension("ROWS")
}
ValueRange rangeData = dataRequest.execute()
return rangeData.getValues()
}
/**Clears a range of data from a sheet
* @param {String} sheetId The id of the sheet, this can be taken from the sheet's URL
* @param {String} range The range of data to delete, using A1 notation.
* */
static Map clearRange(String sheetId, String range) {
ClearValuesRequest clearValuesRequest = new ClearValuesRequest()
ClearValuesResponse clearValuesResponse = sheets.spreadsheets().values().clear(sheetId, range, clearValuesRequest).execute()
return clearValuesResponse
}
/**Updates a range of data from a sheet
* @param {String} sheetId The id of the sheet, this can be taken from the sheet's URL
* @param {String} range The range of data to update, using A1 notation.
* @param {List} values The list of values that will be updating starting from the range. It can take a list of lists that will be written into subsequent rows.
* */
static Map updateRange(String sheetId, String range, List values) {
ValueRange body = new ValueRange()
.setValues(values)
Map updatedCells = sheets.spreadsheets().values().update(sheetId, range, body).setValueInputOption("RAW").execute()
return updatedCells
}
/**Updates a range of data from a sheet
* @param {String} sheetId The id of the sheet, this can be taken from the sheet's URL
* @param {String} range The range of data to append to, using A1 notation.
* @param {List} values The list of values that will be appended starting from the range. It can take a list of lists that will be written into subsequent rows.
* */
static Map appendRange(String sheetId, String range, List values){
ValueRange body = new ValueRange()
.setValues(values)
Map appendedCells = sheets.spreadsheets().values().append(sheetId, range, body).setValueInputOption("RAW").execute()
return appendedCells as Map
}
}
Reload the engine in Tryout to make sure that no warning comes up. If you see any warning related to the code above, please check if you have imported all the jar files listed above together with the credentials.json to /script_lib and reload the engine again. If the problem still occurs, please let us know here in the Forum.
Access the Google Sheets in Teneo Studio
The code above contains 4 methods, corresponding to getting data, adding data, updating data and removing data from Google Sheets. In this article we will use this spreadsheet as an example:
Get data
Use the GoogleSheets.getRange(sheetId,range) method in any script node in your solution to retrieve data from Google Sheets.
This method require two arguments:
-
sheetId: The ID of your spreadsheet in Google Sheets, which is included in the URL. For example in the following link, the part with red underscore indicates the Sheet ID:
-
range: The range of data you need to retrieve, in A1 notation.
The A1 notation is the basic method to make reference to a certain range in a spreadsheet in the following format: label!from:to, using upper-case letters for columns and numbers for rows, for example this notation: Sheet1!A1:B2 refers to the cells from A1 to B2 (i.e. A1, B1, A2 and B2) in the spreadsheet with label Sheet1. Click here for more examples of A1 notation.
The returned value for this method will be a list of sublists, in which each sublist represents a row in the spreadsheet. For example you will get the following list:
[[Name, Price per kilo], [Apple, 2.49], [Pear, 2.99], [Kiwi, 6.79], [Kaki, 3.39]]
By the following code:
GoogleSheets.getRange("1c991G33-VMpcpo7kmIeeTwUJwCfoAHJgc_wRzSx_VUE","Sheet1!A:B")
Update data
Use the GoogleSheets.updateRange(sheetId,range,values) method in any script node in your solution to update data in Google Sheets.
Besides sheetId and range, you need another argument which contains the new values of the cells you want to update. This argument should be a list of sublists where each sublist represents a row (same format with the returned value of the getRange method). For example, running the following code:
GoogleSheets.updateRange("1c991G33-VMpcpo7kmIeeTwUJwCfoAHJgc_wRzSx_VUE","Sheet1!A2:B2",[["Orange","1.49"]])
Will change the value of the cells A2 and B2 to Orange and 1.49 in the spreadsheet:
Please remember that the updated value should always be a list of sublists, even if you only need to change values in one single row (as the example above).
Add data
Use the GoogleSheets.appendRange(sheetId,range,values) method in any script node in your solution to append data to Google Sheets. This method requires the same arguments as the updateRange method, for example running the following code:
GoogleSheets.appendRange("1c991G33-VMpcpo7kmIeeTwUJwCfoAHJgc_wRzSx_VUE","Sheet1!6:6",[["Banana","1.99"]])
Will add Banana and 1.99 in Row 6 in the spreadsheet:
Remove data
Use the GoogleSheets.clearRange(sheetId,range) method in any script node in your solution to append data to Google Sheets. This methods only requires sheet ID and range, the same as the getRange method. For example, running the following code:
GoogleSheets.clearRange("1c991G33-VMpcpo7kmIeeTwUJwCfoAHJgc_wRzSx_VUE","Sheet1!6:6")
Will remove the contents in Row 6 from the spreadsheet:
Conclusion
In this article we provide you with the methods for manipulating spreadsheets stored in Google Sheets during the runtime of your chatbot, covering retrieving, updating, appending, and removing data. If you are using Google Sheets in your Teneo project, we encourage you to share your use case in the forum. We hope you found this article useful, and feel free to ask here any questions you might have on this topic.