So I have been working on putting together a database based on a bunch of APIs I imported (over 20,000), so no custom IMPORTAPI functions have had enough abilities to extract the information from the data, so I have been working on a script to collect that data in an array, then import it all at once so I'm not constantly appending rows, which I have gotten to work, but is too slow to get through the whole list. Here is the current code I have put together in my attempts to make this work:
function dataImport() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Import1");
var countRow = 1;
var exportSheet = ss.getSheetByName("Data1");
var url = sheet.getRange("A2:A").getRichTextValue().getLinkUrl();
var playerCount = sheet.getRange("A2:A").getValues().filter(String).length
var response = UrlFetchApp.fetch(url);
var data = response.getContentText();
var playerResult = JSON.parse(data);
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;
if (weight.error){
return null
}
var height = playerResult.height;
if (height.error){
return null
}
var position = playerResult.position.abbreviation;
if (position.error){
return null
}
var teamUrl = playerResult.team.$ref;
if (teamUrl.error){
return null
}
var city = playerResult.birthPlace.city;
if (city.error){
return null
}
var state = playerResult.birthPlace.state;
if (state.error){
return null
}
var country = playerResult.birthPlace.country;
if (country.error){
return null
}
var years = playerResult.experience.years;
if (years.error){
return null
}
var displayClass = playerResult.experience.displayValue;
if (displayClass.error){
return null
}
var jersey = playerResult.jersey;
if (jersey.error){
return null
}
var active = playerResult.active
var { items, playerCount } = playerResult;
items = items.map(e => [e[id,firstName,lastName,fullName,displayName,shortName,weight,height,position,teamUrl,city,state,country,years,displayClass,jersey,active]])
var reqs = []
for (var p = 1; p <= playerCount; p++) {
reqs.push(url + p)
}
var responses = UrlFetchApp.fetchAll(reqs);
var temp = responses.flatMap(r => r.getResponseCode() == 200 ? JSON.parse(r.getContentText()).items.map(e => [e[id,firstName,lastName,fullName,displayName,shortName,weight,height,position,teamUrl,city,state,country,years,displayClass,jersey,active]]) : []);
var res = [['IDs','First Name','Last Name','Full Name','Display Name','Short Name','Weight','Height','Position','Team URL','City','State','Country','Years','Class','Active'], ...items, ...temp];
exportSheet.getRange(countRow, 1, res.length).setValues(res);
}
Now there may be other issues with this function I have not been able to find yet, but I'm running into an error saying "TypeError: Cannot read properties of undefined (reading 'map')", and I'm not exactly sure how to fix it. There may be other issues with this code as I have not been able to get an import to work due to this error, such as my makeshift IFERROR work with variables like "weight" that do not show up in every API link. I'm not completely sure if I have the looping mechanism right to go through all of the links in the cells or not. If I can get any help with this, I would appreciate it; I also have provided a list of test links from my sheet so testing can be done with getting the data. The sheet it pulls from is very simple, with a header row in A1, then links in every column below it.
