Namokonov

sheets_fetchall

Sep 22nd, 2021 (edited)
267
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2. Таблица с примером https://docs.google.com/spreadsheets/d/161b7qSFSpJh8MMDeKjMRfPUqAFLT4VEqL_PPgV4Wm0o/edit?usp=sharing
  3.  
  4. ✅ Канал о Таблицах: https://t.me/google_sheets
  5. ✅ Чат: https://t.me/google_spreadsheets_chat
  6. ✅ Оглавление канала: https://goo.gl/HdS2qn
  7. */
  8.  
  9. let d = {
  10.   range: "a2:a",
  11.   regExp: 'img src="(.+?)"'
  12. }
  13.  
  14. function onOpen(e) {
  15.   SpreadsheetApp.getUi()
  16.     .createMenu("СКРИПТЫ")
  17.     .addItem("запустить!", "main")
  18.     .addToUi();
  19. }
  20.  
  21. function main() {
  22.   let sh = SpreadsheetApp.getActive();
  23.   let range = sh.getRange(d.range);
  24.   let urls = range.getValues();
  25.   let requests = create_rqsts(urls);
  26.   let response = fetch_all_all_all(requests);
  27.   let matchall = match_all_all_all_all(response);
  28.   let image_urls = redesign(urls, matchall)
  29.   range.offset(0, 1).setValues(image_urls);
  30. }
  31.  
  32. function create_rqsts(urls) {
  33.   return urls.map(row => isURL(row[0]) ? { url: row[0], muteHttpExceptions: true } : '')
  34.     .filter(t => t)
  35. }
  36.  
  37. function fetch_all_all_all(req) {
  38.   let response = UrlFetchApp.fetchAll(req);
  39.   return response;
  40. }
  41.  
  42. function match_all_all_all_all(array) {
  43.   return array.map(page => {
  44.     let m = page.toString().matchAll(d.regExp);
  45.     return 'https://adv-map.com' + [...m]?.[1]?.[1] || '-'
  46.   })
  47. }
  48.  
  49. function redesign(urls, data) {
  50.   return urls.map((h, i) => [isURL(h[0]) ? data[urls.slice(0, i + 1).filter(f => f[0]).length - 1] : '-']);
  51. }
  52.  
  53. function isURL(str) {
  54.   var pattern = new RegExp('^(https?:\\/\\/)?' + // protocol
  55.     '((([a-z\\d]([a-z\\d-]*[a-z\\d])*)\\.?)+[a-z]{2,}|' + // domain name
  56.     '((\\d{1,3}\\.){3}\\d{1,3}))' + // OR ip (v4) address
  57.     '(\\:\\d+)?(\\/[-a-z\\d%_.~+]*)*' + // port and path
  58.     '(\\?[;&a-z\\d%_.~+=-]*)?' + // query string
  59.     '(\\#[-a-z\\d_]*)?$', 'i'); // fragment locator
  60.   return pattern.test(str);
  61. }
  62.  
  63.  
RAW Paste Data