M365 Excel (.gov.sg Agencies only)
What is M365 Excel?
M365 Excel is a spreadsheet program used to organize, format, and analyse data using tables, calculations, formulas and graphs. It's part of the Microsoft Office suite and is widely used for tasks like data entry, accounting, financial analysis, and more.
What to take note?
Please make sure to read through this entire document as there may be restrictions that could affect your operations.
There is a cap on total disk space and Excel actions across all Plumber users. Please exercise prudence when using this action. To ensure that storage space is sufficient for all users, versions are deleted when they exceed 90 days since last modified. We maintain a M365 Excel action queue to ensure fairness. If there are many executions on the same file, the changes may not execute immediately. To prevent choking of the system, the Plumber team reserves the right to pause your pipe from using Excel if:
Your pipe exceeds a large amount of actions in a short span of time.
You are storing large files in your folder.
If your use case requires large files (>500 MB), or more than 100 Excel actions per 10 minutes, please contact us at https://go.gov.sg/plumber-support to avoid any disruption to your workflow.
Setting up M365 Excel connection (one-time)
M365 Excel is only available for agencies that can access the SG Govt SharePoint. Or, you may try to access this link on your GSIB, if you're able to access, it means you'll be able to access the integration on Plumber. If your agency can access this SharePoint, but you are unable to see this app in Plumber, please contact us at https://go.gov.sg/plumber-support
To get started, you need to perform a one-time configuration to connect your Plumber account to M365. This will create a shared folder, known as your "Plumber folder," where you can place Excel files for Plumber to access.
Follow the steps below to set up the configuration.
Part 1: Configure on Plumber
Select "Add step" in the pipe editor
Select “M365 Excel” action
Then select the “Create table row” action (or any action you require)
Click on "Connect" and we will create a folder for you to use on the SharePoint site
Click on "Ok, got it!" and the connection is completed. You can now perform M365 excel actions on Plumber!
Part 2: Start creating or moving Excel files into your Plumber folder on the SharePoint site
Go to your OneDrive on the SG Govt SharePoint, and click on the “Shared” link
You will see a new folder named with your email. The subtitle of that folder will be Plumber (OGP). Any Excel files placed in this folder can be processed in a plumber pipe. We recommend that you star this folder for easy access.
You can create a new excel file via the “Add New” button, or move an existing excel file to this folder.
CAUTION: If you want to move files into this folder, make sure that “Keep sharing with the same people” is selected! Otherwise, existing users will lose access to that file!
Available Actions
Plumber does not automatically refresh changes to your excel file. If you make changes (e.g. creating a new table / renaming a column), you need to manually trigger a data refresh. To do this:
After editing your excel file, please wait 1-2 minutes for Microsoft servers to sync. Or you can refresh your excel file.
Navigate to your Plumber pipe and expand the appropriate drop down menu (e.g. if you added a new table, expand the “Table” menu)
Click on the “Refresh Items” button at the bottom of the menu.
You’ll need a table to use any of the actions Please refer to Microsoft’s video here for instructions on creating a table.
Create Table Row
This action allows you to add rows to an Excel table.
Excel File
Sets the Excel file where your Table will reside in.
✔️
Table
This field will only appear after you select an Excel file.
Set the Excel table you want to create a row to.
✔️
New row data
These are the columns and the respective values you want to add when you create a row
✔️
Set up your Excel step by completing the required fields:
Excel File
Table
New row data
Click on Check step to save and create an Excel row in your table in the Excel file selected based on the row data you have set up.
Please do NOT edit the excel table unless you un-publish your pipe first! Editing the table while the pipe is running will result in data corruption!
Find Table Row
This action allows you to find a single table row by looking up a column value.
Find Table Row only supports tables that are smaller than 50,000 rows or 100,000 cells.
It will also use the first table row whose lookup column matches the lookup value.
Excel File
Sets the Excel file where your Table will reside in.
✔️
Table
This field will only appear after you select an Excel file.
Set the Excel table you want to find a row in.
✔️
Lookup column
This is the column you will select to match with your lookup value to find the topmost row that matches.
✔️
Lookup value
This is the value you input for matching with the lookup column. If this is left empty, it will search for an empty value.
Set up your Excel step by completing the required fields:
Excel File
Table
Lookup column
Click on Check step to save and find an Excel row in your table in the Excel file selected based on the lookup column and value you have set up.
Update Table Row
This action allows you to update a single table row whose column matches a lookup value
Update Table Row only supports tables that are smaller than 50,000 rows or 100,000 cells.
It will also update the first table row whose lookup column matches the lookup value.
Excel File
Sets the Excel file where your Table will reside in.
✔️
Table
This field will only appear after you select an Excel file.
Set the Excel table you want to update a row in.
✔️
Lookup column
This is the column you will select to match with your lookup value to find the topmost row that matches.
✔️
Lookup value
This is the value you input for matching with the lookup column. If this is left empty, it will search for an empty value.
Row data
These are the columns and the respective values you want to overwrite and update the found row with
✔️
Set up your Excel step by completing the required fields:
Excel File
Table
Lookup column
Row data
Click on Check step to save and update an Excel row in your table in the Excel file selected based on the lookup column and value you have set up.
List of agencies allowed for M365 integration
This list was last updated on 29 Nov 2024.
If you're user with the email domains "x.gov.sg" and you're not able to access M365 app, please raise a ticket with us at https://go.gov.sg/plumber-support
@tech.gov.sg
@schools.gov.sg
@sport.gov.sg
@defence.gov.sg
@mom.gov.sg
All .edu.sg
@mindef.gov.sg
All healthcare institutions
@nlb.gov.sg
@mnd.gov.sg
@singstat.gov.sg
@wsg.gov.sg
@bca.gov.sg
@spf.gov.sg
@thedigitalacademy.tech.gov.sg
@mas.gov.sg
@nea.gov.sg
@hdb.gov.sg
@moh.gov.sg
@mlaw.gov.sg
@judiciary.gov.sg
@nyc.gov.sg
@moe.gov.sg
@pub.gov.sg
@customs.gov.sg
@iras.gov.sg
@msf.gov.sg
@htx.gov.sg
@scdf.gov.sg
@mci.gov.sg
@mha.gov.sg
@stb.gov.sg
@jtc.gov.sg
@lta.gov.sg
@vital.gov.sg
@pris.gov.sg
@caas.gov.sg
@hsa.gov.sg
@gra.gov.sg
@ssg.gov.sg
@ica.gov.sg
@pa.gov.sg
@acra.gov.sg
@mfa.gov.sg
@cpf.gov.sg
@pmo.gov.sg
@toteboard.gov.sg
@csa.gov.sg
@hpb.gov.sg
@mddi.gov.sg
@mpa.gov.sg
@psd.gov.sg
@nparks.gov.sg
@enterprisesg.gov.sg
@mti.gov.sg
@sfa.gov.sg
@muis.gov.sg
@ncss.gov.sg
@hta.gov.sg
@imda.gov.sg
@mccy.gov.sg
@edb.gov.sg
@cscollege.gov.sg
@nac.gov.sg
@ura.gov.sg
@agc.gov.sg
@agd.gov.sg
Common Errors
Table too large (for find and update table row actions)
This happens when your table has more than 50k rows, please reduce the number of rows before running the execution again.
Invalid column in Excel table (for any action)
The column is most likely deleted from your table, please reselect the correct column before running the execution again.
403 forbidden
You are most likely attempting to perform actions on a protected sheet.
Remove the protection and try again.
504 gateway timeout error
You most likely have a long-running formula in your excel sheet, please set your formulas to manual instead of automatic to prevent issues with creating a table row.
Follow these steps: click on Formulas -> Calculation Options-> Manual
FAQ
Why are only ".gov.sg" email domains given access to M365?
This restriction is in place because the ".gov.sg" email domains share the same system on SG SharePoint, which is the only system integrated with the product. The other domains reside on a separate SharePoint system to which the product does not have access.
Why couldn't Plumber detect the table in my Excel sheet?
You need to set up a table by following the instructions in the Excel Tables overview article in Microsoft Support.
What is the meaning of disallowed email domains?
Users will not be able to use the M365 excel integration on Plumber. The reason is because the users are not able to access SG sharepoint.
Why does the date from Excel display as numbers on Plumber?
Excel stores dates internally as serial numbers (days since January 1, 1900). When Excel doesn't recognise your input as a proper date format, it may display these serial numbers instead of formatted dates.
To fix this:
Select the cells containing the number-dates (or click the column letter to select the entire column)
Right-click and choose "Format Cells"
In the Number tab, select "Date" from the Categories list
Choose your preferred date format from the Type list
Click OK
Will the team work towards opening M365 to everyone?
There are multiple systems across Singapore Government and healthcare settings. We aim to make these available to as many users as possible, so please bear with us while we work behind the scenes.
Why does my response take a while to appear in M365 excel?
We have implemented a queuing system to ensure fairness. If there are many automations using M365 running at the same time, your request may not execute immediately. Please allow around half an hour for your response to show up. The response ultimately resides on FormSG. Alternatively, you may also set up Tiles in your action.
Can I use macros along with this integration?
No, it is not possible to use macros with this integration. Plumber will not be able to detect the excel sheet if it's macros-enabled.
How do I know if I am able to access M365 integration on Plumber?
You can try to access the https://gccprod-my.sharepoint.com with your GSIB.
If you are able to access it, you should be able to use the integration on Plumber.
I cannot find my Plumber folder on SharePoint. What should I do?
Open SharePoint: https://gccprod-my.sharepoint.com/
Click on 'Shared'
Click on the Folder icon to filter by folders
Why can't I see my Excel table on Plumber?
Please ensure you have set up a table in Excel. For more information, view the Microsoft guide here.
Last updated