Read and Write From Google Sheets Golang

How to read from and write into Google Sheets from your robots

Google Sheets, part of Google Workspace, allows teams to create and share spreadsheets online and has grown into a widely used business organisation tool. For many processes, all you lot need is a shared spreadsheet and a robot using it!

Which automation library should you utilise?

The RPA.Cloud.Google library, part of RPA Framework, enables interaction with Google Sheets.

Installation and setup

Your robots will need to cosign with Google to be able to interact with Google Sheets spreadsheets, using the concept of service accounts. The account used by the robot will and so demand to be added as a collaborator to the canvas(s) y'all want to access.

Create a Google Service Account

To admission the data stored in Google Sheets, you volition need to create a service account and get a ready of OAuth2 credentials from the Google API Console.

1. Access the Google APIs Console while logged into your Google account.
2. Create a new project and requite information technology a name.
3. Click on ENABLE APIS AND SERVICES.
4. Find and enable the Google Canvass API.
5. Create new credentials to the Google Sheets API. Select Other UI from the dropdown and select Awarding Data. Then click on the What credentials practice I demand? button.
6. On the next screen, cull a proper name for your service business relationship, assign it a role of Project->Editor, and click Continue.
7. The credentials JSON file will be downloaded by your browser.

   The credentials file allows anyone to access your cloud resources, then you lot should store it deeply. More data from Google.

8. Discover the downloaded file and rename it to service_account.json.

Create a new Google Sheet and add the Service Account equally an editor to it

1. Create or select an existing Google Canvas.
2. Open the service_account.json file and find the client_email belongings.
3. Click on the Share button in the summit right, and add the email address of the service business relationship as an editor.

   If you want merely to let the business relationship read access to the spreadsheet, assign it the Viewer role instead.

4. Take notation of the ID of the Google Sheet document, which is contained in its URL, later the /d chemical element. So, for case, if the URL of your certificate is https://docs.google.com/spreadsheets/d/1234567890123abcf/edit#gid=0, the ID volition exist 1234567890123abcf.

Robot example

Now that our account setup is complete, nosotros will build a robot that:

1. Reads the existing data from a Google Spreadsheet and logs it.
2. Adds more data to the Google Sail.

Here's our example spreadsheet with some examination data:

Create a new robot and add together the RPA.Cloud.Google library

1. Create a new robot using the VS Lawmaking Robocorp extension.
2. Edit the conda.yaml file in your robot like this:

                          channels:   - conda-forge dependencies:   - python=3.seven.v   - pip=xx.i   - pip:       - rpaframework-google==1.0.ii                                    

The google package in RPA Framework is not included past default because of the size of its dependencies. Past adding the - rpaframework-google==1.0.2 line you are adding it explicitly to your robot.

Robot script

Important! Remember to add the service_account.json file to the root directory of your robot.

                          *** Settings *** Documentation     An example robot that reads and writes data ...               into a Google Sheet document. Library           RPA.Cloud.Google Suite Setup       Init Sheets    service_account.json  *** Variables *** ${SHEET_ID}       1234567890123abcf ${SHEET_RANGE}    Sheet1!A2:D10  *** Tasks *** Read values from the Google Sail     ${spreadsheet_content}=    Get Sheet Values     ...    ${SHEET_ID}     ...    ${SHEET_RANGE}     Log Many    ${spreadsheet_content["values"]}  *** Tasks *** Add values to the Google Sheet     ${values}=    Evaluate    [["Marking", "The Monkey", 100000, 10000]]     Insert Sheet Values     ...    ${SHEET_ID}     ...    ${SHEET_RANGE}     ...    ${values}     ...    ROWS                                    

Robot script explained

                          *** Settings *** Documentation     An example robot that reads and writes data ...               into a Google Sail certificate. Library           RPA.Cloud.Google Suite Setup       Init Sheets    service_account.json                                    

In the *** Settings *** section, the Documentation setting explains what our robot does. Nosotros then add together the RPA.Cloud.Google library. Finally, we use the Suite Setup setting to initialize the Google Sheets client. This manner, it will be initialized just once, even if our robot has multiple *** Tasks *** sections.

Y'all can learn more about Suite Setup and Teardown in the Robot Framework User Guide.

                          *** Variables *** ${SHEET_ID}       1234567890123abcf ${SHEET_RANGE}    Sheet1!A2:D10                                    

In the *** Variables *** section, nosotros set two variables:

• ${SHEET_ID} volition hold the id of our Google Canvass certificate.
• ${SHEET_RANGE} is the range of cells that we want to work on, written in A1 notation. In our example, the expanse we are interested in in our spreadsheet starts from the A2 cell, and ends with the D10 cell of the outset sheet, so our value will be Sheet1!A2:D10.

                          *** Tasks *** Read values from the Google Canvas     ${spreadsheet_content}=    Go Sheet Values     ...    ${SHEET_ID}     ...    ${SHEET_RANGE}     Log Many    ${spreadsheet_content["values"]}                                    

In this chore, we are reading the rows specified by the ${SHEET_RANGE} of our Google Sheet, which is identified past the ${SHEET_ID}, into the ${spreadsheet_content} variable.

The Get Sheet Values keyword returns a dictionary with a values item in information technology containing a list of rows. Using the Log Many keyword, we can log that row data:

                          *** Tasks *** Add values to the Google Sail     ${values}=    Evaluate    [["Mark", "The Monkey", 100000, 10000]]     Insert Canvass Values     ...    ${SHEET_ID}     ...    ${SHEET_RANGE}     ...    ${values}     ...    ROWS                                    

In this job, we add some arbitrary data to a new row in the spreadsheet.

1. Using the Evaluate keyword, nosotros create a variable with the values for the row.
2. We pass the values, the sheet id, and range to the Insert Sheet Values keyword. The values will be added to the first bachelor row using the ROWS major dimension pick.

Storing the credentials in Control Room Vault

You should never include passwords or credential files direct into the code of your robot. Instead of reading the credentials from the service_account.json file, our robot tin can use the vault feature of Control Room.

1. Ready upwardly your robot to run in Control Room
2. Create a new vault in the robot'southward workspace. Assign it the name GoogleSheets.
3. Create a new surreptitious in the vault. Give it a key of service_account, and paste the contents of the service_account.json file into the value field:
4. Modify the ***Settings*** section of the script to configure the RPA.Cloud.Google library to utilise the vault:

                                     *** Settings *** Documentation     An example robot that reads and writes information ...               into a Google Sheet certificate. Library           RPA.Cloud.Google ...               vault_name=GoogleSheets ...               vault_secret_key=service_account Suite Setup       Init Sheets    use_robocorp_vault=True                                                

December ix, 2021

mcdanielyoutund.blogspot.com

Source: https://robocorp.com/docs/development-guide/google-sheets/interacting-with-google-sheets

Share this post