A simple Google Apps Script for Google Sheets to perform queries on the first column of a Sheet called All, where row 1 is reserved for header labels. Column A holds the search query, column B will contain the romanized original title from AniList, column C will contain the english title, or the romanized title if there is no English title, and …

readme.md

A simple Google Apps Script for Google Sheets to perform queries on the first column of a Sheet called All, where row 1 is reserved for header labels. Column A holds the search query, column B will contain the romanized original title from AniList, column C will contain the english title, or the romanized title if there is no English title, and column D will contain the AniList link. If there is any content (including whitespace) present in columns B, C, or D for any row, that row is skipped. Perform the update on the sheet by calling 'Perform Update' from the 'Anime Update' dropdown that gets made.

queryAnilist.gs

var QUERYSHEETNAME = "All";
var QUERYSHEETRANGE = "All!A2:D";

// Query String for Anilist GraphQl endpoint
// Retrieve id and english title
var query = `
query ($searchString: String) {
  Media(search: $searchString, type: ANIME){
    id
    title {
      english
      romaji
    }
  }
}
`;

// Base URL for the anime link
var baseUrl = "https://anilist.co/anime/";


// Create the Menu object on open of the document
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Anime Update')
      .addItem('Perform Update', 'performUpdate')
      .addToUi();
}

// Query Helper Function
function queryAniList(search) {
  // Create the options for the AniList Request
  var options = {
    'method': 'POST',
    'muteHttpExceptions': true,
    'contentType': 'application/json',
    'payload': JSON.stringify({
      query: query,
      variables: {searchString: search}
    })
  };
  var response = UrlFetchApp.fetch('https://graphql.anilist.co', options);
  response = JSON.parse(response);
  
  // Sanity check to make sure there is data
  if (response && response.data && response.data.Media && response.data.Media.id && response.data.Media.title.romaji)
    //if there's no english name, put the romaji name in place
    return {id: response.data.Media.id,
            english: (response.data.Media.title.english ? response.data.Media.title.english : response.data.Media.title.romaji),
            romaji: response.data.Media.title.romaji};
  
  // If not, return nulls
  else return {id: null, english: null, romaji: null};
}

// Test function
function testQuery(){
  search = "asbdfgdsf";
  Logger.log(queryAniList(search));
}

// Perform the simple update
function performUpdate(){
  var all_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(QUERYSHEETNAME);
  var working_range = SpreadsheetApp.getActiveSpreadsheet().getRange(QUERYSHEETRANGE);
  var working_list = working_range.getValues();
  
  //Cycle through each row
  for(var i = 0; i < working_list.length; i++){
    //Skip if either of the two cells to be filled in are non-empty
    if (("" + working_list[i][1] + working_list[i][2] + working_list[i][3]).length > 0) continue;
    //Skip if there is no search term
    var search = working_list[i][0];
    if (("" + search).replace(/[^\w]/gi, '').length == 0) continue;
    
    //Process out special characters and replace them with spaces
    search = search.replace(/[^\w\s()!?'\/."]/gi, ' ')
    
    //Perform the query and make sure there's a response
    var response = queryAniList(search);
    if (!(response.id && response.english)) continue;
    
    //Update row (this way if there's a crash, it doesn't all fail)
    var update_list = [[response.romaji, response.english, baseUrl + response.id]];
    all_sheet.getRange(i+2, 2, 1, 3).setValues(update_list);
    //yes, GApps Scripts is 1 indexed for this. +1 for the header, +1 for 1 indexing
  }
}