Google Sheets – Netsuite Integration With Celigo Integrator.io

Introduction

The process of integrating Google Sheets with Netsuite Integration using Celigo Integrator.io is a complicated one. Data created will flow from Google sheets to Netsuite and back to Google sheets. The current version of the Google Sheets connector isn’t an updated one, and is similar to the HTTP connector. The prerequisites for performing this integration are:

• Celigo Integrator.io account
• Gmail Account
• Netsuite Account
• Basic knowledge of Celigo and NetSuite

Implementation

1. Set up the Google Sheets Connector: Create a flow and name it accordingly. In the export, create a source with the Google Sheets connector as an Application
2. Using a preexisting connection is preferable. Otherwise, add a new connection by clicking on the ‘+’ icon. Name the connection, save and authorize it
3. Setting up the Netsuite Connector: Go to the import/destination section, select Netsuite as an Application and establish the connection. Select the authentication according to the requirement
4. Export Operation:  With the connections are complete, the export operation follows. The export pulls records from the Google spreadsheet. To perform this operation, name the export and select the connection. Then, in the API Name field, select ‘spreadsheets.values’.  Use the GET operation and fill the respective spreadsheet IDs and the range of values you want read. Then click on ‘save’ and close the export
5. Import  Operation: For the import operation, click on the import, assign a name to it and select the Netsuite connection. Set the ‘One to Many’ field to ‘true’ if you have many records and then enter the key of the [array object]. You can also choose to ignore it and let it be the default option. Next, select the record type and operation that needs to be performed (insert or upsert, or update).
6. Mapping of Netsuite Fields: Map the fields from the Google sheets to the Netsuite record fields. This completes the process of importing the record to Netsuite.
7. Updating the Netsuite Values to Google sheet: In order to update the result from Netsuite back to Google sheets, click on the ‘+’ in the Destinations/Lookups. Then select the Google sheets in the application, select the existing Google sheets connection and create another import or lookup for updating the sheet. Assign a name to the lookup and set the ‘One to Many’ field based on the previous selection. The HTTP method would be PUT and the Relative URI should be of the format v4/spreadsheets/{{spreadsheetId}}/values/{{range}}?valueInputOption=RAW
8. The HTTP Request body should be in the following format:
   {

 “majorDimension” : “ROWS”,

 “range” : “{{range}}”,

“values”: [

  [“{{internalId}}”,”{{error}}”]

      ]

}

9. Note that the range should be the range of the cells you want updates, and the fields are the required fields from Netsuite
10. The last step for completing the design of the flow is to test the flow and go through the mappings once more

Tips

1. Refer to the Google developer docs for info on the operation on the spreadsheets
2. The spreadsheets can be noted from the URL of the spreadsheet i.e, the URL would be of this format https://docs.google.com/spreadsheets/d/{spreasheetID}/
3. For the mapping, ensure that the range iterates accordingly while updating the values for Netsuite to Google sheet. You can make use of Hooks (javascript functions) for the iteration
4. Ensure you have the required permissions for your Netsuite role

Gallery

Flow  

Mapping from Google Sheets to Netsuite