RSSBus Excel Formulas
You can customize your spreadsheets with data from NetSuite using the formulas provided by the RSSBus Excel Add-In for NetSuite: RSSBUSQUERY, RSSBUSINSERT, RSSBUSUPDATE, and RSSBUSDELETE. You can refer to specific cells in the function parameters and rely on the add-in to automatically refresh the results. Since Excel formulas are stored within the Excel file you can easily share your dynamic spreadsheets with others.
RSSBUSQUERY
=RSSBUSQUERY(Query, [Connection], [Parameters], [ResultLocation])The RSSBUSQUERY function will execute a SELECT, INSERT, UPDATE, or DELETE statement or a stored procedure call. The cell where the formula is entered will show the number of rows returned or the number of rows that have been modified; subsequent rows will show the results.
| Query | The Query argument is a string containing the query; for example, SELECT * FROM AccountThe query may be parameterized, in which case the parameter values are read from the range defined in the Parameters argument. |
| Connection | The Connection argument defines the connection either as the connection name, such as NetSuite.Connection1 (see Managing Connections), or as a connection string, such as Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;Provider=NetSuite. If you use a connection string, you must include the Provider property in the connection string. The Connection argument is optional if the sheet is already bound to a table: You automatically bind a sheet to a table when you use the Data Selection wizard to execute a query. |
| Parameters | The Parameters argument specifies the range of cells that contains the names and values for the parameters in a query like the one below:
SELECT InternalId, AcctName FROM Account WHERE acctName = @Param1 AND acctName = @Param2;For example, if the range A10:B11 contains the parameters then the cells A10:B10 should contain the parameter names @Param1 and @Param2, respectively, and the cells A11:B11 should contain the parameter values for Param1 and Param2. |
| ResultLocation | The ResultLocation argument is a string that specifies the cell where the row-by-row output of the results will start. By default, RSSBUSQUERY will output the results just below the cell where the formula was entered; however, if ResultLocation is specified, the results will be written from the specified cell onwards. |
RSSBUSINSERT
=RSSBUSINSERT(Data, [Table], [Connection], [ColumnHeaders])The RSSBUSINSERT function will insert the specified rows into the specified table.
| Data | The Data argument specifies the range of rows to be inserted using the standard Excel range format, such as J15:L15. The first row must contain the column headers unless they are specified in the ColumnHeaders argument; the range will then specify only the values to be inserted. |
| Connection | The Connection argument defines the connection, either as the connection name, such as NetSuite.Connection1 (see Managing Connections), or as a connection string; e.g., Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;Provider=NetSuite. If you use a connection string you must include the Provider property in the connection string. The Connection argument is optional if the sheet is already bound to a table: You automatically bind a sheet to a table when you use the Data Selection wizard to execute a query. |
| Table | The Table argument is a string that specifies the table to insert data into. This argument is optional if the current sheet is bound to a table, as explained above. |
| ColumnHeaders | The ColumnHeaders argument specifies the range of cells that contains the column headers. The ColumnHeaders argument only needs to be specified if the column headers are not in the first row of data. You can also use the ColumnHeaders argument to control the orientation of the data table: If the column headers are located in a vertical array, such as B20:B30, then each column is inserted as a row into the chosen table. |
RSSBUSUPDATE
=RSSBUSUPDATE(Data, [Table], [Connection], [ColumnHeaders])The RSSBUSUPDATE function will update the data from the specified rows into the specified table.
| Data | The Data argument defines the rows to be updated, using the standard Excel range format, such as J15:L15. The first row must contain the column headers, unless the ColumnHeaders argument is also defined; the range of data will then specify only the values to be inserted. Each row of data to be inserted must contain the primary key. |
| Connection | The Connection argument defines the connection, either as the connection name, such as NetSuite.Connection1 (see Managing Connections), or as a connection string, such as Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;Provider=NetSuite. If you use a connection string you must include the Provider property in the connection string. The Connection argument is optional if the sheet is already bound to a table: You automatically bind a sheet to a table when you use the Data Selection wizard to execute a query. |
| Table | The Table argument is a string that specifies the table to update. If the current sheet is bound to a table, as explained above, then the Table argument is optional. |
| ColumnHeaders | The ColumnHeaders argument specifies a range of cells that contains the column headers. The ColumnHeaders argument only needs to be defined if the column headers are not in the first row of the specified data. You can also use the ColumnHeaders argument to control the orientation of the data table: If the column headers are located in a vertical array, such as B20:B30, then each column is updated as a row into the chosen table. |
RSSBUSDELETE
=RSSBUSDELETE(Data, [Table], [Connection], [ColumnHeaders])The RSSBUSDELETE function will delete the specified rows into the specified table.
| Data | The Data argument specifies the rows to be deleted, using the standard Excel range format, such as J15:L15. The first row of the range must contain the column headers unless the ColumnHeaders argument is also defined; the range of data will then specify only the values to be inserted. Each row of data to be inserted must include the primary key. |
| Connection | The Connection argument defines the connection, either as the connection name, such as NetSuite.Connection1 (see Managing Connections), or as a connection string, such as Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;Provider=NetSuite. If you use a connection string you must include the Provider property in the connection string. The Connection argument is optional if the sheet is already bound to a table: You automatically bind a sheet to a table when you use the RSSBus Data Selection wizard to execute a query. |
| Table | The Table argument is a string that specifies the table from which the data is to be deleted. If the current sheet is bound to a table then the Table argument is optional. |
| ColumnHeaders | The ColumnHeaders argument specifies the range of cells that contain the column headers. The ColumnHeaders argument only needs to be specified if the column headers are not in the first row of the specified data. You can also use the ColumnHeaders argument to control the orientation of the data table: If the column headers are located in a vertical array, such as B20:B30, then each column is updated as a row into the chosen table. |
Examples
The examples in this section are based on the range of data J14:L17, shown below. For conciseness, we only show the cells with data, which makes it appear as if the data is top-left aligned, but a top-left alignment is not required: the rows of data can exist anywhere on the spreadsheet.
------------------------------------------------------- | | J | K | L | ------------------------------------------------------- | 14 | Id | Name | City | ------------------------------------------------------- | 15 | 21 | Ron | ATL | ------------------------------------------------------- | 16 | 22 | Gary | SFO | ------------------------------------------------------- | 17 | 23 | John | NYC | -------------------------------------------------------
=RSSBUSDELETE(J14:L15,"Contacts",NetSuiteConnection): This formula deletes the row with the Id 21 from the table.
=RSSBUSINSERT(J14:L17,"Contacts",NetSuiteConnection): This formula inserts the contacts Ron, Gary, and John into the Contacts table. This insert will assign a new Id to each row. To refresh the Ids use the RSSBUSSELECT formula below.
=RSSBUSQUERY("SELECT * from Contacts",NetSuiteConnection,,"J14"): This formula selects all contacts and outputs them starting at row J14.
=RSSBUSUPDATE(J17:L17,"Contacts",NetSuiteConnection,J14:L14): This formula updates the city to NYC and the name to John for Id 23.