• MAL API
• Range Class
• Sheet Class
• Spreadsheet Class
• NamedRange Class
• UrlFetchApp Class
• HTTPResponse Class
• GmailApp Class

// @ts-nocheck

var startTime = Date.now()

const ss = SpreadsheetApp.getActiveSpreadsheet()

const statsWS = ss.getSheetByName("Stats")

const animeWS = ss.getSheetByName("Anime")

const dataWS = ss.getSheetByName("Data")

var list = []

var headList = []

function main() {

//Trys the code, if error it emails me the error

//Clears Google Sheet

animeWS.clear()

//Gets all data using MAL API

list = getAllData()

//Adds blanks to make all desired array values equal in length

list = blankAdd()

//Adds header

headList = addHeader()

//Prints to Google Sheet

printToSheet()

postSheet()

//Updates Timeline Chart

updateTimeline()

//Adds how long the script took to run

statsWS.getRange("B5").setValue(((Date.now()-startTime)/1000))

}

//A function that will run main but eMAIl me any errors

function eMailMain() {

try {

main()

} catch(err)

{

MailApp.sendEmail("Tyler-DK@hotmail.com","MAL Error",err)

}

}

//A function that gets all data from MAL and returns a list

function getAllData() {

var allList = []

//Forever loop that keeps incrimenting by a thousand to get all data

for(var i = 0; true; i = i + 1000)

{

//Get the section of data

list = getSection(i)

//If it has data

if (list.data.length > 0) {

//Removes unwanted values

list = cleanData()

//Adds remaining values to the allList

for(var a = 0; a < list.data.length; a = a + 1)

{

allList.push(list.data[a])

}

} else {

//Else leaves the forever loop

break;

}

}

return allList

}

//A function that uses the MAL API to get a list of data based on the offset

function getSection(offset) {

//URL for MAL API request

var url = "https://api.myanimelist.net/v2/users/Tyler_Pancakes/animelist?limit=1000&fields=id,title,list_status%7Bstatus,num_times_rewatched,score,num_episodes_watched,is_rewatching,start_date,finish_date,priority,num_times_rewatched,rewatch_value,tags,comments,updated_at%7D,media_type,average_episode_duration,alternative_titles,genres,source,main_picture,start_date,end_date,synopsis,mean,rank,popularity,num_list_users,num_scoring_users,nsfw,created_at,updated_at,status,num_episodes,start_season,broadcast,rating,studios&nsfw=true"+"&offset="+offset;

//Header for MAL API request

var headers = {

"contentType": "application/json",

"headers":{"X-MAL-CLIENT-ID": "5234607d3d95af9bd38278d7346cc266"}

};

//Does the MAL API request until a successful attempt

var response

while(response == undefined)

{

try{

response = UrlFetchApp.fetch(url, headers).getContentText();

}

catch(err)

{

console.log(err)

}

}

//return response

return JSON.parse(response)

}

//A function that will remove unwanted values

function cleanData() {

//Goes through the list og objects

for(var i = 0; i < list.data.length; i = i + 1)

{

//Gets the value of the show

var val = list.data[i].list_status

//If it is unwanted

if(val.status == "plan_to_watch" && val.num_episodes_watched == 0)

{

//It is removed

list.data.splice(i,1)

//i correction to hit all values

i = i - 1

}

}

return list

}

//A function that lengthens every desired array in the show to line up correctly when put in Google Sheets

function blankAdd() {

//Create variables

var maxGenre = list[0].node.genres.length

var maxSynonyms = list[0].node.alternative_titles.synonyms.length

//Finds max length of desired arrays

for(var i = 1; i < list.length; i = i + 1)

{

var testGenre = list[i].node.genres.length

var testSynonyms = list[i].node.alternative_titles.synonyms.length

if (testGenre > maxGenre)

{

maxGenre = testGenre

}

if(testSynonyms > maxSynonyms)

{

maxSynonyms = testSynonyms

}

}

//Adds blanks

for(var i = 0; i < list.length; i = i + 1)

{

var genres = list[i].node.genres

var synonyms = list[i].node.alternative_titles.synonyms

while(genres.length < maxGenre)

{

genres.push("")

}

while(synonyms.length < maxSynonyms)

{

synonyms.push("")

}

}

//Returns list

return list

}

//Prints all desired values to the Google Sheet

function printToSheet() {

//Gets the Header row

const range = animeWS.getRange(1,1,1,animeWS.getMaxColumns())

//Finds the starting column position of various Headers

var daysColumn = range.createTextFinder("Total Days").matchCase(true).matchEntireCell(true).findNext().getColumn()

var durColumn = range.createTextFinder("Avg. Ep Duration (min)").matchCase(true).matchEntireCell(true).findNext().getColumn()

var epColumn = range.createTextFinder("Total Ep.").matchCase(true).matchEntireCell(true).findNext().getColumn()

var totalShowDurationColumn = range.createTextFinder("Total Show Duration (hr)").matchCase(true).matchEntireCell(true).findNext().getColumn()

//Goes through all values

var totalAdd = []

for(var i = 0; i < list.length; i = i + 1)

{

var value = list[i]

//List to add all values to

var add = []

//Adding desired values to list

add.push(value.node.id)

add.push(value.node.title)

add.push(value.node.media_type)

add.push(value.node.rating)

add.push(value.node.mean)

add.push(value.node.rank)

add.push(value.node.popularity)

add.push(value.node.average_episode_duration/60)

//Total Show Duration (hr) formula

var episodes = animeWS.getRange(i+2,epColumn).getA1Notation()

var duration = animeWS.getRange(i+2,durColumn).getA1Notation()

add.push("=DIVIDE(PRODUCT("+episodes+","+duration+"),60)")

add.push(value.node.source)

add.push(value.node.start_season.season)

add.push(value.node.start_season.year)

add.push(value.list_status.status)

add.push(value.list_status.start_date)

add.push(value.list_status.finish_date)

//Total Days Formula

var startCell = animeWS.getRange(i+2,daysColumn-2).getA1Notation()

var endCell = animeWS.getRange(i+2,daysColumn-1).getA1Notation()

add.push("=if((LEN("+startCell+")>0)*(LEN("+endCell+")>0),MINUS("+endCell + "," + startCell+'),"")')

//Avg. Hours Per Day Formula

var days = animeWS.getRange(i+2,daysColumn).getA1Notation()

var totalShowDuration = animeWS.getRange(i+2,totalShowDurationColumn).getA1Notation()

add.push("=if(LEN("+days+")>0,"+"DIVIDE("+totalShowDuration+","+days+'+1),"")')

add.push(value.list_status.num_episodes_watched)

add.push(value.node.num_episodes)

add.push(value.list_status.num_episodes_watched / value.node.num_episodes * 100)

add.push(value.list_status.num_times_rewatched)

add.push(value.node.nsfw)

add.push(value.node.alternative_titles.en)

add.push(value.node.alternative_titles.ja)

value.node.alternative_titles.synonyms.map(f => add.push(f))

value.node.genres.map(f => add.push(f.name))

//Adding values to totalAdd

totalAdd.push(add)

}

//Prints values to Google Sheet

animeWS.getRange(2,1,totalAdd.length,totalAdd[0].length).setValues(totalAdd)

}

//Adds the Header to the Google Sheet

function addHeader() {

//Sets all Headers before array Values

var add = ["MAL ID","Title","Media Type", "Rating", "Mean Score", "Rank", "Popularity Rank", "Avg. Ep Duration (min)","Total Show Duration (hr)","Source", "Start Season", "Start Year", "Status","Start Date","End Date","Total Days","Hours Per Day", "Ep. Watched","Total Ep.", "% Complete","Times Rewatched", "NSFW?", "English Title","Japanese Title"]

//Sets various lengths as variables

var initial = add.length + 1

var synLen = list[0].node.alternative_titles.synonyms.length

var genLen = list[0].node.genres.length

//Adds Columns to Google Sheet to prevent errors

while(animeWS.getMaxColumns() < initial-1+synLen+genLen)

{

animeWS.insertColumnsBefore(1,1)

}

//Merges Columns

animeWS.getRange(1,initial,1,synLen).mergeAcross()

animeWS.getRange(1, initial+synLen, 1, genLen).mergeAcross()

//Add filler values to copinsate for merged columns

list[0].node.alternative_titles.synonyms.map(f => add.push("Synonyms"))

list[0].node.genres.map(f => add.push("Genres"))

//Print to Google Sheet

animeWS.appendRow(add)

return add

}

//A function that cleans up the sheet and adds information

function postSheet() {

//Formats all cells

animeWS.autoResizeColumns(1,animeWS.getMaxColumns())

animeWS.getRange(1,1,list.length+1,animeWS.getMaxColumns()).setVerticalAlignment("middle")

animeWS.getRange(1,1,list.length+1,animeWS.getMaxColumns()).setHorizontalAlignment("center")

//Adds current date to Google Sheet

var date = new Date()

statsWS.getRange("B4").setValue(date.toLocaleString())

statsWS.autoResizeColumns(1,2)

//Adds all unique items to newHeader

var newHeader = [headList[0]]

for(var i = 0; i < headList.length; i = i + 1)

{

var count = 1

//Test if it is all ready added to newHeader, if so increases count

for(var a = 0; a < newHeader.length; a = a + 1)

{

if(headList[i] == newHeader[a])

{

count = count + 1

break;

}

}

//Sees if count has changed in value if not adds value to newHeader

if(count == 1)

{

newHeader.push(headList[i])

}

}

//Adds how many instances of a value in headList and adds it to headerCount

var headerCount = []

for(var i = 0; i < newHeader.length; i = i + 1)

{

var count = 0

//Counts how many instances the value shows up

for( var a = 0; a < headList.length; a = a + 1)

{

if(headList[a] == newHeader[i])

{

count = count + 1

}

}

headerCount.push(count)

}

//Removes all illegal and unwanted characters, like spaces

var spaceHeader = []

for(var i = 0; i < newHeader.length; i = i + 1)

{

var value = newHeader[i]

//Remove known illegal or unwanted values

if(value == "Avg. Ep Duration (min)")

{

value = value.substr(0,value.length-6)

} else if (value == "% Complete")

{

value = value.substr(2)

} else if (value == "Total Show Duration (hr)")

{

value = value.substr(0,value.length-5)

}

//Removes all spaces

while(value.indexOf(" ") > 0)

{

var index = value.indexOf(" ")

value = value.substr(0,index) + value.substr(index+1)

}

//Adds to list

spaceHeader.push(value)

}

//Creates Google Sheet Named Ranges for all recorded values

for(var i = 0; i < spaceHeader.length; i = i + 1)

{

//Finds the cell the header is in

cellFound = animeWS.getRange(1,1,1,animeWS.getMaxColumns()).createTextFinder(newHeader[i]).matchCase(true).matchEntireCell(true).findNext()

//Creates Named Range based on the cell found

ss.setNamedRange(spaceHeader[i],animeWS.getRange(cellFound.getRow()+1,cellFound.getColumn(),animeWS.getMaxRows()-1,headerCount[i]))

//Adds a Named Range for both start season and start year

if(newHeader[i] == "Start Season")

{

ss.setNamedRange("StartSeasonYear",animeWS.getRange(cellFound.getRow()+1,cellFound.getColumn(),animeWS.getMaxRows()-1,2))

}

}

}

//A function that updates charts and data

function updateTimeline() {

//Finds the date column

date = dataWS.getRange(1,1,1,animeWS.getMaxColumns()).createTextFinder("Date").matchCase(true).matchEntireCell(true).findNext()

//Initialises the max and min dates

min = list[0].list_status.start_date

max = list[0].list_status.finish_date

//Finds the max and min dates

for (var i = 0; i < list.length; i++)

{

item = list[i].list_status

if (Date.parse(item.start_date) < Date.parse(min))

{

min = item.start_date

}

if (Date.parse(item.finish_date) > Date.parse(max))

{

max = item.finish_date

}

}

//Adds the fourmula to find the date and time for the chart

totalAdd = [[min, '=SUMIFS(HoursPerDay,StartDate,CONCAT("<=",J2),EndDate,CONCAT(">=",J2))']]

for (var i = 3; i < ((Date.parse(max) - Date.parse(min)) / 1000 / 60 / 60 / 24) + 3; i++)

{

add = []

rowAbove = dataWS.getRange(i - 1, date.getColumn()).getA1Notation()

row = dataWS.getRange(i, date.getColumn()).getA1Notation()

add.push("=if(((" + rowAbove + "+1)<=MAX(EndDate))*(LEN(" + rowAbove + ")>0),(" + rowAbove + "+1),null)")

add.push('=SUMIFS(HoursPerDay,StartDate,CONCAT("<=",' + row + '),EndDate,CONCAT(">=",' + row + '))')

totalAdd.push(add)

}

//Adds and formats values to the Google Sheet

dataWS.getRange(date.getRow() + 1, date.getColumn(), totalAdd.length, totalAdd[0].length).setValues(totalAdd)

dataWS.getRange(1, 1, dataWS.getMaxRows(), dataWS.getMaxColumns()).setVerticalAlignment("middle")

dataWS.getRange(1, 1, dataWS.getMaxRows(), dataWS.getMaxColumns()).setHorizontalAlignment("center")

}

postSheet