javascriptgoogle-apps-scriptspotifyexecution

How to run consecutive executions after execution times out?

I'm running a script on my personal Google account using Spotify API. I'm limited to six minutes for each execution run time. I need the execution to run longer than that for me to retrieve all the episodes for my list of podcasts.

Is there a way for me to automatically run the execution again once I reach the six-minute time-out and pick up where the previous execution had left off? I don't want to manually edit the GSheets range in the script where I read in the list of podcasts for every execution. I would also like the clear() code in my script to only run in the first round of execution - I don't want to clear all of the episodes from the first run when the consecutive executions follow.

function getEpisodes() {

  var clientId = <your Spotify API client ID>;
  var clientSecret = <your Spotify API client secret>; 

  var ss = SpreadsheetApp.openById(<sheet ID>);
  var sListOfPodcasts = ss.getSheetByName("List of podcasts");
  var sOutput = ss.getSheetByName("Output");
  var arrPodcasts = sListOfPodcasts.getRange("A2:A").getValues();
  sOutput.getRange("A2:L").clear();

  var url = "https://accounts.spotify.com/api/token";
  var params = {
    method: "post",
    headers: {"Authorization" : "Basic " + Utilities.base64Encode(clientId + ":" + clientSecret)},
    payload: {grant_type: "client_credentials"},
  };

  var res = UrlFetchApp.fetch(url, params);
  var obj = JSON.parse(res.getContentText());
  var token = obj.access_token;

  Logger.log("token = " + token);

  var parameters = {       
        method: "GET",
        headers: {
          "Authorization" : "Bearer " + token
          },
        json : true,
  };

  for (const show of arrPodcasts) {

    let offset = 0;
    let j = 1; // this is later used to index the episodes per podcast in the logs

    var getPodcast = "https://api.spotify.com/v1/shows/" + show + "/episodes";
    var fetchPodcast = UrlFetchApp.fetch(getPodcast, parameters);
    var totEps = JSON.parse(fetchPodcast.getContentText()).total
    Logger.log("total episodes = " + totEps);

    let n = Math.floor(totEps/50) + 1; // determine number of loops needed to retrieve all episodes
    Logger.log("We need to loop " + n + " times");   

    for (c = 0; c < n; c++) {

      var podcasts = "https://api.spotify.com/v1/shows/" + show + "/episodes?offset=" + offset + "&limit=50&market=GB";
      Logger.log(podcasts);

      Logger.log("Offset = " + offset);
      
      var nameShow = JSON.parse(UrlFetchApp.fetch("https://api.spotify.com/v1/shows/" + show + "/?market=gb", parameters).getContentText()).name;
      var publisher = JSON.parse(UrlFetchApp.fetch("https://api.spotify.com/v1/shows/" + show + "/?market=gb", parameters).getContentText()).publisher;
      Logger.log(nameShow);

      try {
        var podcast = UrlFetchApp.fetch(podcasts, parameters);
      }
      catch(err) {
        Logger.log("Move onto the next podcast");
      }
      
      var object = JSON.parse(podcast.getContentText()); 
      
      offset = (c+1) * 50;
      Logger.log("Offset = " + offset);                     

      if (c == n) {
        break; // break the loop when we retrive the last batch of episodes, then move onto the next podcast
      }

      for (let b = 0; b < 1; b++) {  

        for (const episode of object.items) {

          Logger.log(j + ') ' + episode.name + '\n'+ episode.release_date);
          j = j + 1; // index the episodes for each podcast

          var rowStart = sOutput.getLastRow() + 1;          
          sOutput.getRange(rowStart, 1, 1, 10).setValues([[nameShow, publisher, episode.name, episode.release_date, episode.description, episode.type, episode.explicit, episode.duration_ms, "https://open.spotify.com/embed/episode/" + episode.id, episode.images[0].url]]);

        }                   

      }

    }

  }

}

Below is what my list of podcast episodes looks like on GSheets.

enter image description here

Many thanks in advance!

Solution

  • I received great help from u/JetCarson on Reddit and got a solution.

    Set up a time-based trigger for spotify(). Don't worry the ScriptApp.deleteTrigger(triggers[i]) part of the script will not delete the spotify() trigger.

    function spotify() {
  clearProperties();
  getEpisodes();
}

// run this function first to clear the properties before reading in values from the Spotify API
function clearProperties() {
  PropertiesService.getScriptProperties().deleteProperty('LastShowIndexRead');
  PropertiesService.getScriptProperties().deleteProperty('lastEpisodeOffset');
};    

function getEpisodes() {  

  var ss = SpreadsheetApp.openById(<sheetID>);
  var sListOfPodcasts = ss.getSheetByName("List of podcasts");
  var sOutput = ss.getSheetByName("Output");
  var arrPodcasts = sListOfPodcasts.getRange("A2:A").getValues();

  //these are needed to ensure we don't go over 360 seconds of execution time
  const MAX_TIME = 5 * 60 * 1000; // run the execution for 5 mins
  var startTime = new Date();

  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() == 'getEpisodes') { // make sure the name of the function in if statement is in string format
      ScriptApp.deleteTrigger(triggers[i]);
    } 
  }
  
  //this next variable will be used to determine where we start off on this call to this function
  var lastShowIndexRead = PropertiesService.getScriptProperties().getProperty('LastShowIndexRead');

  // if lastShowIndexRead is NULL then lastShowIndexRead is 0, else parse lastShowIndexRead as an integer
  lastShowIndexRead = (lastShowIndexRead === null ? 0 : parseInt(lastShowIndexRead));

  // this variable is the offset value used in the UrlFetch when getting each episode for each podcast
  var lastEpisodeOffset = PropertiesService.getScriptProperties().getProperty('lastEpisodeOffset');
  lastEpisodeOffset = (lastEpisodeOffset === null ? 0 : parseInt(lastEpisodeOffset));

  //we'll now delete the property so it won't be used again on another call
  PropertiesService.getScriptProperties().deleteProperty('lastShowIndexRead');
  PropertiesService.getScriptProperties().deleteProperty('lastEpisodeOffset');

  if (lastEpisodeOffset === 0 && lastShowIndexRead === 0) {
    sOutput.getRange("A2:L").clear(); // only clear the spreadsheet on the first execution run of this function
    };

  let j = sOutput.getLastRow();

  var clientId = <your clientID>;
  var clientSecret = <your clientSecret>;

  var url = "https://accounts.spotify.com/api/token";
  var params = {
    method: "post",
    headers: {"Authorization" : "Basic " + Utilities.base64Encode(clientId + ":" + clientSecret)},
    payload: {grant_type: "client_credentials"},
  };

  var res = UrlFetchApp.fetch(url, params);
  var obj = JSON.parse(res.getContentText());
  var token = obj.access_token;

  Logger.log("token = " + token);

  var parameters = {       
        method: "GET",
        headers: {
          "Authorization" : "Bearer " + token
          },
        json : true,
  };

  for (var showIndex = lastShowIndexRead; showIndex <  arrPodcasts.length; showIndex++) {

    const show = arrPodcasts[showIndex][0];
    
    if (show !== '') {

      let offset = lastEpisodeOffset;
      lastEpisodeOffset = 0;

      var getPodcast = "https://api.spotify.com/v1/shows/" + show + "/episodes";
      var fetchPodcast = UrlFetchApp.fetch(getPodcast, parameters);
      var totEps = JSON.parse(fetchPodcast.getContentText()).total;
      Logger.log("total episodes = " + totEps);

      let n = Math.floor(totEps/50) + 1; // determine number of loops needed to retrieve all episodes for the podcast
      Logger.log("We need to loop " + n + " times");
        
      var outputArray = [];

      do {

        var podcasts = `https://api.spotify.com/v1/shows/${show}/episodes?offset=${offset}&limit=50&market=GB`;
        Logger.log(podcasts);

        Logger.log("Offset = " + offset);
        
        var obj = JSON.parse(UrlFetchApp.fetch("https://api.spotify.com/v1/shows/" + show + "/?market=gb", parameters).getContentText());

        var nameShow = obj.name;
        var publisher = obj.publisher;

        Logger.log(nameShow);

        try {
          var podcast = UrlFetchApp.fetch(podcasts, parameters);
        }
        catch(err) {
          Logger.log("Move onto the next podcast");
        }
        
        var object = JSON.parse(podcast.getContentText()); 

        Logger.log("Offset = " + offset);                     

        if ('items' in object) {

          for (var i = 0; i < object.items.length; i++) {

            offset++;
            var episode = object.items[i];

            if (episode !== null) {

              Logger.log(j + ') ' + episode.name + '\n'+ episode.release_date);
              j = j + 1; // index the episodes for each podcast

              //var rowStart = sOutput.getLastRow() + 1;          
              //sOutput.getRange(rowStart, 1, 1, 12).setValues([[nameShow, publisher, episode.name, episode.release_date, episode.description, episode.type, episode.explicit, episode.duration_ms, "https://open.spotify.com/embed/episode/" + episode.id, episode.images[0].url, showIndex, offset]]);
              outputArray.push([nameShow, publisher, episode.name, episode.release_date, episode.description, episode.type, episode.explicit, episode.duration_ms, "https://open.spotify.com/embed/episode/" + episode.id, episode.images[0].url, showIndex, offset]);

            }

          }
        } else {
          console.log(`ERROR: no items found in object`);
        }

        var currentTime = new Date();

        // if execution time exceeds define MAX_TIME and there are still more episodes to retrieve, then build a new trigger and resume in the next trigger rather than letting the current execution time out
        if (offset < totEps && currentTime.getTime() - startTime.getTime() > MAX_TIME) {

          PropertiesService.getScriptProperties().setProperty('LastShowIndexRead', showIndex.toString());
          PropertiesService.getScriptProperties().setProperty('lastEpisodeOffset', offset.toString());
          
          ScriptApp.newTrigger('getEpisodes')
            .timeBased()
            .at(new Date(new Date().getTime() + (20 * 1000))) // 20 seconds from NOW
            .create();

          console.log(`Trigger created to resume ${'getEpisodes'} in 20 seconds...`);
          return; //exit before the time-out

        }
        
      } while (offset < totEps);

      var rowStart = sOutput.getLastRow() + 1;  
      sOutput.getRange(rowStart, 1, outputArray.length, outputArray[0].length).setValues(outputArray);

    }
  }
}

    Hope someone finds this useful!