Microsoft Excel Online is the spreadsheet software that is included as part of Office 365, the office productivity suite of apps from Microsoft. We currently integrate with the Office 365 version ONLY as the personal Excel Online APIs are still in beta and not officially supported
Setting up your Microsoft Excel
As a best practice, we recommend setting up the first row in your spreadsheet as the Column Names. However, the integration works as such that it will pick up the first non-empty row as the Headers for the sheet and in case values are not provided, it will use unique IDs to reference them. Please note, Column Headers are case sensitive.
How to Find and Update an existing Row
Let's say you have a sheet with Columns like Name, Email, and Phone number. And assume you have Name, Email columns filled-in but Phone numbers are not.
Assume you have a Website Form which takes a person's email and phone number. Let's say you want to now go to the sheet and add the phone number data corresponding to the email entered.
Let's see how this can be done.
The ‘Update Row’ action can be used for this. Once you select the Workbook and Worksheet, the Columns will be visible as Input fields.
The tricky part is in specifying which row to update – Row Number. In most cases, this data is not available.
In such cases, you will need to use the ‘Search’ option for Row Number. This will add a ‘Search Rows’ action (image below) before the Update Row action.
The idea here is to search for the Row containing the Email, get the row Number, and use it in the next Action 'Update Row'.
So, in the 'Search Row' action, you can specify the Column to search in, the value to search for (the First coming from the Form).
This will fetch the matching Row data into the output fields including the Row Number. Now in the 'Update Row' action, provide this Row ID and also the data of All the columns. So you are essentially reusing the same data (coming from Search Action) for Name and Email, and add the New data (phone number) coming from the Form.
New Row Trigger
Limitation of New Row Trigger
- Avoid empty rows in your sheet. The trigger will only fire when you add a new row immediately after the currently filled rows.
- Avoid Deleting or Modifying existing data in the sheet. If you need to it, Disable the Bot first, make necessary changes, then Edit your bot to check if the output fields are still valid and enable it again when you are done.
- Changing column names will cause problems. If you change the names, you will need to Edit the Bot and check all your input/output field mappings again and re-enable the Bot.
- Manual data entry into the sheet may sometimes cause issues. The Bot may pickup data before you finish typing the data into the row and hence not function properly. A
workaroundis to enter data into a different sheet and copy the entire row(s) into the actual sheet.
Working with Formulas
When using the 'Add Row' action, if one of your Columns needs to be a Formula, you will need to add the Formula in the Bot itself (and not in the Sheet). Adding a Formula in the Sheet and dragging it will cause the Bot to treat all those rows as filled rows and it will start adding new rows below the dragged formula.
For example, you can add a SUM Formula to the Sheet Column from the Bot like this
Saving Phone Numbers
When you add phone numbers to a google sheet with the 'Add Row' action, there may be an issue sometimes when the number starts with a plus ( + ). As a work around, you will need to edit the Bot and add a single quote ( ' ) at the beginning of the phone number field.