Example of using Go to Cell action from Google Sheets package

Build a bot that loops through the records in a Google Sheet and creates a report based on the records.

Prerequisites

  • Set up an OAuth2 application in Google Cloud Platform. For more information, see Set up OAuth2 in the Google Cloud Platform.
  • Configure OAuth connection in the Control Room. For more information, see Configure OAuth 2.0 connection in Control Room.
  • Make sure you have a spreadsheet with some sample product inventory data in Google Drive. If you do not have any sample data, you can download this spreadsheet and copy the data from it to a Google Sheet in your Google Drive. Make a note of its file ID.
  • Create another Google Sheet where the bot can copy data from the inventory list. Name the file 'restock" and make a note of the file ID.

In the example, the bot loops through an inventory list in a Google Sheet and finds products that need to restocked. After that, the bot updates another Google Sheet with the details of the products that require restocking.

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. 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 gsheet.
    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.
  3. Open the spreadsheet that contains the inventory list.
    1. From the Actions panel, find and add Google Sheets > Open spreadsheet action to the Bot editor.
    2. In Username, select Insecure string and enter the email ID associated with the OAuth connection.
    3. In Session name, enter gsheet.
    4. In Open sheet, click From Spreadsheet id and then enter the file ID of the spreadsheet that contains inventory data in Google Drive.
    5. Select Sheet contains header.
    6. Save changes.
  4. Copy the inventory data to a Data table variable.
    1. From the Actions panel, find and add Google Sheets > Get multiple cells action to the Bot editor.
    2. In Session name, enter gsheet.
    3. In Cell option, click All cells.
    4. In Assign the output to variable, click (x) and create a Data table variable named InventoryTable.
    5. Save the changes.
  5. Close the current Google Sheet.
    1. From the Actions panel, find and add Google Sheets > Close action to the Bot editor.
    2. In Session name, enter gsheet.
  6. Open the Google Sheet where you want to copy information about the items that need to be restocked.
    1. From the Actions panel, find and add Google Drive > Open spreadsheet action to the Bot editor.
    2. In Username, select Insecure string and enter the email ID associated with the OAuth connection.
    3. In Session name, enter gsheet.
    4. In Open sheet, click From Spreadsheet id and then enter the file ID of the spreadsheet to which you want to copy the items to be restocked.
    5. Select Specific sheet name.
    6. Save changes.
  7. Select the second row in the current spreadsheet.
    1. From the Actions panel, find and add Google Sheets > Go to cell action to the Bot editor.
    2. In Session name, enter gsheet.
    3. In Cell options, click Specific cell and then enter A2 in Cell name.
    4. Select Specific sheet name.
    5. Save changes.
  8. Loop through the records in the data table variable.
    1. From the Actions panel, find and add Loop > Loop action to the Bot editor.
    2. In Iterator, choose Data table > For each row in table .
    3. In In Table variable, select the InventoryTable variable.
    4. In Assign the current row to this variable, click (x) and create a record variable named TableRow.
    5. Save changes.
  9. The bot selects only the rows where the current stock is below the reorder threshold. To compare the current stock value to the reorder threshold for each item in the inventory, you need two variables.
    1. From the Actions panel, find and add String > To number action with the Loop action.
    2. In Enter the string, enter $TableRow[4]$to get the reorder threshold value for the current item.
    3. In Assign the output to variable, click (x) and create a Number variable named ReorderThreshold.
    4. From the Actions panel, find and add String > To number action with the Loop action.
    5. In Enter the string, enter $TableRow[3]$to get the current stock value for the current item.
    6. In Assign the output to variable, click (x) and create a Number variable named currentStock.
    7. Save the changes.
  10. To check if the current item is below the reorder threshold, use the If action.
    1. From the Actions panel, find and add If > If action with the Loop action.
    2. In Condition, select Number > > Number condition .
    3. In Source value, enter $currentStock$.
    4. In Operator, choose Less Than (<).
    5. In Target value, enter $ReorderThreshold$.
    6. Save the changes.
  11. Copy the item ID to the restock sheet.
    1. From the Actions panel, find and add Google Sheets > Set cell action within the if action.
    2. In Session name, enter gsheet.
    3. In Cell options, click Active cell.
    4. In Cell value, enter $TableRow[0]$.
    5. Save the changes.
  12. Move the cursor to the next column.
    1. From the Actions panel, find and add Google Sheets > Go to cell action within the If action.
    2. In Session name, enter gsheet.
    3. In Cell options, click One cell to the right.
    4. Save changes.
  13. Copy the product name to the restock sheet.
    1. From the Actions panel, find and add Google Sheets > Set cell action within the if action.
    2. In Session name, enter gsheet.
    3. In Cell options, click Active cell.
    4. In Cell value, enter $TableRow[1]$.
    5. Save the changes.
  14. Move the cursor to the next column.
    1. From the Actions panel, find and add Google Sheets > Go to cell action within the If action.
    2. In Session name, enter gsheet.
    3. In Cell options, click One cell to the right.
    4. Save changes.
  15. Calculate the quantity needed for reordering the current item.
    1. From the Actions panel, find and add Number > Decrement action within the if action.
    2. In Enter number, enter $ReorderThreshold$.
    3. In Enter decrement value, enter $currentStock$.
    4. In Assign the output to variable, click (x) and create a Number variable named quantityToStock.
    5. Save the changes.
  16. Write the quantity to reorder in the Quantity to Restock column.
    1. From the Actions panel, find and add Google Sheets > Set cell action within the if action.
    2. In Session name, enter gsheet.
    3. In Cell options, click Active cell.
    4. In Cell value, enter $quantityToStock.Number:toString$.
    5. Save the changes.
  17. Move the cursor to the start of the current row.
    1. From the Actions panel, find and add Google Sheets > Go to cell action within the If action.
    2. In Session name, enter gsheet.
    3. In Cell options, click Beginning of the row.
    4. Save changes.
  18. Select the next row before the next iteration.
    1. From the Actions panel, find and add Google Sheets > Go to cell action within the If action.
    2. In Session name, enter gsheet.
    3. In Cell options, click One cell below.
    4. Save changes.
  19. Close the current Google Sheet.
    1. From the Actions panel, find and add Google Sheets > Close action after the Loop action.
    2. In Session name, enter gsheet.
  20. Disconnect from Google Sheets.
    1. From the Actions panel, find and add Google Sheets > Disconnect action after the Loop action.
    2. In Username, select Insecure string and enter the email ID associated with OAuth connection you have set up.
    3. In Session name, enter gsheet.
  21. Run the bot.