How do I turn importing a database into a Google Sheet via AppScript into a custom formula?

Question

So I'm importing a huge API into my spreadsheet, and running into issues with the AppScript timing out after 6 minutes, however, I have read custom formulas do not run into this issue. With my database importing using a lot of for loops and such, I'm not to sure how I transition it into a custom formula.

Here is the code I am working with:

function database() {
  
  var url = "https://sports.core.api.espn.com/v2/sports/football/leagues/college-football/athletes?limit=1000&active=true&page=1"
  var response = UrlFetchApp.fetch(url)
  var data = response.getContentText()
  var result = JSON.parse(data)

  var pageCount = result.pageCount
  var sheet = SpreadsheetApp.getActiveSheet()
  var startingCell = SpreadsheetApp.getCurrentCell(cell)

  var headerRow = ['ID','First Name','Last Name','Full Name','Display Name','Short Name','Weight','Height','Position','Team','City','State','Country','Years','Class','Jersey','Active']

  sheet.appendRow(headerRow)

  for(var pageNumber=1;pageNumber<=pageCount;pageNumber++){

    var newUrl = ("https://sports.core.api.espn.com/v2/sports/football/leagues/college-football/athletes?limit=200&active=true&page=" + pageNumber);
    var trueResponse = UrlFetchApp.fetch(newUrl)
    var trueData = trueResponse.getContentText()
    var trueResult = JSON.parse(trueData)

    Logger.log(pageNumber)

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

      var playerUrl = trueResult.items[i].$ref
      var playerResponse = UrlFetchApp.fetch(playerUrl)
      var playerData = playerResponse.getContentText()
      var playerResult = JSON.parse(playerData)

      var id = playerResult.id
      var firstName = playerResult.firstName
      var lastName = playerResult.lastName
      var fullName = playerResult.fullName
      var displayName = playerResult.displayName
      var shortName = playerResult.shortName
      var weight = playerResult.weight
      var height = playerResult.height
      var position = playerResult.position.abbreviation
      
      var teamUrl = playerResult.team.$ref
      var teamResponse = UrlFetchApp.fetch(teamUrl)
      var teamData = teamResponse.getContentText()
      var teamResult = JSON.parse(teamData)
      var team = teamResult.abbreviation

      var city = playerResult.birthPlace.city
      var state = playerResult.birthPlace.state
      var country = playerResult.birthPlace.country
      var years = playerResult.experience.years
      var displayClass = playerResult.experience.displayValue
      var jersey = playerResult.jersey
      var active = playerResult.active 

      var row = [id,firstName,lastName,fullName,displayName,shortName,weight,height,position,team,city,state,country,years,displayClass,jersey,active]

      sheet.appendRow(row)
    
    }
  }

}  

I'm thinking of having the custom formula take an input of a cell location to import the database. I've made a couple attempts, and I think the answer has to do with arrays, but honestly I don't completely know. If I can get any help figuring this out and understanding how to transition this into a custom formula, that would be so great!

Answer 1

Your script suffers from two problems: 1 - you are trying to get the original JSON plus all of the subsidiary data in a single pass of the script. That is too much processing. 2 - as commented by @TheWizEd, are appending on every new row. It would be better to accumulate those values and save them once only.

The following script generates the JSON for a single page. This can be extended to create a loop to retrieve multiple pages in a single script, or multiple scripts as need me. Once the data has been saved, you can run a secondary script against the URLs to obtain player information.

---

function getBaseJson() {
  
  // get the json and parse it.
  var url = "https://sports.core.api.espn.com/v2/sports/football/leagues/college-football/athletes?limit=1000&active=true&page=1"
  var response = UrlFetchApp.fetch(url)
  var data = response.getContentText()
  var result = JSON.parse(data)

  // establish the sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getSheetByName("Sheet2")

  // ignore the first four fields
  // concentrate on 'items'
  // create an Item header
  var tempHeaderArray = new Array
  var headerRow = ['Item']
  tempHeaderArray.push(headerRow)
  var countRow = 1
  var cellRange = sheet.getRange(countRow,1,tempHeaderArray.length,1)
  cellRange.setValues(tempHeaderArray)
  Logger.log("DEBUG: Saved header")

  // save the item URLs
  var myItems = result.items
  var tempItemDataArray = new Array
  for (var i=0;i<myItems.length;i++){
    for (var [key, value] of Object.entries(myItems[i])) {
      tempItemDataArray.push([value])
    }
  }
  countRow++
  var cellRange = sheet.getRange(countRow,1,tempItemDataArray.length,1)
  cellRange.setValues(tempItemDataArray)
  Logger.log("DEBUG: saved items")
}