Files on Google drive

[MasomaKhatoon]

Hi, I am working on a bot which would read around 72 google sheets in a folder on google drive and perform validations on the rows on the google sheet.  How to get the bot to read the google sheet file names and make sure it goes and reads the unread google sheets. 
thanks

------------------------------
Masoma Khatoon
------------------------------

[devneetmohanty07]

Hi Masoma,

For this specific requirement you can use both Google Drive API and Google Sheet API  . For integrating both these services with Blueprism you can use the the DX exchange connectors as well: Google Drive V3 and Google Sheets V4  

For using the services, you need to create a service account in your Google Workspace and then you can enable the APIs and generate the private keys which will be used in the credential manager of your Blue Prism client. You can refer to the documentations in each of the DX exchange connector page which explains it clearly.

Theoretically there are two straightforward solutions that come to my mind:

First Solution:

- First, you can use the list function call of drives from Google Drive API which will return you all the drive ID's in your Google Drive account.  You can filter out the correct drive id where you want to work

GET https://www.googleapis.com/drive/v3/drives

- Second, you can use the list function call of files from Google Drive API which will return you all the file ID's at the respective 'driveid' parameter which you provide. (Determine the drive id first using the first step)

https://www.googleapis.com/drive/v3/files

- Once all the file id's are determined, iterate through them one by one and you can use the API functions of Google Sheets API. There all the functions accept a parameter called as 'sheetid' which is same as the current 'fileid' you are working on. Pass the aprameter and perform the operation.


Second Solution:


- First, you can use the list function call of drives from Google Drive API which will return you all the drive ID's in your Google Drive account.  You can filter out the correct drive id where you want to work

GET https://www.googleapis.com/drive/v3/drives

- Second, you can use the list function call of files from Google Drive API which will return you all the file ID's at the respective 'driveid' parameter which you provide. (Determine the drive id first using the first step)

https://www.googleapis.com/drive/v3/files

- Once all the file id's are determined, iterate through them one by one and you can use the export function of the Drive API itself to download the files as a bit stream and then you can write the same to any given excel file. This will download the files for you and then you can perform excel operations directly.

GET https://www.googleapis.com/drive/v3/files/fileId/export

NOTE: The second solution won't be using Google Sheets API at all however, the limitation is that you can only download files worth 10 MB if I am not wrong. Moreover, continuous downloading might make your workflow bit complicated as you need to check as well if the files are getting downloaded properly or not

------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Automation Consultant
Blueprism 6x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

[MasomaKhatoon]

Thanks a lot Devneet. It worked. I need to know how the BOT can read the worksheets name in the google sheet. should the worksheet name across all the google sheets be same or can we have different names for worksheets across all google sheets.

------------------------------
Masoma Khatoon
------------------------------

[devneetmohanty07]

Hi Masoma,

Glad that my answer could help you out. In order to determine worksheet names dynamically you can follow the below approach:

Once you've determined the 'spreadsheetId' or 'fileId' whichever convention you want to use for better understanding that corresponds to your Google Sheet file, you can use the below API endpoint:

https://sheets.googleapis.com/v4/spreadsheets/?&fields=sheets.properties

Here, replace the highlighted parameter with the 'fileId' or 'spreadsheetId' that you have determined. This API call will then return you a JSON array comprising of the sheet properties for each sheet present within it. 

Sample response will look something like this:

{
  "sheets": [
    {
      "properties": {
        "sheetId": 867266606,
        "title": "Sheet1",
        "index": 0,
        "sheetType": "GRID",
        "gridProperties": {
          "rowCount": 100,
          "columnCount": 20,
          "frozenRowCount": 1
        }
        "tabColor": {
          "blue": 1.0
        }
      },
      ...
  ],
}

You can reserialize this JSON array and get your desired parameter which in your case would be "title"

If this resolves your query, please do mark the answer as the "Best Answer" so that others can track it easily.

------------------------------
----------------------------------

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant
Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------