Time trigger on Google Scheet and Survey Monkey API addon

Question

I'm during my university project and I'm using SurveyMonkey to gather data for my team usage.

To easily read and maintain with the data, I use API Connector Add-On in Google Scheet to download latest responses from Survey Monkey, especially when due to my Uni policy I am the only person who can have access to Survey Monkey in my team.

The problem is that we need to make the data refresh every 5 minutes, as due to the project characteristics the amount of rapidly sent responses have to be monitored by us during 4h window every day and every 5 minutes.

The API Connector allows me to re-run request for data automatically, but only for every 1 hour, which is not sufficient for me and my team. I've tried to use Google Apps Script from that post, but unfortunately I can't modify to make it work due to my lack of knowledge.

 function onInstall() {
  onOpen(); 
}
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Schedule')
  .addItem('Start Schedule', 'menuItem3')
  .addToUi();
}

function menuItem3() {
  createTrigger();
}

function createTrigger()
{
    ScriptApp.newTrigger('startProcess')
    .timeBased()
    .everyDays(1)
    .create(); 
}


function startProcess(){
// Add your processing logic here. e.g. send notifications.
}

Could you please help me and my team? We aren't really a programmer of any kind (economics student) and I'm really struggling with this :(

Answer 1

As I understand, you

1. gather information with surveymonkey
2. import data from surveymonkey to google sheets
3. you want to refresh data from surveymonkey in google sheets every 5 minutes

Well true, you should use triggers for that.

---

1. first of all, take a look at this surveymonkey guide, in case you didn't do that before
2. secondly lets create/modify a script with the next code

Here is an example of code for trigger test purposes

function myFunction(){
  // change id by your actual spreadsheet ID
  var ss = SpreadsheetApp.openById("id").getSheetByName("Sheet1");
  var originalRange = ss.getRange("A1");
  var destinationRange = ss.getRange("A2");
  originalRange.copyTo(destinationRange);
}

What it does?

• copies the cell A1 to A2, if you launch it from Apps Script you will see a work in action.

But how to trigger it every minute or every 5 minutes?

Follow the next step by step guide:

1. In apps script click on Menu > Edit > Current project's triggers
2. New tab with Triggers will be opened, clock on blue button Add Trigger
3. In configuration block select the next parameters:
   • Choose which function to run : myFunction
   • Select event source : Time-drive
   • Select type of time based trigger : Minutes timer
   • Select minute interval : Every minute

so it looks like this

4. click on Save

From now on your cell A1 will be copied to A2 every minute, automatically

---

Knowing this trick you can put your API Connect into myFunction to import data into Google Sheet

I hope this helps you and by the way Google has wonderful service like Google Forms, you can test it as well :)

---

References:

• Google Apps Script - Spreadsheet
• External APIs
• Installable Triggers
• Google Forms