This is my process: I begin by crafting a .dll containing two public functions. One function's purpose is to retrieve the bearer token for executing HTTP requests, while the other is designed to read a sheet and store its contents as a CSV file.
I then execute the .dll through PowerShell. When it comes to reading data, I've devised two approaches. The first method is suitable for handling large datasets. In this approach, I provide the service account's .json credential path, the spreadsheet ID, sheet name, and the desired CSV output path. Then I can read the csv without problems.
For reading a single cell's content, is the same as writing process but I change the http request to use the one to read.
Switching to the writing process, I initiate by checking if a token has been generated within the past hour. If a token exists, I log it in a text file for reference. If the token has expired, I utilize PowerShell to create a new token, requiring the service account's .json credential path, and then save it to the text file.
Once armed with the valid token, I proceed to make an HTTP request to the Google API. This request includes the sheet name, range, and the value to be written.
It's important to note that for this entire process to function seamlessly, the file must be shared with the service account email to ensure proper access and interaction with the Google API.
------------------------------
Felipe Andres Barrera Torres
------------------------------
Original Message:
Sent: Thu August 17, 2023 10:48 AM
From: Vinicius Marques
Subject: Google Sheets integration
Does anyone know how to best interact (write and read values) on a Google Sheets file using IBM RPA? The final file HAS to be in Google Sheets format because we'll need to write values, run a plugin and then read the output that will be available on another cell there.
------------------------------
Vinicius Marques
------------------------------