javascriptgoogle-sheetssurveymonkeygoogle-apps-script-addon

Time trigger on Google Scheet and Survey Monkey API addon


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 :(


Solution

  • 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?

    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 enter image description here

    1. 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: