When a request to NetSuite from the Google sheet is initiated, prospect, contact, opportunity, and phone call records are created in the NetSuite.
The google sheet will sync the data to NetSuite every hour. The already processed rows will not be considered while syncing the data. The prospect and contact will create in Netsuite, only if it does not exist in NetSuite else it will reuse the existing prospect and contact in Netsuite. The opportunity will create for the prospect. The status of the integration will be added at the end of the line (column name- “NetSuite”). It will be any of “FAILURE”, “FAIL”, or “ SUCCESS” and will have cell color red, yellow, or green respectively.
Point to Note
- The contact and prospect record will not create if there already exists one with the same email address. For contract records, it will only check whether a contact with the same email address exists for the corresponding prospect(set on that line).
- For the mandatory fields (except the hard coded fields) the value must be available on google sheet.
- For dropdown fields in NetSuite the value name should be an exact match (case sensitive)with NetSuite . Otherwise it will throw an error.
- Email entered for the prospect and contact will be considered as the unique key for those records.
- The integration will be scheduled to run every hour.
- The order of the columns in the spreadsheet shouldn’t be changed.
- New columns shouldn’t be added and the existing columns shouldn’t be removed from the sheet.
- The “NetSuite” column (column for showing the status)will be added as the 26th column (z) so any value shouldn’t be set on that column and also the value set in that column through the script should not be removed.
- The sheet with the name “NetSuite” will be considered for the integration so the sheet name shouldn’t be updated.
Testing steps
- Steps for running the script:
- Open the spreadsheet.
- Select the sheet for integration( the sheet named “NetSuite”)
- Go to Extensions> Apps Script. The script page will be opened.
- Select “JJ Google Sheet Netsuite integration UNZ-9.js”.
- Click “Run” to run the script.
- Allow one minute to complete the processing of the script.
- The script will run and create corresponding records in NetSuite.
- The status will be updated (within one minute)in the 26th column (z) having column name “NetSuite”.
- The created records (prospect and contact )can be viewed in the NetSuite by using a global search option and the opportunity and phone call records can be found under the sales tab and communication tab in prospect record respectively.
Google sheet – App script
/*******************************************************************************
* Utemaster NZ Ltd | UNZ-9, UNZ-69
*
* **************************************************************************
* Date : 21-12-2020
*
* Author: Jobin & Jismi IT Services LLP
* Script Description : Google sheet integration to create prospect, contact, opportunity, and phone call in netsuite.
* Date created :21-12-2020
*
* REVISION HISTORY
*
* Revision 1.0 ${21-12-2020} Gloria : created
* Revision 1.0 $10-03-2022} Gloria : Update - Add the 'Subsidiary' and 'Projected Total' fields in the google sheet.
*
******************************************************************************/
const fieldId = [{ 'fieldid': 'id', 'colname': 'Id', 'isMandatory': false }, { 'fieldid': 'companyname', 'colname': 'Company Name', 'isMandatory': true }, { 'fieldid': 'email', 'colname': 'Email', 'isMandatory': true }, { 'fieldid': 'phone', 'colname': 'Phone', 'isMandatory': false }, { 'fieldid': 'custentity7', 'colname': 'Region - NZ', 'isMandatory': false },
{ 'fieldid': 'leadsource', 'colname': 'Lead Souce', 'isMandatory': true }, { 'fieldid': 'custentity19', 'colname': 'Customer Industry Type', 'isMandatory': true }, { 'fieldid': 'custentity15', 'colname': 'Vehicle Brand Sold Primary', 'isMandatory': true }, { 'fieldid': 'custentity9', 'colname': 'Customer Type', 'isMandatory': true }, { 'fieldid': 'subsidiary', 'colname': 'Subsidiary', 'isMandatory': true },
{ 'fieldid': 'entityid', 'colname': 'Contact', 'isMandatory': true }, { 'fieldid': 'firstname', 'colname': 'First Name', 'isMandatory': true }, { 'fieldid': 'lastname', 'colname': 'Last Name', 'isMandatory': false }, { 'fieldid': 'contactemail', 'colname': 'Email', 'isMandatory': true },
{ 'fieldid': 'contactphone', 'colname': 'Phone Number', 'isMandatory': false }, { 'fieldid': 'title', 'colname': 'Title', 'isMandatory': false }, { 'fieldid': 'expectedclosedate', 'colname': 'Expected Close', 'isMandatory': true }, { 'fieldid': 'memo', 'colname': 'Details', 'isMandatory': false }, { 'fieldid': 'custbody193', 'colname': 'Next Action', 'isMandatory': false },
{ 'fieldid': 'custbody203', 'colname': 'Opportunity Category', 'isMandatory': true }, { 'fieldid': 'projectedtotal', 'colname': 'Projected Total', 'isMandatory': true },
{ 'fieldid': 'phonetitle', 'colname': 'Subject', 'isMandatory': true }, { 'fieldid': 'assigned', 'colname': 'Organizer', 'isMandatory': true }, { 'fieldid': 'startdate', 'colname': 'Date', 'isMandatory': true }, { 'fieldid': 'message', 'colname': 'Message', 'isMandatory': false }]
function Fetch_data_from_sheet() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
//select the sheet and activate that sheet
var sheet = ss.getSheetByName("Netsuite");
sheet.activate();
// This represents ALL the data
var values = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
Logger.log(values);
// iterate through each row
for (var i = 1; i < values.length; i++) {
var notAdded; var obj = {};
var index = values[0].indexOf("NetSuite")
if (index != -1 && values[i][index]) {
notAdded = false;
} else
notAdded = true;
if (notAdded) {
for (var j = 1; j < 25; j++) {
if (values[i][j] && values[0][j] && j != index) {
if (fieldId[j].fieldid == 'expectedclosedate' || fieldId[j].fieldid == 'startdate') {
var formattedDate = Utilities.formatDate(values[i][j], 'Pacific/Auckland', 'MMMM dd, yyyy HH:mm:ss Z')
obj[fieldId[j].fieldid] = formattedDate;
} else
obj[fieldId[j].fieldid] = values[i][j];
}
}
Logger.log('obj', obj);
//sync each row to netsuite
var response = sendNetSuitePayload(obj)
if (index == -1) {
sheet.getRange(1, 26).setValue("NetSuite")
}
var colour = color(response)
sheet.getRange(i + 1, 26).setValue(response)
sheet.getRange(i + 1, 26).setBackground(colour)
}
}
}
//set color for each type of response.
function color(response) {
if (response.indexOf("FAILURE") != -1)
return "#f78d86"
else if (response.indexOf("FAIL") != -1)
return "#ffbf80"
else if (response.indexOf("SUCCESS") != -1)
return "#b7e1cd"
else
return "#ffffff"
}
//send the JSON data to netsuite
function sendNetSuitePayload(payloadObject) {
Logger.log('payloadObject', JSON.stringify(payloadObject));
for (var key in payloadObject) {
var field = fieldId.filter(function (f) {
return f.fieldid == key
});
if (field[0].isMandatory && !payloadObject[key])
return "FAILURE, CREATE RECORDS MANUALLY, Reason: Empty Value for mandatory field (" + field[0].colname + ")";
}
var credentials = Utilities.base64Encode("NetsuiteUserJJ:Thanks7$!!", Utilities.Charset.UTF_8);
Logger.log('credentials', credentials);
var options = {
method: "post",
contentType: "application/json",
headers: { Authorization: "Basic " + credentials },
payload: JSON.stringify(payloadObject)
};
// var response = UrlFetchApp.fetch("https://3778816-sb1.extforms.netsuite.com/app/site/hosting/scriptlet.nl?script=781&deploy=1&compid=3778816_SB1&h=e3acc82510fc67dc14c3", options);
var response = UrlFetchApp.fetch("https://3778816.extforms.netsuite.com/app/site/hosting/scriptlet.nl?script=781&deploy=1&compid=3778816&h=3c96c2b668487ae6e330", options);
return response.getContentText();
};
Suitelet script
/**
* @NApiVersion 2.x
* @NScriptType Suitelet
* @NModuleScope SameAccount
*/
/*******************************************************************************
* Utemaster NZ Ltd | UNZ-9, UNZ-69
*
* **************************************************************************
* Date : 22-12-2020
*
* Author: Jobin & Jismi IT Services LLP
* Script Description : Google sheet integration to create prospect, contact, opportunity, and phone call.
* Date created :22-12-2020
*
* REVISION HISTORY
*
* Revision 1.0 ${22-12-2020} Gloria : created
* Revision 1.0 $10-03-2022} Gloria : Update - Add the 'Subsidiary' and 'Projected Total' fields in the google sheet.
*
******************************************************************************/
define(['N/search', 'N/record', 'N/https', 'N/format'],
function (search, record, https, format) {
const prospect = { 'customform': 174, 'entitystatus': 8, 'companyname': '', 'email': '', 'phone': '', 'custentity7': '', 'leadsource': '', 'custentity19': '', 'custentity15': '', 'custentity9': '', 'subsidiary': '' }
const contact = { 'customform': 78, 'company': '', 'entityid': '', 'firstname': '', 'lastname': '', 'email': '', 'phone': '', 'custentity7': '', 'custentity19': '', 'custentity15': '', 'custentity9': '', 'subsidiary': '' }
const opportunity = { 'customform': 178, 'entity': '', 'entitystatus': 8, 'probability': 20, 'projectedtotal': '', 'currency': 1, 'title': '', 'expectedclosedate': '', 'memo': '', 'custbody193': '', 'custbody203': '' }
const phoneCall = { 'customform': 80, 'title': '', 'priority': 'MEDIUM', 'status': 'SCHEDULED', 'assigned': '', 'startdate': '', 'message': '', 'company': '', 'contact': '', 'transaction': '' }
// create prospect if the prospect doesn't exist in netsuite
function createProspect(obj) {
try {
var id = checkIfAlreadyExist(obj['email'], 'customer')
if (id)
return [id, '']
else {
var objRecord = record.create({
type: record.Type.PROSPECT,
isDynamic: true,
defaultValues: null
});
for (var key in prospect) {
if (prospect[key]) {
objRecord.setValue({
fieldId: key,
value: prospect[key]
});
} else if ((key == 'custentity7' || key == 'custentity19' || key == 'custentity15' || key == 'custentity9' || key == 'leadsource' || key == 'subsidiary') && obj[key]) {
var objField = objRecord.getField({
fieldId: key
});
log.debug('objField', objField)
var options = objField.getSelectOptions({
filter: obj[key],
operator: 'is'
});
log.debug("optionsprospect", options)
if (options.length && options.length == 1 && options[0].text == obj[key]) {
log.debug("optionsprospect", options)
objRecord.setValue({
fieldId: key,
value: options[0].value
});
} else
return ['', objField.label + ' field value is not valid']
} else {
if (obj[key])
objRecord.setValue({
fieldId: key,
value: obj[key]
});
}
}
var recordId = objRecord.save({
enableSourcing: true,
ignoreMandatoryFields: false
});
log.debug("Prospectid", recordId);
return [recordId, ''];
}
} catch (e) {
log.error("error@createProspect", e)
return ['', e.message];
}
}
//create contact if contact doesn't exists in netsuite
function createContact(obj, prospectId) {
try {
var id = haveMultipleContact(obj['contactemail'], prospectId)
log.debug("Contactid", id);
if (id)
return [id, '']
else {
var objRecord = record.create({
type: record.Type.CONTACT,
isDynamic: true,
defaultValues: null
});
for (var key in contact) {
if (contact[key]) {
objRecord.setValue({
fieldId: key,
value: contact[key]
});
} else if ((key == 'custentity7' || key == 'custentity19' || key == 'custentity15' || key == 'custentity9' || key == 'subsidiary') && obj[key]) {
log.debug("contactselect")
var objField = objRecord.getField({
fieldId: key
});
log.debug('objField', objField)
var options = objField.getSelectOptions({
filter: obj[key],
operator: 'is'
});
log.debug("optionscontact", options)
if (options.length && options.length == 1 && options[0].text == obj[key]) {
log.debug("optionscontact", options)
objRecord.setValue({
fieldId: key,
value: options[0].value
});
} else
return ['', objField.label + ' field value is not valid']
} else if (key == 'company' && prospectId) {
objRecord.setValue({
fieldId: key,
value: prospectId
});
} else if (key == 'email' && obj['contactemail']) {
objRecord.setValue({
fieldId: key,
value: obj['contactemail']
});
} else if (key == 'phone' && obj['contactphone']) {
objRecord.setValue({
fieldId: key,
value: obj['contactphone']
});
} else {
if (obj[key])
objRecord.setValue({
fieldId: key,
value: obj[key]
});
}
}
var recordId = objRecord.save({
enableSourcing: true,
ignoreMandatoryFields: false
});
log.debug("Contactid", recordId);
return [recordId, ''];
}
} catch (e) {
log.error("error@createContact", e)
return ['', e.message];
}
}
//create opportunity for the prospect
function createOpportunity(obj, prospectId) {
try {
var objRecord = record.create({
type: record.Type.OPPORTUNITY,
isDynamic: true,
defaultValues: null
});
for (var key in opportunity) {
if (opportunity[key]) {
objRecord.setValue({
fieldId: key,
value: parseInt(opportunity[key])
});
} else if (key == 'custbody203' && obj[key]) {
log.debug("opposlect")
var objField = objRecord.getField({
fieldId: key
});
var options = objField.getSelectOptions();
var options = objField.getSelectOptions({
filter: obj[key],
operator: 'is'
});
log.debug("oppooption", options)
if (options.length && options.length == 1 && options[0].text == obj[key]) {
log.debug("oppooption", options)
objRecord.setValue({
fieldId: key,
value: options[0].value
});
} else
return ['', objField.label + ' field value is not valid']
} else if (key == 'entity' && prospectId) {
objRecord.setValue({
fieldId: key,
value: prospectId
});
} else if (key == 'expectedclosedate' && obj[key]) {
var formattedDate = format.format({ value: new Date(obj[key]), type: format.Type.DATETIMETZ, timezone: format.Timezone.PACIFIC_AUCKLAND })
var parseDate = format.parse({ value: new Date(obj[key]), type: format.Type.DATETIMETZ, timezone: format.Timezone.PACIFIC_AUCKLAND })
log.debug('oppo formattedDate parseDate formattedDate.split(" ")[0]', formattedDate + " " + parseDate + " " + formattedDate.split(" ")[0])
objRecord.setText({
fieldId: key,
text: String(formattedDate.split(" ")[0])
});
// objRecord.setValue({
// fieldId: key,
// value: String(formattedDate.split(" ")[0])
// });
} else {
log.debug(key, obj[key])
if (obj[key])
objRecord.setValue({
fieldId: key,
value: obj[key]
});
}
}
var recordId = objRecord.save({
enableSourcing: true,
ignoreMandatoryFields: false
});
log.debug("Opportunity", recordId);
return [recordId, ''];
} catch (e) {
log.error("error@createOpportunity", e)
return ['', e.message];
}
}
//create phone call for the prospect, contact and the opportunity
function createPhoneCall(obj, prospectId, contactId, opportunityId) {
try {
var objRecord = record.create({
type: record.Type.PHONE_CALL,
isDynamic: true,
defaultValues: null
});
for (var key in phoneCall) {
if (phoneCall[key]) {
objRecord.setValue({
fieldId: key,
value: phoneCall[key]
});
} else if (key == 'assigned' && obj[key]) {
log.debug("phone slect")
var objField = objRecord.getField({
fieldId: key
});
var options = objField.getSelectOptions({
filter: obj[key],
operator: 'is'
});
if (options.length && options.length == 1 && options[0].text == obj[key]) {
log.debug("phoneoption", options)
objRecord.setValue({
fieldId: key,
value: options[0].value
});
} else
return ['', objField.label + ' field value is not valid']
} else if (key == 'title' && obj['phonetitle']) {
objRecord.setValue({
fieldId: key,
value: obj['phonetitle']
});
} else if (key == 'startdate' && obj[key]) {
var formattedDate = format.format({ value: new Date(obj[key]), type: format.Type.DATETIMETZ, timezone: format.Timezone.PACIFIC_AUCKLAND })
var parseDate = format.parse({ value: new Date(obj[key]), type: format.Type.DATETIMETZ, timezone: format.Timezone.PACIFIC_AUCKLAND })
log.debug('phone formattedDate parseDate formattedDate.split(" ")[0]', formattedDate + " " + parseDate + " " + formattedDate.split(" ")[0])
objRecord.setText({
fieldId: key,
text: String(formattedDate.split(" ")[0])
});
} else if (key == 'company' && prospectId) {
objRecord.setValue({
fieldId: key,
value: prospectId
});
} else if (key == 'contact' && contactId) {
log.debug('contact', contactId)
objRecord.setValue({
fieldId: key,
value: contactId
});
} else if (key == 'transaction' && opportunityId) {
objRecord.setValue({
fieldId: key,
value: opportunityId
});
} else {
if (obj[key])
objRecord.setValue({
fieldId: key,
value: obj[key]
});
}
}
var recordId = objRecord.save({
enableSourcing: true,
ignoreMandatoryFields: false
});
log.debug("PhoneCallid", recordId);
//set contact field
var id = record.submitFields({
type: record.Type.PHONE_CALL,
id: recordId,
values: {
contact: contactId
},
options: {
enableSourcing: false,
ignoreMandatoryFields: true
}
});
log.debug("id", id);
return [recordId, ''];
} catch (e) {
log.error("error@createPhoneCall", e)
return ['', e.message];
}
}
//format the reponse for each cases.
function response(prospectId, contacId, opportunityId, phoneCallId) {
try {
if (prospectId[0] && contacId[0] && opportunityId[0] && phoneCallId[0]) {
return "SUCCESS,RECORDS ARE CREATED"
} else if (prospectId[0] && contacId[0] && opportunityId[0]) {
return phoneCallId && phoneCallId[1] ? "FAIL, CREATE PHONE CALL RECORD MANUALLY, Reason: " + phoneCallId[1] : "FAIL, CREATE PHONE CALL RECORD MANUALLY"
} else if (prospectId[0] && contacId[0]) {
return opportunityId && opportunityId[1] ? "FAIL, CREATE PHONE CALL AND OPPORTUNITY MANUALLY, Reason: " + opportunityId[1] : "FAIL, CREATE PHONE CALL AND OPPORTUNITY MANUALLY"
} else if (prospectId[0] && opportunityId[0]) {
return contacId && contacId[1] ? "FAIL, CREATE PHONE CALL AND CONTACT MANUALLY, Reason: " + contacId[1] : "FAIL, CREATE PHONE CALL AND CONTACT MANUALLY"
} else if (prospectId[0]) {
return contacId && contacId[1] && opportunityId && opportunityId[1] ? "FAIL, CREATE PHONE CALL, CONTACT AND OPPORTUNITY MANUALLY, Reason: " + opportunityId[1] + "and " + contacId[1] : "FAIL, CREATE PHONE CALL, CONTACT AND OPPORTUNITY MANUALLY"
} else
return prospectId && prospectId[1] ? "FAILURE, CREATE RECORDS MANUALLY, Reason: " + prospectId[1] : "FAILURE, CREATE RECORDS MANUALLY"
} catch (e) {
}
}
//check if the contact or prospect already exists in netsuite.
function checkIfAlreadyExist(mailid, type) {
var internalid;
var customerSearchObj = search.create({
type: type,
filters:
[
["email", "is", mailid],
"AND",
["isinactive", "is", "F"]
],
columns:
[
search.createColumn({ name: "internalid", label: "Internal ID" })
]
});
var searchResultCount = customerSearchObj.runPaged().count;
log.debug("customerSearchObj result count", searchResultCount);
customerSearchObj.run().each(function (result) {
// .run().each has a limit of 4,000 results
internalid = result.getValue({ name: "internalid", label: "Internal ID" });
});
return internalid;
}
function haveMultipleContact(mailid, prospectId) {
var contactArr = []; var id;
var contactSearchObj = search.create({
type: "contact",
filters:
[
["email", "is", mailid],
"AND",
["company.type", "anyof", "CustJob"],
"AND",
["company.internalid", "anyof", prospectId]
],
columns:
[
search.createColumn({
name: "internalid",
join: "company",
summary: "COUNT",
label: "Internal ID"
}),
search.createColumn({
name: "internalid",
summary: "GROUP",
sort: search.Sort.ASC,
label: "Internal ID"
})
]
});
var searchResultCount = contactSearchObj.runPaged().count;
log.debug("contactSearchObj result count", searchResultCount);
contactSearchObj.run().each(function (result) {
// .run().each has a limit of 4,000 results
var count = result.getValue({
name: "internalid",
join: "company",
summary: "COUNT",
label: "Internal ID"
});
id = result.getValue({
name: "internalid",
summary: "GROUP",
sort: search.Sort.ASC,
label: "Internal ID"
})
// contactArr.push({ count: count, id: id })
// return true;
});
return id;
}
//entry point
function onRequest(context) {
try {
if (context.request.method === 'POST') {
log.debug("method", context.request.method);
log.debug('request.headers', context.request.headers)
log.debug('request.headers', context.request.headers.Authorization)
//authorising the request
if (String(context.request.headers.Authorization) == 'Basic TmV0c3VpdGVVc2VySko6VGhhbmtzNyQhIQ==') {
var requestBody = JSON.parse(context.request.body);
log.debug("requestBody", requestBody);
var contacId, opportunityId, phoneCallId;
//create prospect if the prospect doesn't exist for this email addresses
var prospectId = createProspect(requestBody);
if (prospectId && prospectId[0]) {
//create contact if the prospect doesn't exist for this email addresses
contacId = createContact(requestBody, prospectId[0])
opportunityId = createOpportunity(requestBody, prospectId[0])
if (prospectId && prospectId[0] && contacId && contacId[0] && opportunityId && opportunityId[0]) {
phoneCallId = createPhoneCall(requestBody, prospectId[0], contacId[0], opportunityId[0])
}
}
var res = response(prospectId, contacId, opportunityId, phoneCallId)
context.response.write(res);
}
}
} catch (e) {
log.error("error@onRequest", e)
}
}
return {
onRequest: onRequest
}
});