SurveyMonkey is the world’s leading People Powered Data platform. SurveyMonkey helps curious people and companies have conversations at scale with the people who matter most. SurveyMonkey has helped over 20 million users answer more than 100 million questions, making it one of the largest survey platforms in the world. Companies like Amazon, Coca-Cola, Disney, Ford and Microsoft use our software to collect feedback from their customers about their products and services so they can know more about what drives customer satisfaction and make better informed decisions for product development or marketing campaigns.
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.