This article is relevant if you are a NetSuite Systems Administrator or a power user that is seeking to learn how you can use Google Sheets to directly update NetSuite without resorting to CSV file uploads.
Background
One of our clients in the distribution business has complex purchasing and receiving logistics. The client wanted an easier way to update NetSuite without having to resort to Comma Separated Values (CSV) file uploads. What they sought was a way to use a Google Sheets to update NetSuite quickly and without “IT” file data management.
As purchased goods were “on the water”, multiple records would frequently need to be updated. For those interested, see this article: Finally: Capitalize NetSuite Inventory Costs with Item Consumption Landed Costs . To achieve this tracking objective, they created and shared a Google Sheet with their outside supplier who helped with their inventory logistics management. Thus, the Google Sheet was a great way to collaborate with outside parties that did not have a NetSuite license. Yet, the data on the sheet had to get back to NetSuite to drive the entire inventory operation.
Using Google Sheets to Update NetSuite
With the power of NetSuite’s Restlet technologies, we were able to create a service that allows NetSuite users to update NetSuite records directly from Google Sheets simply by using a custom formula field.
Note, we have produced similar capacities as referenced by our Excel based Cash Receipts Application. However, the difference here is that we seeing so many more Apple desktops in corporate environments; Google Sheets is cross operating system platform compatible where as Excel is much less so. Thus Google Sheets is becoming a great tool for spreadsheets in the cloud.
Simple and Complex Updates
The technology can work on simple and complex records. In the video demo below, I illustrate how we can create and update a simple custom freight container record (noted from the article, Finally: Capitalize NetSuite Inventory Costs with Item Consumption Landed Costs ). Thereafter, I illustrate how you can update a complex Item Fulfillment record to specify quantities and mark as shipped (we could easily have added tracking information for those interested).
Using validation routines, we pass messages back to the user so that we protect the NetSuite database and help the user refine their operational work. Once everything is setup, it is very efficient.
Watch Google Sheet to NetSuite Video
The video (duration 7:33) will illustrate some of the capacities which includes a custom login form to identify the NetSuite user so we can connect up with our custom Restlet based backend technologies.
Refine the Google Spreadsheet for the Business User
I call this video “raw” in that it is designed to help illustrate the power but much less the form. In an actual business implementation, we would work to format the spreadsheet so it makes complete sense to the user. We would setup the formulas ahead of time so that it may be as simple as a formula copy and paste to rapidly update a large batch of NetSuite records.
Leverage the Google Sheet Applicator Framework
We offer this capacity to our clients as part of our general implementation efforts. We have bundled up the framework to help kick start any effort. Simply, if the SuiteScript API is open on the target record, we can then create or update records from Google Sheets in a controlled manner. Do you have an NetSuite Google Sheet application in mind? If so, let’s have a conversation.
I would like to dynamically update Google Sheets with the results of a Saved Search or Report.
Basically I would like Excels Web Query functionality in Google Sheets.
Can you point me in the right direction?
Hi Reed,
This pattern can work with Excel if we adapt it. Also, did you see this recent article?: https://blog.prolecto.com/2018/02/04/how-to-connect-netsuite-report-to-excel-web-query
Marty