Example of using Find file/folder action from Google Drive package

Build a bot that searches Google Drive for a file and creates the file if it is not found in the specified path.

Prerequisites

Imagine you want to create a Google sheet if it does not exist in Google Drive. You can use the Google Drive > Find File/Folder action from the Google Drive package to check if a file already exists in Google Drive. If it does not exist, you can create the file using the Create Workbook action from the Google Sheets package.

This bot checks Google Drive for a Google sheet. If it does not exist in the path specified, the bot creates a Google sheet and copies data from a CSV file to it.

Procedure

  1. Create a new bot.
    1. On the left panel, click Automation.
    2. Click Create new > Task Bot .
    3. In the Create Task Bot window, enter a name for the bot.
    4. Accept the default folder location: \Bots\
    5. To change the default bot storage location, click Choose and follow the prompts.
    6. Click Create and edit.
  2. Open the CSV file that contains data you wan to copy to a Google Sheet.
    1. From the Actions panel, find and add CSV/TXT > Open action to the Bot editor.
    2. In Session name, enter csvsession.
    3. In File path, specify the path to the CSV file from which data should be copied.
    4. If the CSV table has a header, select Contains header.
    5. Select a delimiter as per the delimiter used in the CSV file.
    6. Save the changes.
  3. Read data from CSV and store it in a data table variable.
    1. From the Actions panel, find and add CSV/TXT > Read action to the Bot editor.
    2. In Session name, enter csvsession.
    3. In Assign value to the variable, click (x) and create a variable called TableFromCSV.
    4. Save the changes.
  4. Close the CSV file.
    1. From the Actions panel, find and add CSV/TXT > Close action to the Bot editor.
    2. In Session name, enter csvsession.
    3. Save the changes.
  5. Connect to Google Drive.
    1. From the Actions panel, find and add Google Drive > Connect action to the Bot editor.
    2. In Username, select Insecure string, and enter the email ID associated with OAuth connection you have set up.
    3. In OAuth2 Authentication Mode, select Control Room managed.
    4. Click Pick and select the connection you have configured in Control Room.
    5. Save the changes.
  6. Connect to Google Sheets.
    1. From the Actions panel, find and add Google Sheets > Connect action to the Bot editor.
    2. In Username, select Insecure string and enter the email ID associated with OAuth connection you have set up.
    3. In Session name, enter gsheetsession.
    4. In OAuth2 Authentication Mode, select Control Room managed.
    5. Click Pick and select the connection you have configured in Control Room.
    6. Save the changes.
  7. Before you create a new sheet in Google Drive, check if the file you are trying to create already exists. Create the file only if it does not exist in the specified path. To do this , use the Try Catch actions.
    1. From the Actions panel, find and add Error Handler > Try action to the Bot editor.
    2. Save the changes.
  8. To check if the file already exists, use the Google Drive Find file/folder action.
    1. From the Actions panel, find and add Google Drive > Find file/folder action within the Try block.
    2. In Username, select Insecure string and enter the email ID associated with the OAuth connection.
    3. In Find, click File.
    4. In Source Folder Path, enter the source folder. For example, in this sample scenario, the bot looks for the file in the aa-docs/spreadsheets path.
    5. In Match type, select Exactly matches.
    6. In File/Folder name, enter contacts.
    7. In Returns the list of files or folders, select Dictionary.
    8. Click (x) , and create a dictionary variable named FileFolderDictionarywith subtype as Any.
    9. Save changes.
  9. If the file does not exist, create the file.
    1. From the Actions panel, find and add Google Sheets > Create workbook action within the Catch block.
    2. In Username, select Insecure string and enter the email ID associated with the OAuth connection.
    3. In Session name, enter gsheetsession.
    4. In Workbook name, enter contacts.
    5. In File path, click From my shared location, and enter the parent folder path. For example, in this sample in scenario, the botsearches for the file in the aa-docs/spreadsheets path.
    6. Save changes.
  10. To open a spreadsheet from Google Drive, you need its file ID or the file path. Since this bot creates the file dynamically, you will not have the file path prior to the bot execution. However, you can get the File ID using the Google Drive Find file/folder actionagain.
    1. From the Actions panel, find and add Google Drive > Find file/folder action after the Try Catch blocks.
    2. In Username, select Insecure string and enter the email ID associated with the OAuth connection.
    3. In Find, click File.
    4. In Source Folder Path, enter the source folder. For example, the sample bot in this example looks for the file in the aa-docs/spreadsheets path.
    5. In Match type, select Exactly matches.
    6. In File/Folder name, enter contacts.
    7. In Returns the list of files or folders, click Dictionaryand select the FileFolderDictionary variable.
      The Find file/folder API returns a dictionary with information about matched files. The dictionary contains a key called NumberOfFiles. It provides the count of files that matched. The dictionary also contains keys named File1, File2, and so on, depending on the number of matched files. These keys contain information such as file names and file IDs in JSON format. The following is an example of a dictionary returned by the Find file/folder API call:
      {NumberOfFiles,1},{File1,{"fileName":"contacts","fileId":"171yBUzqH80ZW_KLEp23DwebzkCdDZgnGFWBzB6K9kco"}}
      You can retrieve the file ID by parsing the JSON string, which is the value associated with the key File1.
    8. Save changes.
  11. Parse the JSON string associated with the File1 key.
    1. From the Actions panel, find and add Json > Start session action.
    2. In Data source, select Text and enter $FileFolderDictionary{File1}$
    3. In JSON object session, click Local session and enter jsonsession.
    4. Save the changes.
  12. Retrieve file ID from the JSON object.
    1. From the Actions panel, find and add Json > Get node value.
    2. In JSON node key or path, enter fileId
    3. In Session name, enter jsonsession.
    4. In Save the outcome to a variable, click (x) and create a variable called FileId.
    5. Save the changes.
  13. End the JSON session.
    1. From the Actions panel, find and add Json > End session action.
    2. In Session name, enter jsonsession.
    3. Save the changes.
  14. With the retrieved file ID, open the spreadsheet created in Google Drive.
    1. From the Actions panel, find and add Google Sheets > Open spreadsheet action.
    2. In Username, select Insecure string and enter the email ID associated with the OAuth connection.
    3. In Session name, enter gsheetsession
    4. In Open sheet, click From Spreadsheet Id, and enter $FileId$.
    5. Save the changes.
  15. Write the data table variable to the Google sheet.
    1. From the Actions panel, find and add Google Sheets > Write from data table action.
    2. In Session name, enter gsheetsession
    3. In Sheet options, click Specific Sheet and enter Sheet1 or the sheet name from your target spreadsheet in Sheet name.
    4. In Enter data table variable, select TableFromCSV
    5. In Start Cell value, enter A1.
    6. Save the changes.
  16. Disconnect Google Drive and Google Sheets.
    1. From the Actions panel, find and add Google Drive > Disconnect action.
    2. In Username, select Insecure string and enter the email ID associated with the OAuth connection.
    3. Save changes.
    4. From the Actions panel, find and add Google Sheets > Disconnect action.
    5. In Username, select Insecure string and enter the email ID associated with the OAuth connection.
    6. In Session name, enter gsheetsession
    7. Save the changes.
  17. Run the bot.