Sales and marketing teams often collect new leads in a spreadsheet before a CRM integration exists, and this tutorial closes that gap by pushing each new row straight into HubSpot as a contact record instead of requiring a manual import later.
The script calls HubSpot's CRM API v3 contacts endpoint with a POST request, authenticating with a private app token that is scoped to the specific CRM objects the integration needs rather than a broad, all-access key.
HubSpot expects contact fields under a nested properties object with lowercase internal names like firstname and lastname rather than the capitalized labels shown in the HubSpot UI, which is a common source of silent mismatches on a first attempt.
After a successful create, the script writes the new HubSpot contact ID back into a Contacts tab alongside the submitted fields, giving the sheet a reference id it can use later for updates or deduplication.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Config | Value | Purpose |
|---|---|---|
| HUBSPOT_TOKEN | Script property | Private app token |
| Endpoint | /crm/v3/objects/contacts | Create contact |
| Sheet | Contacts | email, firstname, lastname, hubspotId |
What it does
createHubspotContact takes an email, first name, and last name, wraps them in the properties object HubSpot's API requires, and posts them to the crm/v3/objects/contacts endpoint.
Prerequisites
A successful create returns HTTP 201 along with the new object's id, which the script reads off the parsed response body and appends to the Contacts sheet next to the fields that were submitted.
Walkthrough
Create a private app under HubSpot's Settings > Integrations > Private Apps, grant it the crm.objects.contacts.write scope, and store the generated token under HUBSPOT_PRIVATE_APP_TOKEN in Script Properties.
Edge cases
The Authorization header uses the Bearer scheme with the private app token, contentType is explicitly set to application/json so HubSpot parses the payload correctly, and muteHttpExceptions keeps a 409 conflict from throwing before the script can inspect it.
Testing
Any response code other than 201 throws with the full parsed error body attached, which is important for HubSpot because a 409 on this endpoint usually means a contact with that email already exists rather than a generic failure.
Hardening
Create a contact with a throwaway email in a HubSpot sandbox or test account first, confirm the returned id appears in the Contacts tab, and check the HubSpot UI directly to verify the properties mapped correctly.
Variations
Because HubSpot treats email as a de facto unique key for contacts, a production version of this script should catch the 409 conflict case and update the existing contact instead of treating every duplicate submission as a hard failure.
Full code: createHubspotContact()
Store the private app token in Script Properties, then call createHubspotContact with an email, first name, and last name for each new lead.
var HUBSPOT_TOKEN_PROPERTY = 'HUBSPOT_PRIVATE_APP_TOKEN';
function createHubspotContact(email, firstName, lastName) {
var token = PropertiesService.getScriptProperties().getProperty(HUBSPOT_TOKEN_PROPERTY);
if (!token) throw new Error('Missing HUBSPOT_PRIVATE_APP_TOKEN script property.');
var payload = {
properties: {
email: email,
firstname: firstName,
lastname: lastName
}
};
var options = {
method: 'post',
contentType: 'application/json',
headers: { Authorization: 'Bearer ' + token },
payload: JSON.stringify(payload),
muteHttpExceptions: true
};
var response = UrlFetchApp.fetch('https://api.hubapi.com/crm/v3/objects/contacts', options);
var code = response.getResponseCode();
var body = JSON.parse(response.getContentText());
if (code !== 201) {
throw new Error('HubSpot create contact failed: ' + JSON.stringify(body));
}
var sheet = SpreadsheetApp.getActive().getSheetByName('Contacts') || SpreadsheetApp.getActive().insertSheet('Contacts');
sheet.appendRow([body.id, email, firstName, lastName, new Date()]);
return body.id;
}- Line 4: Reads the HubSpot private app token from Script Properties.
- Line 7: Nests contact fields under the properties object HubSpot's API expects.
- Line 18: Sends the token with the Bearer authorization scheme.
- Line 25: Reads the response code before trusting the parsed body.
- Line 28: Throws with the full HubSpot error body for a non-201 response.
- Line 32: Writes the new contact ID back into the Contacts sheet.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: create HubSpot contacts
- 1Private app created in HubSpot with the crm.objects.contacts.write scope
- 2Token stored under HUBSPOT_PRIVATE_APP_TOKEN in Script Properties
- 3Property names confirmed lowercase, matching HubSpot's internal field names
- 4Contacts sheet exists to receive the returned contact ID
- 5Test contact created with a throwaway email before production use
- 6409 conflict response reviewed as a possible duplicate rather than an error
- 7Response code checked before reading the returned contact ID
Frequently asked questions
HubSpot's CRM API v3 represents every object, including contacts, as a generic record with a properties map, so custom and standard fields both live under that same key regardless of object type.
At minimum crm.objects.contacts.write to create contacts, and crm.objects.contacts.read if a later version of the script also needs to look up existing contacts before creating new ones.
It almost always means a contact with that email already exists, since HubSpot enforces uniqueness on the email property by default for contact records.
Yes, add any additional internal property name as a key inside the properties object, as long as that property has already been created in the HubSpot account's contact schema.
HubSpot has deprecated its legacy API keys in favor of private app tokens, which support scoped permissions and can be rotated or revoked without affecting other integrations.
Send a PATCH request to crm/v3/objects/contacts/{contactId} with the same properties object, using the contact ID this tutorial writes into the Contacts sheet.