Ajout intelligent de groupes musicaux à Google Sheets via VK API, Tampermonkey et Telegram bot

access_token = '1111111111' // VK token
ss = SpreadsheetApp.getActiveSpreadsheet() // 
sheet = ss.getSheets()[getNumSheet('')] // 
data = sheet.getDataRange().getValues() //  
numRows = sheet.getLastRow()+1 //  
faveTag = '.' //    

//   get 
function doGet(e) {
try {
    var link = e.parameters['link']
    /*
      A - 
      B - English
      C - 
      D - 
      E - 
      F - 
      G - 
    */
    var n = searchExists(link)
    if (n != false) return ContentService.createTextOutput(' '+n+'  !')
    //   API VK https://vk.com/dev/groups.getById
    var html = UrlFetchApp.fetch('https://api.vk.com/method/groups.getById.json?group_id='+link+'&fields=city,description&access_token='+access_token+'&v=5.107')
    //   json   response
    var json = JSON.parse(html).response[0]
    addInTableFromArray(json, link)
    sort()
    return ContentService.createTextOutput('')
  } catch (e) {
     return ContentService.createTextOutput('   doGet! '+e)
  }
}

//  /     
function searchGenre(txt) {
  var t = txt.match(/(?:(?:[||||]|genre|[|||]?)[\s:-]+){1}([a-z-\/-]+)/i)
  if(txt != '' && t) return t[1]
}

//     
function isRus(txt) {
  return txt.search(/[^A-Za-z0-9\/.:_]+/i)
}

// 
function sort(){
  //         
  var tableRange = "A2:G"+numRows
  var editedCell = sheet.getActiveCell()
  var range = sheet.getRange(tableRange)
  //     .    0,   1
  range.sort([{ 
    column : 4,
    ascending: true
  },{
    column: 1,
    ascending: true
  },{
    column: 2,
    ascending: true
  }])
}

//   
function searchExists(t) {
  for (var i = 1; i < data.length; i++) {
    if (t == data[i][2] || 'club'+t == data[i][2]){
      return data[i][0]+data[i][1]
    }
  }
  return false
}

//      
function addInfo(isBot = false) {
  var arr = []
  var j = 0
  var part = 1000 // - ,      . -      UrlFetchApp.fetch
  arr[j] = new Array()
  for (var i = 1; i < data.length; i++) {
    var txt = data[i][2].replace('https://vk.com/','').replace('vk.com/','').replace('^club','')
    if (txt != '' && data[i][0] == '' && data[i][1] == '' && data[i][3] == '' && data[i][4] == '' && data[i][5] == '' && data[i][6] == ''){
      arr[j].push(txt)
      if (arr[j].toString().length > part){
        j++
        arr[j] = new Array()
      }
    }
  }
  if (arr[0].length == 0){
    if (isBot) return false
    else{
      SpreadsheetApp.getUi().alert(' ,   ')
      return false
    }
  }
  //   
  for (var t = 0; t < Math.ceil(arr.toString().length/part); t++) {
    var html = UrlFetchApp.fetch('https://api.vk.com/method/groups.getById.json?group_ids='+arr[t].toString()+'&fields=city,description&access_token='+access_token+'&v=5.107')
    var json = JSON.parse(html).response
    if(json){
      for (var i = 0; i < json.length; i++) {
        var id = json[i].id
        var link = json[i].screen_name
        var name = json[i].name
        var description = (json[i].description)?json[i].description:''
        var city = (json[i].city)?json[i].city.title:''
        var rus = (isRus(name) != -1)?"A":"B"    
        //         arr
        for (var j = 1; j < data.length; j++) {
          var nameCell = data[j][2].replace('https://vk.com/','').replace('vk.com/','').replace('^club','')
          if (nameCell == link || nameCell == id){
            var num = j+1
            break
          }
        }
        sheet.getRange(rus+num).setValue(name.replace('=',''))
        sheet.getRange("C"+num).setValue('=HYPERLINK("https://vk.com/'+link+'";"'+link+'")')
        sheet.getRange("D"+num).setValue(city)
        sheet.getRange("E"+num).setValue(searchGenre(description))
        sheet.getRange("F"+num).setValue(description)
      }
    }
  }
  sort()
  return true
}

//     
function getVkFave(isBot = false) {
  var idTag
  var getTags = UrlFetchApp.fetch('https://api.vk.com/method/fave.getTags.json?access_token='+access_token+'&v=5.107')
  var res = JSON.parse(getTags).response
  var iTag = res.items
  for (var i = 0; i < iTag.length; i++) {
    if (iTag[i].name == faveTag) idTag = iTag[i].id
  }
  
  // 
  if (!isBot){
    var ui = SpreadsheetApp.getUi()
    var resp = ui.alert('     "'+faveTag+'"     . ?', ui.ButtonSet.YES_NO)
  }
  var inside = function (){
    var getPages = UrlFetchApp.fetch('https://api.vk.com/method/fave.getPages.json?tag_id='+idTag+'&fields=city,description&access_token='+access_token+'&v=5.107')
    var iPage = JSON.parse(getPages).response.items
    //    .   ,   
    for (var j = 0; j < iPage.length; j++) {
      var gr = iPage[j].group
      addInTableFromArray(gr)
      numRows++
        UrlFetchApp.fetch('https://api.vk.com/method/fave.removePage?group_id='+gr.id+'&access_token='+access_token+'&v=5.107')
        Utilities.sleep(1000) // 
    }
    sort()
  }
  if (isBot) { // ,      ui
    inside()
  }else if(resp == ui.Button.YES) {
    inside()
  }
}

//    
function addInTableFromArray(arr, linkIn) {
  if (linkIn){
    var link = linkIn
  }else{
    var link = arr.screen_name
    if (searchExists(link) != false) return false
  }
  var name = arr.name // 
  var description = (arr.description)?arr.description:'' //
  var city = (arr.city)?arr.city.title:'' // 
  //      ,      "English"
  var rus = (isRus(name) != -1)?"A":"B"
  //    
  sheet.getRange(rus+numRows).setValue(name.replace('=',''))
  sheet.getRange("C"+numRows).setValue('=HYPERLINK("https://vk.com/'+link+'";"'+link+'")')
  sheet.getRange("D"+numRows).setValue(city)
  sheet.getRange("E"+numRows).setValue(searchGenre(description))
  sheet.getRange("F"+numRows).setValue(description)
}
//  . .
function checkActualLink(){
  for (var i = 1; i < data.length; i++) {
    try {
      var num = i+1
      UrlFetchApp.fetch("https://vk.com/"+data[i][2])
      sheet.getRange('C'+num).setBackgroundColor('')
    }catch(err) {
      var num = i+1
      sheet.getRange('C'+num).setBackgroundColor('red')
    }
  }
}
//  URL  .     
function getStatusUrl(url){
   var options = {
     'muteHttpExceptions': true,
     'followRedirects': false
   }
   var response = UrlFetchApp.fetch(url.trim(), options)
   return response.getResponseCode()
}
//    
function getNumSheet(nameList){
  var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(nameList)
  if (s != null) {
    return s.getIndex()-1
  }else{
    return false
  }
}
// 
function escapeHtml(text) {
  return text
      .replace(/&/g, "")
      .replace(/</g, "")
      .replace(/>/g, "");
}
// 
function onOpen() {
  SpreadsheetApp.getUi()
  .createMenu(' ')
  .addItem('', 'sort')
  .addItem('   ', 'getVkFave')
  .addItem(' ', 'addInfo')
  .addToUi();
}

//   
function ll(t){
  return Logger.log(t)
}

var botApi = 'https://api.telegram.org/bot1123123:AAA/' //  Telegram  
/* :
 /start -   
 /sort - 
 /getvkfave -     
 /addinfo -  
 /getall -   
*/
function doPost(e){
  var inp = JSON.parse(e.postData.contents)
  // 
  var sheet2 = ss.getSheets()[getNumSheet('')]
  sheet2.getRange('A1').setValue(inp)
  
  var inpTxt = inp.message.text
  var chatId = inp.message.chat.id
  
  var link = inpTxt.replace('https://vk.com/','').replace('vk.com/','').replace('^club','')
  //  
  // start
  if (inpTxt == '/start' || inpTxt == ' '){
    sendText(chatId, '      Google .\n'+
                    '/start -   \n'+
                    '/sort - \n'+
                    '/getvkfave -    \n'+
                    '/addinfo -  \n'+
                    '/getall -   ')
    return true
  }
  // sort
  if (inpTxt == '/sort' || inpTxt == ''){
    sort()
    sendText(chatId, '')
    return true
  }
  // getvkfave
  if (inpTxt == '/getvkfave' || inpTxt == '   '){
    getVkFave(true)
    sendText(chatId, '')
    return true
  }
  // addinfo
  if (inpTxt == '/addinfo' || inpTxt == ' '){
    if (addInfo(true)) sendText(chatId, '')
    else sendText(chatId, ' ,   ')
    return true
  }
  // getall
  if (inpTxt == '/getall' || inpTxt == '  '){
    var arr = [[],[],[],[],[],[],[],[],[],[],[' ']] // TODO 
    var repeat = ''
    var n = 0
    var j = 0
    for (var i = 1; i < data.length; i++) {
      if (j == 4){ //    
        n++
        j = 0
      }
      if (repeat != data[i][3]){
        arr[n][j] = data[i][3]
        if (data[i][3] == '') arr[n][j] = '---'
        j++
      }
      repeat = data[i][3]
    }
    var key = JSON.stringify({keyboard: 
                              arr,
                              resize_keyboard:true,
                              one_time_keyboard:true
                            });
    sendText(chatId, ' ', key)
    return true
  }
  
  // 
  if (isRus(link) != -1) {
    var name = (inpTxt == '---')?' ':inpTxt
    for (var j = 1; j < data.length; j++) {
      if (data[j][3] == inpTxt || inpTxt == '---') {
        var str = ' '+name+'\n\n'
        for (var i = 1; i < data.length; i++) {
          if ((data[j][3] == data[i][3]) || (data[i][3] == '' && inpTxt == '---')) {
            if (str.length >= 4000) { //   -    Telegram 
              sendText(chatId, str)
              str = ''
            }
            str = str + '<a href="https://vk.com/' + data[i][2] + '">' + escapeHtml(data[i][0] + data[i][1]) + '</a> \n'
          }
        }
        if (str != '') sendText(chatId, str)
        return true
      }
    }
    sendText(chatId, inpTxt + ' -   .    ')
    return true
  }
  //   
  if (getStatusUrl('https://vk.com/'+link) != 200 && getStatusUrl('https://vk.com/club'+link) != 200){
    sendText(chatId, inpTxt+' -  ')
    return true
  }
  
  var name = searchExists(link)
  if (name != false){
    sendText(chatId, ' '+name+'  ')
    return true
  }else{
    var html = UrlFetchApp.fetch('https://api.vk.com/method/groups.getById.json?group_id='+link+'&fields=city,description&access_token='+access_token+'&v=5.103')
    var json = JSON.parse(html).response[0]
    addInTableFromArray(json, link)
    sort()
    sendText(chatId, ' '+json.name+' ')
    return true
  }
}
//  
function sendText(chatId, text, key = ''){
  var payload = {
    'method': 'sendMessage',
    'chat_id': String(chatId),
    'text': text,
    'parse_mode': 'HTML',
    'reply_markup': key,
    'disable_web_page_preview': true,
    'one_time_keyboard':true
  }     
  var data = {
    "method": "post",
    "payload": payload
  }
  //    UrlFetchApp.fetch   ll(text)
  UrlFetchApp.fetch(botApi, data)
}

// ==UserScript==
// @name         vkGroupToGS
// @namespace    https://vk.com/*
// @version      0.1
// @author       You
// @match        https://vk.com/*
// @grant        GM_xmlhttpRequest
// ==/UserScript==
    var url_first = 'https://script.google.com/macros/s/AAA/exec'
    var url_short = document.location.href.replace("https://vk.com/", "")
    var d = document.createElement('div')
    var head = document.querySelector('.left_menu_nav_wrap')
    d.setAttribute('id', 'send_group')
    d.style.display = 'inline-block'
    d.style.position = 'relative'
    d.style.fontSize = '50pt'
    d.style.cursor = 'pointer'
    d.innerHTML = '+'
    head.parentNode.appendChild(d)
    d.onclick = function() {
    setTimeout(function() {
        GM_xmlhttpRequest({
            method: 'GET',
            url: url_first + '?link=' + url_short,
            headers: {
                'Accept': 'application/atom+xml,application/xml,text/xml'
            },
            onload: function(x) {
                console.log(x.responseText)
                if (//.test(x.responseText)) document.querySelector('#send_group').style.color = 'red'
                else document.querySelector('#send_group').style.color = 'green'
            }
        })
    }, 0)
};

// ==UserScript==
// @name         VK_event_to_list_mvk
// @namespace    https://m.vk.com/*
// @match        https://m.vk.com/*
// @grant    GM_xmlhttpRequest
// ==/UserScript==
setTimeout(function() {
    var url_first = 'https://script.google.com/macros/s/111/exec';
    var url_short = document.location.href.replace("https://m.vk.com/", "");
    GM_xmlhttpRequest({
        method: 'GET',
        url: url_first + '?link=' + url_short,
        headers: {
            'Accept': 'application/atom+xml,application/xml,text/xml'
        },
        onload: function(x) {
            alert(x.responseText);
        }
    });
}, 2000);