I always get the exception: "Exception: Service timed out: Spreadsheets" Why does this happen and would it help to try the method updateSpec(spec)? It always happens when I got to this specific line:
let turnamentGames = response("https://europe.api.riotgames.com/lol/match/v5/matches/by-puuid/" puuid "/ids?type=tourney&start=0&count=100&startTime=" (parseInt((currentTime / 1000) - 15552000)) "&" apikey.substring(1));
It also takes a long time (30s) to run the first output of the initial methods first line:
function main() {
console.info("MAIN: STARTED");
currentTime = new Date();
if (currentTime.getHours() > 11) analyse();
else console.warn("MAIN: Next search at 12:00");
}
This should not be that complicated. Is it then just a issue of bad Google servers or what is the problem?
I appreciate any help.
Full code:
function analyse() {
let firstTeamName = spreadsheet.getSheetByName("Übersicht").getRange("AZ1:AZ1").getValue();
console.info("MAIN: (1/9) Teams loaded");
analyseTeam(spreadsheet.getSheetByName(firstTeamName));
analyseTeam(spreadsheet.getSheetByName("TRUEs"));
for (let id = 1; id < 8; id) if (id !== Number(firstTeamName.startsWith("Tag ") ? firstTeamName.substring(4) : 0)) analyseTeam(spreadsheet.getSheetByName("Tag " id));
}
function response(url) {
response(url, 1);
}
function response(url, id) {
makeABreak.push(new Date().getTime());
if (makeABreak.length === 100) {
if ((new Date().getTime() - makeABreak.shift()) < 120000) {
console.warn("I make a break!");
Utilities.sleep(120000);
makeABreak = [];
}
}
try {
return JSON.parse(UrlFetchApp.fetch(url).getContentText());
} catch(exception) {
console.error("Exception for (" url "):" exception);
return id === 2 ? null : response(url, 2);
}
let sheet;
let puuids;
let newPlayers;
let section;
let sectionRow;
let sectionLine;
let lineRecentGames;
let normsInSection;
function analyseTeam(sheetToAnalyse) {
// Initializing
currentTime = new Date();
sheet = sheetToAnalyse;
puuids = [];
newPlayers = [];
normsInSection = [];
section = -1;
sectionRow = -1;
sectionLine = -1;
lineRecentGames = (sheet.getName() === "TRUEs" ? 22 : 17);
// Load player Data & Custom Games
let games = loadPlayers();
console.info(sheet.getName() ": Spieler geladen, Elos aktualisiert");
games.sort();
findPrimeLeague(games);
console.info(sheet.getName() ": PrimeLeague Spiele geladen.");
// Load Normal Games
findNorms();
console.info("MAIN: " sheet.getName() " loaded");
}
function loadPlayers() {
let recentCustomGames = [];
let recentCustomGamesData = sheet.getRange(lineRecentGames 2, 1, 250, 1).getValues();
for (let customGameId in recentCustomGamesData) {
if (recentCustomGamesData.hasOwnProperty(customGameId)) {
recentCustomGames.push(recentCustomGamesData[customGameId][0]);
if (recentCustomGamesData[customGameId][1] == "" || new Date().getTime() - new Date(recentCustomGamesData[customGameId][1]).getTime() > 15552000000)
sheet.getRange(lineRecentGames 2 customGameId, 1, 1, 73).setValues([["", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", ""]]);
}
}
let games = [];
let column = 22;
let names = [];
let playerData = sheet.getRange(2, 1, sheet.getName() === "TRUEs" ? 20 : 15, 3).getValues();
let puuid;
let summonerData;
let elo = "U";
for (let playerId in playerData) {
if (playerData.hasOwnProperty(playerId)) {
puuid = playerData[playerId][1];
// Insert new Players
if (puuid.length < 10) {
if (playerData[playerId][0] !== "") {
let profileContent = response('https://euw1.api.riotgames.com/lol/summoner/v4/summoners/by-name/' playerData[playerId][0] apikey);
puuid = profileContent.puuid;
playerData[playerId][1] = profileContent.puuid;
newPlayers.push(puuid);
}
} else {
if (currentTime.getHours() === 23) {
summonerData = response('https://euw1.api.riotgames.com/lol/summoner/v4/summoners/by-puuid/' puuid apikey);
playerData[playerId][0] = summonerData.name;
if (sheet.getName().startsWith("Tag")) column = Number(sheet.getName().substring(4));
else if (sheet.getName() === "Scrim") column = 8;
else {
let scrimTeams = inputSheet.getRange(2, 12, 1, Number(inputSheet.getRange("C2:C2").getValue())).getValues()[0];
for (let scrimTeam in scrimTeams) if (scrimTeams.hasOwnProperty(scrimTeam) && String(scrimTeams[scrimTeam]) === sheet.getName()) column = scrimTeam 9;
}
//RANKED
let rankedData = response('https://euw1.api.riotgames.com/lol/league/v4/entries/by-summoner/' summonerData.id apikey);
for (let entry in rankedData) {
if (rankedData.hasOwnProperty(entry) && rankedData[entry].queueType === "RANKED_SOLO_5x5")
elo = String(rankedData[entry].tier).substring(0, 1) (rankedData[entry].rank === "I" ? "1" : rankedData[entry].rank === "II" ? "2" : rankedData[entry].rank === "III"?"3":"4");
}
playerData[playerId][2] = elo;
}
}
puuids.push(puuid);
if (puuid !== "" && playerId < 10) {
let turnamentGames = response("https://europe.api.riotgames.com/lol/match/v5/matches/by-puuid/" puuid "/ids?type=tourney&start=0&count=100&startTime=" (parseInt((currentTime / 1000) - 15552000)) "&" apikey.substring(1));
for (let turnamentGame in turnamentGames) if (turnamentGames.hasOwnProperty(turnamentGame) && !games.includes(turnamentGames[turnamentGame]) && !recentCustomGames.includes(turnamentGames[turnamentGame])) games.push(turnamentGames[turnamentGame]);
let clashGames = response("https://europe.api.riotgames.com/lol/match/v5/matches/by-puuid/" puuid "/ids?startTime=" (parseInt((currentTime / 1000) - 15552000))
"&queue=700&start=0&count=100&" apikey.substring(1));
for (let clashGame in clashGames) if (clashGames.hasOwnProperty(clashGame) && !games.includes(clashGames[clashGame]) && !recentCustomGames.includes(clashGames[clashGame]))
games.push(clashGames[clashGame]);
}
names.push(new Array(playerData[playerId][0]));
playerData[playerId].splice(0, 1);
}
}
//determineAmountofTourneyGames(playerData);
inputSheet.getRange(4, column 3, playerData.length, 1).setValues(names);
sheet.getRange(2, 2, playerData.length, 2).setValues(playerData);
return games;
}
function findPrimeLeague(games) {
const championData = response("http://ddragon.leagueoflegends.com/cdn/" response("http://ddragon.leagueoflegends.com/api/versions.json")[0] "/data/en_US/champion.json");
let startAmount = Number(sheet.getRange("B" lineRecentGames ":B" lineRecentGames).getValue());
let amount = Number(startAmount);
let gamesArrays = [];
let ganks = sheet.getRange("P1:R1").getValues()[0]
for (let game in games) {
if (games.hasOwnProperty(game)) {
let gameId = games[game];
let gameData = response("https://europe.api.riotgames.com/lol/match/v5/matches/" gameId apikey);
let durationSeconds = Number(gameData.info.gameDuration);
if (durationSeconds > 36000) durationSeconds = Math.round(durationSeconds / 1000);
//determineTeam
let team;
let win;
let players = gameData.info.participants;
for (let playerId in players) {
if (players.hasOwnProperty(playerId)) {
let participant = players[playerId];
if (puuids.includes(participant.puuid)) {
team = participant.teamId;
win = participant.win;
break;
}
}
}
let teams = gameData.info.teams;
let banns = [];
let banIds = team === 200 ? (teams[1].bans).concat(teams[0].bans) : (teams[0].bans).concat(teams[1].bans);
if (championData !== undefined) {
let champions = championData.data;
for (let champion in champions) if (champions.hasOwnProperty(champion))
for (let ban in banIds) if (banIds.hasOwnProperty(ban) && banIds[ban].championId === Number(champions[champion].key)) banns.push(champions[champion].id);
}
let top = ["", "", "", "", "", "", "", "", "", ""];
let jgl = ["", "", "", "", "", "", "", "", "", ""];
let mid = ["", "", "", "", "", "", "", "", "", ""];
let bot = ["", "", "", "", "", "", "", "", "", ""];
let sup = ["", "", "", "", "", "", "", "", "", ""];
let totalCS = 0;
let supGold;
for (let playerId in players) {
if (players.hasOwnProperty(playerId)) {
let participant = players[playerId];
if (puuids.includes(participant.puuid)) totalCS = (parseInt(participant.neutralMinionsKilled) parseInt(participant.totalMinionsKilled));
if (participant.teamPosition === "TOP") top = determinePlayer(participant, top);
if (participant.teamPosition === "JUNGLE") jgl = determinePlayer(participant, jgl);
if (participant.teamPosition === "MIDDLE") mid = determinePlayer(participant, mid);
if (participant.teamPosition === "BOTTOM") bot = determinePlayer(participant, bot);
if (participant.teamPosition === "UTILITY") sup = determinePlayer(participant, sup);
if (puuids.includes(participant.puuid) && participant.teamPosition === "UTILITY") supGold = participant.goldEarned;
}
}
let gameDetails = response("https://europe.api.riotgames.com/lol/match/v5/matches/" gameId "/timeline" apikey);
let detailedParticipants = gameDetails.metadata.participants;
let topId = -1;
let jglId = -1;
let midId = -1;
let botId = -1;
let supId = -1;
for (let participant in detailedParticipants) {
if (detailedParticipants.hasOwnProperty(participant)) {
if (detailedParticipants[participant] === top[0]) topId = Number(participant) 1;
else if (detailedParticipants[participant] === jgl[0]) jglId = Number(participant) 1;
else if (detailedParticipants[participant] === mid[0]) midId = Number(participant) 1;
else if (detailedParticipants[participant] === bot[0]) botId = Number(participant) 1;
else if (detailedParticipants[participant] === sup[0]) supId = Number(participant) 1;
}
}
let topSoloKills = 0;
let jglSuccessGanks = 0;
let jglTriedGanks = 0;
let midSoloKills = 0;
let botMinions = 0;
let supSuccessGanks = 0;
let recentDamageStats = 0;
let totalDamage = 0;
for (let frameId in gameDetails.info.frames) {
let events = gameDetails.info.frames[frameId].events;
for (let eventObj in events) {
if (events.hasOwnProperty(eventObj)) {
let event = events[eventObj];
let assistingIds = event.assistingParticipantIds;
if (event.type === "CHAMPION_KILL" && assistingIds === undefined && topId > 0 && event.killerId === topId) topSoloKills ;
if (event.type === "CHAMPION_KILL" && assistingIds === undefined && midId > 0 && event.killerId === midId) midSoloKills ;
if (event.type === "CHAMPION_KILL" && event.timestamp < 1000000) {
if (jglId > 0 && event.killerId === jglId) {
if ((event.position.x - event.position.y) < -3000) {
ganks[0];
} else if ((event.position.x - event.position.y) > 3000) {
ganks[2];
} else {
ganks[1];
}
jglSuccessGanks ;
}
for (let ids in assistingIds)
if (assistingIds.hasOwnProperty(ids) && jglId > 0 && assistingIds !== undefined && assistingIds[ids] === jglId) {
if ((event.position.x - event.position.y) < -3000) {
ganks[0];
} else if ((event.position.x - event.position.y) > 3000) {
ganks[2];
} else {
ganks[1];
}
jglSuccessGanks ;
}
for (let ids in assistingIds) if (assistingIds.hasOwnProperty(ids) && supId > 0 && assistingIds !== undefined && assistingIds[ids] === supId &&
(event.killerId === midId || event.killerId === topId)) supSuccessGanks ;
}
if (event.type === "CHAMPION_KILL" && event.victimId === jglId && event.timestamp < 1000000) {
if ((event.position.x - event.position.y) < -3000) {
ganks[0];
} else if ((event.position.x - event.position.y) > 3000) {
ganks[2];
} else {
ganks[1];
}
}
}
}
let frame = gameDetails.info.frames[frameId];
if (jglId > 0 && frameId < 16) {
totalDamage = frame.participantFrames[jglId].damageStats.totalDamageDoneToChampions;
if (recentDamageStats !== totalDamage) jglTriedGanks ;
}
if (frameId == 15 && botId > 0) botMinions = frame.participantFrames[botId].minionsKilled frame.participantFrames[botId].jungleMinionsKilled;
recentDamageStats = totalDamage;
}
top[9] = (topId === -1) ? "" : topSoloKills;
jgl[9] = (jglId === -1) ? "" : jglSuccessGanks / jglTriedGanks;
mid[9] = (midId === -1) ? "" : midSoloKills;
bot[9] = (botId === -1) ? "" : botMinions;
sup[9] = (supId === -1) ? "" : supSuccessGanks;
let date = Utilities.formatDate(new Date(Number(gameData.info.gameCreation)), CalendarApp.getTimeZone(), 'dd.MM.YYYY HH:mm');
let type = gameData.info.gameType === "MATCHED_GAME" ? "C" : "L";
let totalGold = parseInt(top[6] === "" ? 0 : top[6]) parseInt(jgl[6] === "" ? 0 : jgl[6]) parseInt(mid[6] === "" ?
0 : mid[6]) parseInt(bot[6] === "" ? 0 : bot[6]) parseInt(supGold === "" ? 0 : supGold);
let objectives = (team === 100) ? gameData.info.teams[0].objectives : gameData.info.teams[1].objectives;
let tower = (team === 100 ? objectives.tower.kills : objectives.tower.kills);
let drakes = (team === 100 ? objectives.dragon.kills : objectives.dragon.kills);
let inhibs = (team === 100 ? objectives.inhibitor.kills : objectives.inhibitor.kills);
let heralds = (team === 100 ? objectives.riftHerald.kills : objectives.riftHerald.kills);
let barons = (team === 100 ? objectives.baron.kills : objectives.baron.kills);
let gameArray = [gameId, date, durationSeconds, type, win, banns[0], banns[1], banns[2], banns[3], banns[4], banns[5], banns[6], banns[7], banns[8], banns[9], top[0], top[1], top[2],
top[3], top[4], top[5], top[6], top[7], top[8], jgl[0], jgl[1], jgl[2], jgl[3], jgl[4], jgl[5], jgl[6], jgl[7], jgl[8], mid[0], mid[1], mid[2], mid[3], mid[4], mid[5], mid[6], mid[7],
mid[8], bot[0], bot[1], bot[2], bot[3], bot[4], bot[5], bot[6], bot[7], bot[8], sup[0], sup[1], sup[2], sup[3], sup[4], sup[5], sup[6], sup[7], sup[8], totalCS, totalGold,
tower, drakes, inhibs, heralds, barons, top[9], jgl[9], mid[9], bot[9], sup[9]];
if (sheet.getName() === "TRUEs") gameArray.splice(60, 0, supGold); else gameArray.push("");
gamesArrays.push(gameArray);
amount;
}
}
if (gamesArrays.length > 0) {
if (startAmount % 250 < amount % 250) sheet.getRange(lineRecentGames 2 (startAmount % 250), 1, gamesArrays.length, gamesArrays[0].length).setValues(gamesArrays);
else {
let remainingSpace = 250 - (startAmount % 250);
let lastArray = gamesArrays.slice(remainingSpace);
sheet.getRange(lineRecentGames 2 (startAmount % 250), 1, remainingSpace, gamesArrays[0].length).setValues(gamesArrays.slice(0, remainingSpace));
sheet.getRange(lineRecentGames 2, 1, lastArray.length, gamesArrays[0].length).setValues(lastArray);
}
}
sheet.getRange("B" lineRecentGames ":B" lineRecentGames).setValue(amount);
sheet.getRange("P1:R1").setValues([ganks]);
}
// TODO CALC!A1
function findNorms() {
let allgames = [];
let time = Number(sheet.getRange("K1:K1").getValue());
if (time === 0) sheet.getRange("L1:L1").setValue("init");
let recentNumber = Number(sheet.getRange("B1:B1").getValue());
let amount = Number(sheet.getRange("CM1:CM1").getValue());
let init = String(sheet.getRange("L1:L1").getValue()) === "init";
if (init) {
if (amount > 0) {
let initAddNorms = sheet.getRange(3, 91, amount, 1).getValues();
for (let normId in initAddNorms) {
let norm = initAddNorms[normId][0];
if (initAddNorms.hasOwnProperty(normId) && !allgames.includes(norm)) {
allgames.push(norm);
}
}
} else allgames = addPlayersGames(puuids, allgames);
console.log(allgames.length);
} else {
if (time < parseInt((currentTime / 1000) - 15552000)) time = parseInt((currentTime / 1000) - 15552000)
for (let puuid in puuids) {
if (puuids.hasOwnProperty(puuid) && puuids[puuid] !== "" && puuids[puuid] !== undefined) {
let games40 = response("https://europe.api.riotgames.com/lol/match/v5/matches/by-puuid/" puuids[puuid] "/ids?startTime=" time "&queue=400&start=0&count=100&" apikey.substring(1));
for (let game in games40) if(games40.hasOwnProperty(game) && !allgames.includes(games40[game]) && Number(String(games40[game]).substring(5))>recentNumber) allgames.push(games40[game]);
let games42 = response("https://europe.api.riotgames.com/lol/match/v5/matches/by-puuid/" puuids[puuid] "/ids?startTime=" time "&queue=420&start=0&count=100&" apikey.substring(1));
for (let game in games42) if(games42.hasOwnProperty(game) && !allgames.includes(games42[game]) && Number(String(games42[game]).substring(5))>recentNumber) allgames.push(games42[game]);
let games44 = response("https://europe.api.riotgames.com/lol/match/v5/matches/by-puuid/" puuids[puuid] "/ids?startTime=" time "&queue=440&start=0&count=100&" apikey.substring(1));
for (let game in games44) if(games44.hasOwnProperty(game) && !allgames.includes(games44[game]) && Number(String(games44[game]).substring(5))>recentNumber) allgames.push(games44[game]);
}
}
}
for (let game in allgames) if (Number(allgames[game].substring(5)) < recentNumber) allgames.splice(game, 1);
if (newPlayers.length > 0 && !init) allgames = addPlayersGames(newPlayers, allgames);
allgames.sort();
console.info(sheet.getName() ": " ((allgames.length === 0) ? "No more matches " : allgames.length " new matches"));
if (allgames.length > 0) {
let initNormsArray = [];
for (let i = 0; i < allgames.length; i) initNormsArray.push("");
if (init && allgames.length > 0) {
for (let normId in allgames) if (allgames.hasOwnProperty(normId)) initNormsArray[normId] = [allgames[normId]];
sheet.getRange(3, 91, initNormsArray.length, 1).setValues(initNormsArray);
}
for (let game in allgames) {
let gameContent = response("https://europe.api.riotgames.com/lol/match/v5/matches/" allgames[game] apikey);
if (response !== undefined && gameContent !== undefined) performGame(gameContent, gameContent.info.participants);
else console.error(allgames[game] " skipped");
}
if (normsInSection.length > 0) sheet.getRange(sectionLine, sectionRow, normsInSection.length, 16).setValues(normsInSection);
sheet.getRange("K1:K1").setValue(parseInt(Number(currentTime.getTime()) / 1000));
if (init) {
for (let normId in allgames) if (allgames.hasOwnProperty(normId)) initNormsArray[normId] = [allgames[normId]];
sheet.getRange(3, 91, initNormsArray.length, 1).setValues(initNormsArray);
console.log("OUT");
}
}
if (init) sheet.getRange("L1:L1").setValue("");
}
function performGame(game, players) {
let timestamp = Number(game.info.gameCreation);
let sectionNew = (parseInt((new Date(timestamp).getTime() - new Date('June 16, 2021 00:00:00 0000').getTime()) / 864000000)) % 20 1;
if (sectionNew !== section) {
if (normsInSection.length > 0) sheet.getRange(sectionLine, sectionRow, normsInSection.length, 16).setValues(normsInSection);
normsInSection = [];
section = sectionNew;
let sectionRowBig = (section === 1) ? (section 3) : (section 4);
sectionRow = (sectionRowBig % 5 === 4) ? 74 : (sectionRowBig % 5 * 17 1);
sectionLine = sheet.getRange("BL" (300 section) ":BL" (300 section)).getValue() (parseInt(sectionRowBig / 5) * 500) 1;
}
let duration = game.info.gameDuration;
let matchId = game.metadata.matchId;
let date = Utilities.formatDate(new Date(timestamp), CalendarApp.getTimeZone(), 'dd.MM.YYYY HH:mm');
for (let playerId in players) {
if (players.hasOwnProperty(playerId)) {
let participant = players[playerId];
if (puuids.includes(participant.puuid)) {
let lane = participant.teamPosition;
let puuid = participant.puuid;
let champion = participant.championName;
let enemy = "";
let win = participant.win;
let cs = (participant.neutralMinionsKilled participant.totalMinionsKilled) * 60 / ((duration > 10000) ? (duration / 1000) : duration);
cs = Math.round(cs * 10) / 10;
let gold = participant.goldEarned;
let egold = 0;
for (let enemyPlayer in players) {
if (players.hasOwnProperty(enemyPlayer)) {
let counterpart = players[enemyPlayer];
if (counterpart.teamPosition === lane && counterpart.puuid !== puuid) {
enemy = counterpart.championName;
egold = counterpart.goldEarned;
break;
}
}
}
let gdiff = (egold > 0) ? gold - egold : 0;
let gpm = parseInt(gold * 60 / ((duration > 10000) ? (duration / 1000) : duration));
let ad = participant.physicalDamageDealtToChampions;
let ap = participant.magicDamageDealtToChampions;
let td = participant.trueDamageDealtToChampions;
let taken = participant.totalDamageTaken;
let healed = participant.totalHeal;
let cc = participant.timeCCingOthers;
normsInSection.push([matchId, date, lane, puuid, champion, enemy, win, cs, gdiff, gpm, ad, ap, td, taken, healed, cc]);
}
}
}
sheet.getRange("B1:B1").setValue(matchId.substring(5));
}
function fetchResponses(puuid, epoch) {
let matchIds = [];
let contentEpoch = fetchresponse(puuid, epoch, "400");
for (let matchId in contentEpoch) if (contentEpoch.hasOwnProperty(matchId) && !matchIds.includes(contentEpoch[matchId])) matchIds.push(contentEpoch[matchId]);
contentEpoch = fetchresponse(puuid, epoch, "420");
for (let matchId in contentEpoch) if (contentEpoch.hasOwnProperty(matchId) && !matchIds.includes(contentEpoch[matchId])) matchIds.push(contentEpoch[matchId]);
contentEpoch = fetchresponse(puuid, epoch, "440");
for (let matchId in contentEpoch) if (contentEpoch.hasOwnProperty(matchId) && !matchIds.includes(contentEpoch[matchId])) matchIds.push(contentEpoch[matchId]);
return matchIds;
}
function fetchresponse(puuid, epoch, queueId) {
return response("https://europe.api.riotgames.com/lol/match/v5/matches/by-puuid/" puuid "/ids?startTime=" (parseInt(currentTime / 1000) - (3000000 * epoch)) "&endTime="
(parseInt(currentTime / 1000) - (3000000 * (epoch - 1))) "&queue=" queueId "&start=0&count=100&" apikey.substring(1));
}
function determinePlayer(participant, array) {
return puuids.includes(participant.puuid) ? [participant.puuid, participant.championName, array[2], participant.kills, participant.deaths, participant.assists, (participant.teamPosition ===
"UTILITY") ? participant.visionScore : participant.goldEarned, (participant.teamPosition === "UTILITY") ? participant.visionWardsBoughtInGame : participant.totalDamageDealtToChampions,
array[8], array[9]] : [array[0], array[1], participant.championName, array[3], array[4], array[5], array[6], array[7], participant.goldEarned, array[9]];
}
function addPlayersGames(puuids, allgames) {
console.log("Load " puuids);
for (let puuid in puuids) {
if (puuids.hasOwnProperty(puuid)) {
for (let epochId = 1; epochId < 11; epochId) {
let contentEpoch = fetchResponses(puuids[puuid], epochId);
for (let matchId in contentEpoch) if (!allgames.includes(contentEpoch[matchId])) allgames.push(contentEpoch[matchId]);
}
}
}
return allgames;
}
function determineAmountofTourneyGames(playerData) {
let alltourneygamesAmountArray = [];
for (let playerId in playerData) {
if (playerData.hasOwnProperty(playerId)) {
let puuid = playerData[playerId][1];
alltourneygamesAmountArray.push((puuid.length > 10) ? ([response("https://europe.api.riotgames.com/lol/match/v5/matches/by-puuid/" puuid "/ids?type=tourney&start=0&count=100&" apikey.substring(1)).length]) : ([0]));
}
}
sheet.getRange(2, 21, playerData.length, 1).setValues(alltourneygamesAmountArray);
}
CodePudding user response：
The cause of the error is that you are calling sheet too many times, and each call is a request which takes time to process and ultimately leads to the timeout. The easy sounding solution is reduce the calls of sheet.getRange() and sheet.setValue() and sheet.getName() etc… so that you ideally do them once per each sheet you are working on. A simple example would be as follows, where everything is done once (except the api call, which ideally should also be done using a bulk api).
function writePlayersApiResToSheet( sheet ){
// get all players data from the sheet in only 1 call
const allPlayers = sheet.getDataRange().getValues()
// remove the header in row 1
allPlayers.shift()
// api responses will be pushed here
const output = []
// do something, like call the api for each user
allPlayers.forEach( player=> output.push([getPlayerInfoFromApi(player)]) )
// write to the sheet once
const startRow = 2
const startCol = 10 // or wherever the api res should go
sheet.getRange( startRow, startCol, allPlayers.length, 1).setValues( output )
}
Google provides this Documentation on this topics.