How To Read Google SpreadSheet Data In UiPath

UiPath Read Google SpreadSheet

Step 1: To create a google spreadsheet

All about Google Sheets

Google Sheets is a web-based spreadsheet application that allows you to store and organize different types of information, much like Microsoft Excel . While Google Sheets does not offer all of Excel’s advanced features, it’s easy to create and edit spreadsheets ranging from the simple to the complex.

While you might think spreadsheets are only used by certain people to process complicated numbers and data, they can actually be used for a variety of everydaytasks . Whether you’re starting a budget, planning a garden, or creating an invoice or just about anything else you can think of, spreadsheets are a great way to organize information.

To create a new Google spreadsheet:

While viewing your Google Drive, click New and select Google Sheets from the drop-down menu.

The spreadsheet will appear in a new browser tab .

To name your spreadsheet, locate and select Untitled spreadsheet at the top of the page. Type a name for your spreadsheet, then press Enter on your keyboard. Your spreadsheet will be renamed .

You may notice that there is no save button. This is because Google Drive uses autosave , which automatically and immediately saves your files as you edit them.

After you share a file.Open a file in Google Docs , Sheets, or Slides. Click “Get shareable link” in the top right of the “Share with others” box. To choose whether a person can view , comment, or edit the file, click the Down arrow next to “Anyone with the link.”

Step 2: To Create a Google Cloud Platform

Create your service account

Go to the API Console.

Sign in to the Google API Console .

From the projects list, select a project or create a new one.

If the APIs and services page isn’t already open, open the console left side menu and select APIs and SERVICES , and then select LIBRARY .

Click the Sheet and drive you want to enable. Click Enable

Once You’ve enable the APIs, you must generate credentials that will allow your robot to access the service.

Click Create credentials > Service account key .

From the dropdown menu, select New service account . Enter a name for your service account.

Select your Json key type and click Create . Your new public/private key pair is generated and downloaded to your machine and is the only copy of this key. You are responsible for storing it securely.

Step 3: Uipath Studio

• First, you will download a Google GSuite. https://go.uipath.com
• Then, open UiPath studio and start a new blank project.
• Click on the “Manage Packages” icon indicated in the picture
• In the new window select “Settings” .
• Click on “+” button to add a new feed source.
• Enter Source as ‘C:\Users\Administrator\Downloads’ and click ok.
• Drag & drop a sequence in the Designer Panel.
• Drag & drop “GSuite Application scope” activity below the sequence in Designer panel. Note that it can be searched from activity panel.
    ◦ AuthenticationType: Select Service Account
    ◦ Services: Select Drive & Sheet
• Drag & drop a Find File and Folder  activity below the Gsuite application scope.
    ◦ SearchExpression- Defines the file search parameters, such as file types and file names. For more information about builing your search query, see Search For shared drive in the Google Drive API documentation.
    ◦ FeelingLucky- The File ID of the first result. You can use this ID in other activity interacting with Google Drive files like Google Sheets
    ◦ Result- Search result returned as an array of Google Files. The File data structure includes information about the file (e.g type, size, and ID ) that you can use in other activities interacting with Google Drive files like Google Sheets. For more information about the File data Structure, see Resource representation in the Google Drive API documentation. 
• Drag & drop a Read Range activity.
    ◦ Range- Defines the range of content to be read from the SpreadSheet. If left empty, the entire  Sheet from the Sreadsheet file is read.
    ◦ SheetName- Defines the Sheet in the document from which the range is read. 
    ◦ SpreadSheetID- The ID of the SpreaSheet from which to read the data. To get a spreadsheetIS directly in UiPath, use the Find File And Folders activities from the GSuite Drive package.
    ◦ Result- The result of operation given as a DataTable.
• Drag & drop a For Each Row activity.
    ◦ DataTable- Add a Read Range Output.
• To do that drap the Message Box activity and mention “FirstNae”.row.item(0) this will read in the sheet1 and Will print the output.

9 Likes

This was perfect…!
Was this supposed to be an answer in itself ? :smiley:

Regards…!

So whats question in this how to use oauth authentication if you don’t have google cloud paid services?

I have followed the same instructions, but not able to access the whole list of files (files in the drive). When I debug the issue, I am only getting the service account file (The name shows as “Getting started”). Even though I have multiple google sheets in the drive. But still the solution works if I hard code the data (spreadsheet Id) in read range activity. But I don’t want to do the hard coding. I want to access all the files in the drive and use the Id dynamically. Can you please help out.

Thanks…!!

  • The screenshots of gsuite activity and find file and folder is listed.

FindFolder

1 Like

Hello, can u tell me how to read spread sheet data to any web page or application eg. google form?
i used type into its not working

New to working with Google API, have tried all these options below yet I get 404 file not found from Studio…

File on my google drive…

Searching thru GoogleAPI finds the file
Note : FileId not the ‘name’ is used as parameter to the query. Auth 2.0 is the choice as ServiceAccount is not an option

I have confirmed that json key file and other config parameters are working fine (by giving wrong values and getting different/appropriate error msgs not 404)

Thanks

Hey Manu,

Here a video on how to use these activities.

2 Likes