Actiondesk has joined Datadog and the product was shut down. Read the blog
HubSpot is a leading CRM platform that provides software and support to help businesses grow better. Their platform includes marketing, sales, service, and website management products that start free and scale to meet customers’ needs at any level of growth. HubSpot has helped thousands of companies in over 100 countries grow their business by bringing together the right people with the right tools they need so they can focus on what matters most: growing their business.
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.
Try the easiest way to build report
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.
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.
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.
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]);
})
}
If it didn’t work, try using console.log() statements to debug your code.
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.
Set it to run updateData, on Head, Time-driven, Minutes timer, Every minute. Click Save.
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.
