SAP Intelligent Robotic Process Automation, SAP Build Process Automation, SAP Business Technology Platform

Automating Gmail, Google Sheets and Google Drive with SAP Build Process Automation

SAP Build Process Automation provides Google Workspace SDK which can be used to automate Google Workspace products like Gmail, Sheets, Drive, Calendar, Docs and Slides. The activities provided by the SDK are grouped into different modules each for automating a Google Workspace product.

This blog will be helpful for those who use Google Workspace products as their office suite and would like to automate them. Let’s look at a few most helpful activities under the modules for Gmail, Google Sheets and Google Drive.

Prerequisite:

Before using the activities provided by Google Workspace SDK, SAP Build Process Automation must be authorized to access the Google account.

Dependencies:

To automate the Google Workspace products, Google Authorization SDK and Google Workspace SDK must be added as dependencies to the project in Cloud Studio.

Required dependencies

Gmail:

Gmail is an email service provided by Google.

The SDK provides the activities for sending an email, searching for emails, reading an email, modifying the labels of an email, and replying to the email.

SDK activities for automating Gmail

Let’s understand a few of these activities in details:

Read Email (Gmail):

This activity reads an email and downloads the associated attachment(s).

This activity can be used by looping over the list obtained as output of the “Search Emails (Gmail)” activity and the “messageId” of each element in the list can be fed to this activity to read the contents of the email.

Input parameters:

  • messageId: Message ID of the email to read.
  • location: The attachments present in the email can be downloaded to either the local file system or to Google Drive by setting this parameter to “fileSystem” or “drive” respectively.
  • pathOrDriveId: Based upon whether to download the attachments to local file system or Google Drive, the “pathOrDriveId” parameter can be set to either the path to a location on the local file system or the URL to a Google Drive location respectively. By default, the ‘Downloads’ folder of the system is used if ‘fileSystem’ is selected, and ‘My Drive’ is used if ‘drive’ is selected as location.
  • markAsRead: Set this true to mark the email as read.
  • timeout: Timeout for the activity in ms. The default is 30,000 ms.

Output parameters:

  • messageDetail: JSON which contains the details of the read email like from, to, cc, subject, body, attachments, etc.
Read Email (Gmail)

Send Email (Gmail):

This activity sends an email.

Input parameters:

  • gmailParameters: It is a data type comprising of:
    • to: List of direct recipients of the email.
    • cc: List of carbon copy recipients of the email.
    • bcc: List of blind carbon copy recipients of the email.
    • subject: Subject of the email.
    • body: Body of the email.
    • isHtmlBody: This is set to false by default and it means that the body will be treated as plain text. However, it is possible to set this to true and send HTML content as part of the email body.
    • attachments: List of attachments.
      • attachFrom: The attachments could be included either from local file system or from Google Drive by setting this parameter to “fileSystem” or “drive” respectively.
      • filePath: Based upon whether to include the attachments from local file system or Google Drive, this parameter can be set to either the path to the local file system or the Google Drive file ID respectively.
  • timeout: Timeout for the activity. The default is 30,000 ms.
Send Email (Gmail)

Google Sheets:

Google Sheets is used to create and edit online spreadsheets.

The SDK provides the activities for creating/renaming a spreadsheet, adding/deleting/renaming/hiding/copying/moving a sheet within a spreadsheet, fetching list of spreadsheets and their details, setting/getting values, importing Excel file as a Google Sheet, etc.

SDK activities for automating Google Sheets

Let’s understand a few of these activities in details:

Get Cell Values (Google Sheet):

This activity reads the values from the specified spreadsheet.

Input parameters:

  • spreadSheetId: ID of the spreadsheet to read.
  • range: It is a data type comprising of:
    • sheetTitle: Title of the sheet to read.
    • startRange: Address of the cell in A1 notation to start reading the data from.
    • endRange: Address of the cell in A1 notation to end reading the data at. Get Last Row (Google Sheet) and Get Last Column (Google Sheet) activities can be used to get the last populated row and column respectively.
  • majorDimension: It can be set to “rows” or “columns”. If it is set to “rows”, then the output data will be a 2D array with row as the major dimension.
  • valueRenderOption: It can be set to
    • “formatted” – the output data will be same as the formatted value in the spreadsheet,
    • “unformatted” – the output will be raw data,
    • “formula” – the output data will be the formula and not the value.
  • timeout: Timeout for the activity in ms. The default is 30,000 ms.

Output parameters:

  • cellsData: JSON with “values” property as a 2D array.
Get Cell Values (Google Sheet)

Set Cell Values (Google Sheet):

This activity sets values in the specified spreadsheet.

Input parameters:

  • spreadSheetId: ID of the spreadsheet to update.
  • range: It is a data type comprising of:
    • sheetTitle: Title of the sheet to update.
    • startRange: Address of the cell in A1 notation to start writing the data at.
    • endRange: Address of the cell in A1 notation to end writing the data at.
  • values: It accepts both single-dimensional and 2-dimensional array. In case of a single dimensional array, it accepts an array of CSV strings [‘1,2,3,4’, ‘A,B,C,D,E’] in which each string represents major dimension with comma as the separator. In case of a 2-dimensional array, each array represents a row or column based on major dimension. For example [[1,2,3,4], [‘first’, ‘second’, ‘third’, ‘fourth’]].
  • majorDimension: It can be set to “rows” or “columns”. If it is set to “rows”, then the “values” will be processed as an array with row as the major dimension.
  • valueInputOption: It can be set to
    • “userEntered” – the data will be written with the destination formatting,
    • “raw” – the raw data will be written.
  • timeout: Timeout for the activity in ms. The default is 30,000 ms.

Output parameters:

  • isDataUpdated: It is set to true if the data was set successfully.
Set Cell Values (Google Sheet)

Google Drive:

Google Drive allows users to store files in the cloud and share them. It has a default drive called “My Drive” and it is also possible to create shared drives.

The SDK provides the activities for fetching the details of files/folders, and uploading/downloading/sharing/moving/renaming a file.

SDK activities for automating Google Drive

Let’s understand a few of these activities in details:

Download File (Google Drive):

This activity downloads a file from Google Drive.

Input parameters:

  • fileId: The unique ID of the file to download.
  • localFolderPath: Parameter to specify the folder in which the file should be downloaded. The ‘Downloads’ folder in the file system is used by default.
  • name: The name to which the downloaded file should be renamed to.
  • timeout: Timeout for the activity in ms. The default is 30,000 ms.

Output parameters:

  • MetaData: The metadata of the file that was downloaded.
Download File (Google Drive)

Upload File (Google Drive):

This activity uploads a file to Google Drive.

Input parameters:

  • filePath: Path of the file to be uploaded.
  • name: The name to which the uploaded file should be renamed to.
  • description: A short description of the file.
  • parentID: The ID of the parent file or folder to upload the file to. If not set, the file will be uploaded to ‘My Drive’ by default.
  • readOnly: If set to true, the file will be in read-only mode.
  • writerCanShare: If set to true, only the users with the writer permission can modify the file’s permissions. This parameter is not applicable for files in shared drives.
  • keepRevisionForever: Set this parameter to true to deactivate the default retention policy in Google Drive. By default, Drive empties files from trash 30 days after they’re moved to trash.
  • copyRequiresWriterPermission: Set this parameter to true to disable the options to copy, print, or download the file for users with reader and commenter permissions.
  • timeout: Timeout for the activity in ms. The default is 30,000 ms.

Output parameters:

  • Metadata: The metadata of the uploaded file.
Upload File (Google Drive)