Apps Script API Import via Links in Google Sheet Cells but Map Properties Undefined
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.
From your following reply,
I guess I missed that one; what I'm looking to import is the same as what is in the map piece of the formula (id,firstName,lastName,fullName,displayName,shortName,weight,height,position,teamUrl,city,state,country,years,displayClass,jersey,active)
I understood that you have wanted to retrieve the values of id,firstName,lastName,fullName,displayName,shortName,weight,height,position,teamUrl,city,state,country,years,displayClass,jersey,active from each URL.
In your current script, unfortunately, this cannot be achieved. In order to achieve your goal, how about the following sample script?
Sample script:
function dataImport() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Import1");
var countRow = 1;
var exportSheet = ss.getSheetByName("Data1");
var reqs = sheet.getRange("A2:A" + sheet.getLastRow()).getDisplayValues().reduce((ar, [url]) => {
if (url) {
ar.push({ url, muteHttpExceptions: true });
}
return ar;
}, []);
// Or, please use the following script for retrieving the URLs from column "A".
// var reqs = sheet.getRange("A2:A" + sheet.getLastRow()).getRichTextValues().reduce((ar, [a]) => {
// if (a && a.getLinkUrl()) {
// ar.push({ url: a.getLinkUrl(), muteHttpExceptions: true });
// }
// return ar;
// }, []);
var responses = UrlFetchApp.fetchAll(reqs);
var temp = responses.reduce((ar, r) => {
if (r.getResponseCode() == 200) {
var { id, firstName, lastName, fullName, displayName, shortName, weight, height, position: { abbreviation }, team: { $ref }, birthPlace: { city }, birthPlace: { state, country }, experience: { years, displayValue }, jersey, active } = JSON.parse(r.getContentText());
ar.push([id, firstName, lastName, fullName, displayName, shortName, weight, height, abbreviation, $ref, city, state, country, years, displayValue, jersey, active]);
}
return ar;
}, []);
var res = [['IDs', 'First Name', 'Last Name', 'Full Name', 'Display Name', 'Short Name', 'Weight', 'Height', 'Position', 'Team URL', 'City', 'State', 'Country', 'Years', 'Class', 'jersey', 'Active'], ...temp];
exportSheet.getRange(countRow, 1, res.length, res[0].length).setValues(res);
}
Testing:
When this sample script is run using your showing URLs, the following result is obtained.
Note:
- About
over 20,000, I'm worried that this sample script can be directly used for your actual situation. If an error related to the concurrent HTTP requests occurs, it might be required to split the URLs. OrUrlFetchApp.fetchmight be suitable instead ofUrlFetchApp.fetchAll.
References:
- Why is the spread operator needed for Math.max()?
- How to access multidimensional data from a json file?
- How to access multidimensional data with loops
- Difference between np.nan and np.NaN
- JS: Filter array of objects by max value per category
- Bash array with spaces in elements
- How to display a multidimensional array
- How to join / combine two arrays to concatenate into one array?
- How do I find a partial match from a data table to an individual cell?
- Parsing a string representation of nested arrays into an Array
- Pipe complete array-objects instead of array items one at a time?
- In Rust, is there any reason to use a `const` array vs array reference?
- How to set a Rust array length dynamically?
- Swift async Loop in Task
- How to print a bash array on the same line
- Looped process only shows the first result per iteration
- How to access a column value in a 2d array?
- Format 2d array data into a string of hyphenated consecutive day ranges based on a shared time range
- Create an array with same element repeated multiple times
- Difference between List and Array types in Kotlin
- Python list with NumPy arrays as elements
- SwiftUI Mapkit: How to reference values from an array for markers?
- How do I Merge two Arrays in VBA?
- Sum of corresponding values from different arrays of the same size with Python
- 2D array in YAML
- Dynamic multidimensional array in twig
- How to append something to an array?
- How does PHP determine the key to use if I declare array elements without offering explicit keys?
- searching an unordered integer array
- PHP JSON (decode)