Advertisement
RemcoE33

OpenLibrary books

Jul 12th, 2022
817
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. //https://docs.google.com/spreadsheets/d/1Liq71Kdhe45sYh2Puk6hrpXIEpVw_GNHsZmKnMvoF4U/edit#gid=2100307022
  2.  
  3. function onOpen(e) {
  4.   SpreadsheetApp.getUi().createMenu('📚 Books')
  5.     .addItem('📥 Load selected row', 'getBookData')
  6.     .addToUi();
  7. }
  8.  
  9. function getBookData() {
  10.   const ss = SpreadsheetApp.getActiveSpreadsheet()
  11.   const sheet = ss.getActiveSheet();
  12.  
  13.   if(sheet.getName() != 'Books'){
  14.     throw new Error('Active sheet is not Books!')
  15.   }
  16.  
  17.   const row = sheet.getActiveCell().getRow()
  18.   const isbn = sheet.getRange(row,2).getValue()
  19.   const urlDetails = `https://openlibrary.org/api/books?bibkeys=ISBN:${isbn}&format=json&jscmd=details`
  20.   const requestDetails = UrlFetchApp.fetch(urlDetails)
  21.   const dataDetails = JSON.parse(requestDetails.getContentText())[`ISBN:${isbn}`]
  22.  
  23.   const urlData = `https://openlibrary.org/api/books?bibkeys=ISBN:${isbn}&format=json&jscmd=data`
  24.   const requestData = UrlFetchApp.fetch(urlData)
  25.   const dataData = JSON.parse(requestData.getContentText())[`ISBN:${isbn}`]
  26.  
  27.  
  28.   const bookUrl = dataDetails.info_url
  29.   const title = dataDetails.details.title
  30.   const coverUrl = dataData.cover.large
  31.   const author = dataData.authors[0].name
  32.   const authorUrl = `https://openlibrary.org/${dataData.authors[0].key}`
  33.   const year = dataDetails.details.publish_date
  34.   const pages = dataDetails.details.number_of_pages
  35.   const genre = [...new Set(dataDetails.details.genres)].join(" / ")
  36.  
  37.   const formulas = [[
  38.     `=HYPERLINK("${bookUrl}",IMAGE("${coverUrl}"))`,
  39.     `=HYPERLINK("${bookUrl}","${title}")`,
  40.     `=HYPERLINK("${authorUrl}","${author}")`
  41.   ]]
  42.  
  43.   sheet.getRange(row, 3,1,3).setFormulas(formulas)
  44.   sheet.getRange(row, 6,1,3).setValues([[year, pages, genre]])
  45. }
  46.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement