Close

How to connect Close to google sheets

Close is a sales engagement CRM for the modern business. Close helps teams across all industries turn more leads into revenue. The company’s mission is to help you grow your business and make every sale count by providing tools to engage with your customers in whichever way they prefer.

Close
google sheet

Follow this step-by-step explanation to learn how to automatically retrieve data from your Close account and import it into Google Sheets. You’ll be building a script right into the Google Apps Script platform via Google Sheets’ Script Editor - no third-party software required.

If you don’t want to manage the script yourself, check out check out our list of Google Sheets Add-Ons that can do it for you.

Step 1

Create a Google Sheets script to import from Close

1. Create a new Google Sheet by typing sheet.new in your Chrome toolbar

2. Name your spreadsheet

Double click on the default name “Untitled spreadsheet” on the top left-hand corner and replace it with the name you want. For instance, we chose to name it “Test” here.

3. Click on Extensions > Apps Script; this will open a new tab with a url starting with https://script.google.com/.

4. Name your Google script

To name your script, click on the default name “Untitled project” on the top left-hand corner. A little window will pop up where you can write the name you choose. For instance, we chose to name it like our spreadsheet: Test. Then, click on the Rename button.

5. Get familiar with the API docs from Close

Review the API documentation from Close to see what data you might be able to pull into Google Sheets. You can use tools from Close like an API explorer or Postman collection to build a sense for what data you have access to. You’ll be referencing this documentation as you continue to write your code below.

6. Write code to write the header column of the data you picked into your spreadsheet: “updateData”

You can hardcode this. Let’s say you decided you’re going to make a sheet of all the users from the API, with columns Email and Name.

function updateData() {
   const HEADERS = ['Name', 'Email']
   const spreadsheet = SpreadsheetApp.getActive();
   const sheet = spreadsheet.getSheetByName('Sheet1');
   sheet.clear();
   sheet.appendRow(HEADERS);
}

You can run this code by clicking Save and then Run.

You’ll be required to Review Permissions to continue.

Look back at your spreadsheet: it’ll have your new header.

7. Write authentication code to start hitting the API: “readData"

Find the section on authentication in the API documentation. Generally, you’ll want to reference JavaScript or Node.js documentation: Apps Script code is written in JavaScript.

Write some code that uses a personal access token or performs an OAuth authentication process. You’ll probably need to create a personal access token on your Close account first.

Write this code as a new function (below your updateData function).

EXAMPLE

function readData() {
   const PERSONAL_ACCESS_TOKEN = "94e3c3fb-9570-48d2-bd44-028100b97879";
   const API_URL = "https://app.asana.com/api/1.0";
   const DATA_ENDPOINT = "/users?opt_fields=name,email&workspace=38823";
   const response = UrlFetchApp.fetch(API_URL + DATA_ENDPOINT, {
       headers: {
           "Authorization": "Bearer " + PERSONAL_ACCESS_TOKEN
       }
   });
   const content = JSON.parse(response.getContentText());
   console.log(content);
}

You can Save and run the readData code to make sure you’re getting the output you expect.

8. Connect “readData” to “updateData”

You want to create a new row of data for every result you get from “readData”.

EXAMPLE

function readData() {
   const PERSONAL_ACCESS_TOKEN = "94e3c3fb-9570-48d2-bd44-028100b97879";
   const API_URL = "https://app.asana.com/api/1.0";
   const DATA_ENDPOINT = "/users?opt_fields=name,email&workspace=38823";
   const response = UrlFetchApp.fetch(API_URL + DATA_ENDPOINT, {
       headers: {
           "Authorization": "Bearer " + PERSONAL_ACCESS_TOKEN
       }
   });
   const content = JSON.parse(response.getContentText());
   console.log(content);
}

function updateData() {
   const HEADERS = ['Name', 'Email']
   const spreadsheet = SpreadsheetApp.getActive();
   const sheet = spreadsheet.getSheetByName('Sheet1');
   sheet.clear();
   sheet.appendRow(HEADERS);
   const content = readData();
   const users = content.data;
   console.log(users);
   users.forEach(user => {
       const name = user.name;
       const email = user.email;
       sheet.appendRow([name, email]);
   })
}

9. Run your updateData function again

10. Check your sheet to view the updated data

If it didn’t work, try using console.log() statements to debug your code.

Don’t want to figure out writing your own code?

Use Actiondesk to pull live data from Close into a spreadsheet that your team can use to build reports.

Step 2

Run a Google Sheets script automatically every minute to retrieve Close data

Now we’ll show you the code to fetch and refresh your data every minute. Each time, it clears the existing content in the chosen sheet and replaces it with the database data while keeping the format you gave to the table in Google Sheets.

Go into Triggers and Add Trigger

Set it to run updateData, on Head, Time-driven, Minutes timer, Every minute. Click Save.

Now watch it run!

You can delete some data from spreadsheet. Wait one minute - and it will refill again.

That’s it! Now, you will import and automatically sync your data between Close and Google Sheets.

But isn’t there an easier way to do this?

You could use an already-built Google Sheets Add-On

Sorry! No pre-existing add-ons found for this data source.

But if you’re adding third party software to your spreadsheet anyway, there’s a better way

The reason you want to push Close data to Google Sheets is probably that you want to make this data accessible to team members who are comfortable in a spreadsheet

If that's the case, you should try out Actiondesk. Actiondesk is the easiest way for startup teams to make better decisions with their data. You can access your company key data without code or writing SQL, you'll just need basic spreadsheet skills.

Actiondesk looks like a spreadsheet and works like a spreadsheet. Users used to Google Sheets and Excel will be able to learn Actiondesk very quickly.

While writing a script or using a Google Sheets Add-On can be a good one-off solution, they're usually not durable. The overwhelming feedback we got from the market is that these solutions are not reliable and you always have to debug them yourself.

If you're looking for a more solid solution, check out Actiondesk, you can try it for free.

You can even easily get started with one of our many Actiondesk Close templates.