title: Appscript - Grab google calendar event to a spreadsheet
body: <p>I built a code that grabs only one type of google calendar event and sends the information to a spreadsheet. It works, but now I need to turn this function into an html dropdown list, I tried several options but when I click OK it doesn't run. What should I change?
  I'll leave the old code (works) and try a new one (doesn't work).</p>
  <p>Old:</p>
  <pre><code>function onOpen(e) {
  
    // MENU
    
    let ui = SpreadsheetApp.getUi()
  
    ui.createMenu(&quot;⚡&quot;)
      .addItem(&quot;⏹ Atualizar Reuniões&quot;, &quot;atualReunioes&quot;)
      .addItem(' Editar Dados', 'formDados')
      .addItem(' Editar Dados', 'formReunioes')
      .addToUi()
  }
  
  function atualReunioes() {
    var ui = SpreadsheetApp.getUi()
    var result = ui.prompt(&quot;Informe o mês (1 - 12) para transferir as reuniões&quot;, Browser.Buttons.OK_CANCEL)
    var escolha = parseInt(result.getResponseText(), 10)
  
    switch (escolha) {
      case 1:
        dtIni = new Date(&quot;2022-01-01T00:00:00&quot;)
        dtFim = new Date(&quot;2022-01-31T23:59:00&quot;)
        break
  
      case 2:
        dtIni = new Date(&quot;2022-02-01T00:00:00&quot;)
        dtFim = new Date(&quot;2022-02-29T23:59:00&quot;)
        break
  
      case 3:
        dtIni = new Date(&quot;2022-03-01T00:00:00&quot;)
        dtFim = new Date(&quot;2022-03-31T23:59:00&quot;)
        break
  
      case 4:
        dtIni = new Date(&quot;2022-04-01T00:00:00&quot;)
        dtFim = new Date(&quot;2022-04-30T23:59:00&quot;)
        break
  
      case 5:
        dtIni = new Date(&quot;2022-05-01T00:00:00&quot;)
        dtFim = new Date(&quot;2022-05-31T23:59:00&quot;)
        break
  
      case 6:
        dtIni = new Date(&quot;2022-06-01T00:00:00&quot;)
        dtFim = new Date(&quot;2022-06-30T23:59:00&quot;)
        break
  
      case 7:
        dtIni = new Date(&quot;2022-07-01T00:00:00&quot;)
        dtFim = new Date(&quot;2022-07-31T23:59:00&quot;)
        break
  
      case 8:
        dtIni = new Date(&quot;2022-08-01T00:00:00&quot;)
        dtFim = new Date(&quot;2022-08-31T23:59:00&quot;)
        break
  
      case 9:
        dtIni = new Date(&quot;2022-09-01T00:00:00&quot;)
        dtFim = new Date(&quot;2022-09-30T00:00:00&quot;)
        break
  
      case 10:
        dtIni = new Date(&quot;2022-10-01T00:00:00&quot;)
        dtFim = new Date(&quot;2022-10-31T00:00:00&quot;)
        break
  
      case 11:
        dtIni = new Date(&quot;2022-11-01T00:00:00&quot;)
        dtFim = new Date(&quot;2022-11-30T00:00:00&quot;)
        break
  
      case 12:
        dtIni = new Date(&quot;2022-12-01T00:00:00&quot;)
        dtFim = new Date(&quot;2022-12-31T23:59:00&quot;)
        break
  
      default:
  
        if (result.getSelectedButton() === ui.Button.CANCEL || ui.Button.CLOSE) {
          Browser.msgBox(&quot;⛔️ CANCELADO! ⛔️&quot;)
  
        } else {
          Browser.msgBox(&quot;Informe valor entre 1 e 12&quot;)
        
        } return
    }
  
    if (result.getSelectedButton() === ui.Button.OK) {
      var aba = &quot;Reuniões&quot;
  
      myCalendar(aba, dtIni, dtFim)
      ordenaPlan(aba)
  
    }
  }
  
  function toStringFromHtml(html) {
    var text = html.replace(/&lt;br&gt;/g, '\n')
  
    text = text.replace(/&lt;[^&gt;]+&gt;/g, &quot;&quot;)
    text = text.replace(/&amp;nbsp;/g, &quot;&quot;)
  
    return text
  }
  
  // BUSCA CALENDÁRIO
  
  function myCalendar(planilha, dataini, datafim) {
    var calendar = CalendarApp.getCalendarById(&quot;gaesp@mppr.mp.br&quot;)
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(planilha)
    var eventosAgenda = calendar.getEvents(dataini, datafim, { search: 'Reunião' })
    var ultLin = sheet.getLastRow() + 1
  
    eventosAgenda = eventosAgenda.concat(calendar.getEvents(dataini, datafim, { search: 'Visita' }))
  
    for (var i = 0; i &lt; eventosAgenda.length; i++) {
  
      StartTime = eventosAgenda[i].getStartTime()
      Title = eventosAgenda[i].getTitle()
      DescriptionTemp = eventosAgenda[i].getDescription()
      Description = toStringFromHtml(DescriptionTemp)
  
      sheet.getRange(ultLin + i, 1).setValue(StartTime)
      sheet.getRange(ultLin + i, 5).setValue(Title)
      sheet.getRange(ultLin + i, 6).setValue(Description)
  
      var depur = {
        'Última linha': ultLin,
        'Iterador': i,
        'Data': StartTime,
        'Título': Title,
        'Descrição': Description
      }
      console.log(depur)
    }
  }
  
  // ORDENAÇÃO DA ABA REUNIÕES
  
  function ordenaPlan(planilha) {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(planilha)
    var intervalo = sheet.getRange(&quot;A2:H&quot;)
  
    intervalo.sort({ column: 1 })
  }
  </code></pre>
  <p>New
  <strong>code.gs</strong></p>
  <pre><code>function onOpen(e) {
  
    // MENU
    
    let ui = SpreadsheetApp.getUi()
  
    ui.createMenu(&quot;⚡&quot;)
      .addItem(&quot;⏹ Atualizar Reuniões&quot;, &quot;atualReunioes&quot;)
      .addItem(' Editar Dados', 'formDados')
      .addItem(' Editar Dados', 'formReunioes')
      .addToUi()
  }
  
  /// UPDATE MEETS
  
  function atualReunioes() {
    var ui = SpreadsheetApp.getUi()
    var result = [Resp]
    //var result = ui.prompt(&quot;Informe o mês (1 - 12) para transferir as reuniões&quot;, Browser.Buttons.OK_CANCEL)
    //var escolha = parseInt(result.getResponseText(), 10)
    var form = HtmlService.createTemplateFromFile(&quot;html2.html&quot;)
    var eita = form.evaluate().setSandboxMode(HtmlService.SandboxMode.IFRAME)
  
    eita.setHeight(335).setWidth(260)
    ui.showModalDialog(eita, 'Atualizar Reuniões ⏹')
    
    var checarBotaoOK = ['Button1']
    var checarBotaoCancel = ['Button2']
    
    switch (result) {
      case 1:
        dtIni = new Date(&quot;2022-01-01T00:00:00&quot;)
        dtFim = new Date(&quot;2022-01-31T23:59:00&quot;)
        break
  
      case 2:
        dtIni = new Date(&quot;2022-02-01T00:00:00&quot;)
        dtFim = new Date(&quot;2022-02-29T23:59:00&quot;)
        break
  
      case 3:
        dtIni = new Date(&quot;2022-03-01T00:00:00&quot;)
        dtFim = new Date(&quot;2022-03-31T23:59:00&quot;)
        break
  
      case 4:
        dtIni = new Date(&quot;2022-04-01T00:00:00&quot;)
        dtFim = new Date(&quot;2022-04-30T23:59:00&quot;)
        break
  
      case 5:
        dtIni = new Date(&quot;2022-05-01T00:00:00&quot;)
        dtFim = new Date(&quot;2022-05-31T23:59:00&quot;)
        break
  
      case 6:
        dtIni = new Date(&quot;2022-06-01T00:00:00&quot;)
        dtFim = new Date(&quot;2022-06-30T23:59:00&quot;)
        break
  
      case 7:
        dtIni = new Date(&quot;2022-07-01T00:00:00&quot;)
        dtFim = new Date(&quot;2022-07-31T23:59:00&quot;)
        break
  
      case 8:
        dtIni = new Date(&quot;2022-08-01T00:00:00&quot;)
        dtFim = new Date(&quot;2022-08-31T23:59:00&quot;)
        break
  
      case 9:
        dtIni = new Date(&quot;2022-09-01T00:00:00&quot;)
        dtFim = new Date(&quot;2022-09-30T00:00:00&quot;)
        break
  
      case 10:
        dtIni = new Date(&quot;2022-10-01T00:00:00&quot;)
        dtFim = new Date(&quot;2022-10-31T00:00:00&quot;)
        break
  
      case 11:
        dtIni = new Date(&quot;2022-11-01T00:00:00&quot;)
        dtFim = new Date(&quot;2022-11-30T00:00:00&quot;)
        break
  
      case 12:
        dtIni = new Date(&quot;2022-12-01T00:00:00&quot;)
        dtFim = new Date(&quot;2022-12-31T23:59:00&quot;)
        break
        
        default:
  
        if (result == null) {
          ui.alert(&quot;⛔️ CANCELADO! ⛔️&quot;)
  
        } else {
          ui.alert('Aguarde ⏰', ui.ButtonSet.OK)
        
        } return
    }
     if (result == Number) {
      var aba = &quot;Reuniões&quot;
  
      myCalendar(aba, dtIni, dtFim)
      ordenaPlan(aba)
  
    }
  }
  
  function toStringFromHtml(html) {
    var text = html.replace(/&lt;br&gt;/g, '\n')
  
    text = text.replace(/&lt;[^&gt;]+&gt;/g, &quot;&quot;)
    text = text.replace(/&amp;nbsp;/g, &quot;&quot;)
  
    return text
  }
  
  // SEARCH CALENDAR
  
  function myCalendar(planilha, dataini, datafim) {
    var calendar = CalendarApp.getCalendarById(&quot;gaesp@mppr.mp.br&quot;)
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(planilha)
    var eventosAgenda = calendar.getEvents(dataini, datafim, { search: 'Reunião' })
    var ultLin = sheet.getLastRow() + 1
  
    eventosAgenda = eventosAgenda.concat(calendar.getEvents(dataini, datafim, { search: 'Visita' }))
  
    for (var i = 0; i &lt; eventosAgenda.length; i++) {
  
      StartTime = eventosAgenda[i].getStartTime()
      Title = eventosAgenda[i].getTitle()
      DescriptionTemp = eventosAgenda[i].getDescription()
      Description = toStringFromHtml(DescriptionTemp)
  
      sheet.getRange(ultLin + i, 1).setValue(StartTime)
      sheet.getRange(ultLin + i, 5).setValue(Title)
      sheet.getRange(ultLin + i, 6).setValue(Description)
  
      var depur = {
        'Última linha': ultLin,
        'Iterador': i,
        'Data': StartTime,
        'Título': Title,
        'Descrição': Description
      }
      console.log(depur)
    }
  }
  
  
  function ordenaPlan(planilha) {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(planilha)
    var intervalo = sheet.getRange(&quot;A2:H&quot;)
  
    intervalo.sort({ column: 1 })
  }
  </code></pre>
  <p><strong>html.html</strong></p>
  <pre><code>&lt;!DOCTYPE html&gt;
  &lt;html&gt;
    &lt;head&gt;
      &lt;base target=&quot;_top&quot;&gt;
    &lt;/head&gt;
    &lt;body&gt;
   &lt;body style=&quot;font-family: Segoe, 'Segoe UI', 'DejaVu Sans', 'Trebuchet MS', Verdana, sans-serif; font-size: 90%&quot;&gt;
    &lt;p&gt;Selecione o mês para transferir as reuniões:&lt;br&gt;
      &lt;select id=&quot;Campo1&quot; title=&quot;Mês&quot; style=&quot;width:188px&quot;&gt;
      &lt;option value = &quot;&quot; disabled selected&gt;Selecione&lt;/option&gt;
      &lt;option value = &quot;Janeiro&quot;&gt;Janeiro&lt;/option&gt;
      &lt;option value = &quot;Fevereiro&quot;&gt;Fevereiro&lt;/option&gt;
      &lt;option value = &quot;Março&quot;&gt;Março&lt;/option&gt;
      &lt;option value = &quot;Abril&quot;&gt;Abril&lt;/option&gt;
      &lt;option value = &quot;Maio&quot;&gt;Maio&lt;/option&gt;
      &lt;option value = &quot;Junho&quot;&gt;Junho&lt;/option&gt;
      &lt;option value = &quot;Julho&quot;&gt;Julho&lt;/option&gt;
      &lt;option value = &quot;Agosto&quot;&gt;Agosto&lt;/option&gt;
      &lt;option value = &quot;Setembro&quot;&gt;Setembro&lt;/option&gt;
      &lt;option value = &quot;Outubro&quot;&gt;Outubro&lt;/option&gt;
      &lt;option value = &quot;Novembro&quot;&gt;Novembro&lt;/option&gt;
      &lt;option value = &quot;Dezembro&quot;&gt;Dezembro&lt;/option&gt;
      &lt;/select&gt;
    &lt;/p&gt;
    &lt;span style=&quot;text-align: center&quot;&gt;
      &lt;button onclick=&quot;clickOk()&quot; style=&quot;color:#FFFFFF;background:#073763&quot;&gt;Ok&lt;/button&gt;
      &amp;nbsp;
      &lt;button onclick=&quot;google.script.host.close()&quot;&gt;Cancelar&lt;/button&gt;
    &lt;/span&gt;
    &lt;script&gt;
      function clickOk() {
        var Resp1 = document.getElementById('Campo1').value
  
        google.script.run.switch(Resp1)
      }
    &lt;/script&gt;
    &lt;/body&gt;
  &lt;/html&gt;
  </code></pre>