Appscript - Grab google calendar event to a spreadsheet

voidalzk 0
Viewed 42

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).

Old:

function onOpen(e) {

  // MENU
  
  let ui = SpreadsheetApp.getUi()

  ui.createMenu("⚡")
    .addItem("⏹ Atualizar Reuniões", "atualReunioes")
    .addItem(' Editar Dados', 'formDados')
    .addItem(' Editar Dados', 'formReunioes')
    .addToUi()
}

function atualReunioes() {
  var ui = SpreadsheetApp.getUi()
  var result = ui.prompt("Informe o mês (1 - 12) para transferir as reuniões", Browser.Buttons.OK_CANCEL)
  var escolha = parseInt(result.getResponseText(), 10)

  switch (escolha) {
    case 1:
      dtIni = new Date("2022-01-01T00:00:00")
      dtFim = new Date("2022-01-31T23:59:00")
      break

    case 2:
      dtIni = new Date("2022-02-01T00:00:00")
      dtFim = new Date("2022-02-29T23:59:00")
      break

    case 3:
      dtIni = new Date("2022-03-01T00:00:00")
      dtFim = new Date("2022-03-31T23:59:00")
      break

    case 4:
      dtIni = new Date("2022-04-01T00:00:00")
      dtFim = new Date("2022-04-30T23:59:00")
      break

    case 5:
      dtIni = new Date("2022-05-01T00:00:00")
      dtFim = new Date("2022-05-31T23:59:00")
      break

    case 6:
      dtIni = new Date("2022-06-01T00:00:00")
      dtFim = new Date("2022-06-30T23:59:00")
      break

    case 7:
      dtIni = new Date("2022-07-01T00:00:00")
      dtFim = new Date("2022-07-31T23:59:00")
      break

    case 8:
      dtIni = new Date("2022-08-01T00:00:00")
      dtFim = new Date("2022-08-31T23:59:00")
      break

    case 9:
      dtIni = new Date("2022-09-01T00:00:00")
      dtFim = new Date("2022-09-30T00:00:00")
      break

    case 10:
      dtIni = new Date("2022-10-01T00:00:00")
      dtFim = new Date("2022-10-31T00:00:00")
      break

    case 11:
      dtIni = new Date("2022-11-01T00:00:00")
      dtFim = new Date("2022-11-30T00:00:00")
      break

    case 12:
      dtIni = new Date("2022-12-01T00:00:00")
      dtFim = new Date("2022-12-31T23:59:00")
      break

    default:

      if (result.getSelectedButton() === ui.Button.CANCEL || ui.Button.CLOSE) {
        Browser.msgBox("⛔️ CANCELADO! ⛔️")

      } else {
        Browser.msgBox("Informe valor entre 1 e 12")
      
      } return
  }

  if (result.getSelectedButton() === ui.Button.OK) {
    var aba = "Reuniões"

    myCalendar(aba, dtIni, dtFim)
    ordenaPlan(aba)

  }
}

function toStringFromHtml(html) {
  var text = html.replace(/<br>/g, '\n')

  text = text.replace(/<[^>]+>/g, "")
  text = text.replace(/&nbsp;/g, "")

  return text
}

// BUSCA CALENDÁRIO

function myCalendar(planilha, dataini, datafim) {
  var calendar = CalendarApp.getCalendarById("gaesp@mppr.mp.br")
  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 < 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("A2:H")

  intervalo.sort({ column: 1 })
}

New code.gs

function onOpen(e) {

  // MENU
  
  let ui = SpreadsheetApp.getUi()

  ui.createMenu("⚡")
    .addItem("⏹ Atualizar Reuniões", "atualReunioes")
    .addItem(' Editar Dados', 'formDados')
    .addItem(' Editar Dados', 'formReunioes')
    .addToUi()
}

/// UPDATE MEETS

function atualReunioes() {
  var ui = SpreadsheetApp.getUi()
  var result = [Resp]
  //var result = ui.prompt("Informe o mês (1 - 12) para transferir as reuniões", Browser.Buttons.OK_CANCEL)
  //var escolha = parseInt(result.getResponseText(), 10)
  var form = HtmlService.createTemplateFromFile("html2.html")
  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("2022-01-01T00:00:00")
      dtFim = new Date("2022-01-31T23:59:00")
      break

    case 2:
      dtIni = new Date("2022-02-01T00:00:00")
      dtFim = new Date("2022-02-29T23:59:00")
      break

    case 3:
      dtIni = new Date("2022-03-01T00:00:00")
      dtFim = new Date("2022-03-31T23:59:00")
      break

    case 4:
      dtIni = new Date("2022-04-01T00:00:00")
      dtFim = new Date("2022-04-30T23:59:00")
      break

    case 5:
      dtIni = new Date("2022-05-01T00:00:00")
      dtFim = new Date("2022-05-31T23:59:00")
      break

    case 6:
      dtIni = new Date("2022-06-01T00:00:00")
      dtFim = new Date("2022-06-30T23:59:00")
      break

    case 7:
      dtIni = new Date("2022-07-01T00:00:00")
      dtFim = new Date("2022-07-31T23:59:00")
      break

    case 8:
      dtIni = new Date("2022-08-01T00:00:00")
      dtFim = new Date("2022-08-31T23:59:00")
      break

    case 9:
      dtIni = new Date("2022-09-01T00:00:00")
      dtFim = new Date("2022-09-30T00:00:00")
      break

    case 10:
      dtIni = new Date("2022-10-01T00:00:00")
      dtFim = new Date("2022-10-31T00:00:00")
      break

    case 11:
      dtIni = new Date("2022-11-01T00:00:00")
      dtFim = new Date("2022-11-30T00:00:00")
      break

    case 12:
      dtIni = new Date("2022-12-01T00:00:00")
      dtFim = new Date("2022-12-31T23:59:00")
      break
      
      default:

      if (result == null) {
        ui.alert("⛔️ CANCELADO! ⛔️")

      } else {
        ui.alert('Aguarde ⏰', ui.ButtonSet.OK)
      
      } return
  }
   if (result == Number) {
    var aba = "Reuniões"

    myCalendar(aba, dtIni, dtFim)
    ordenaPlan(aba)

  }
}

function toStringFromHtml(html) {
  var text = html.replace(/<br>/g, '\n')

  text = text.replace(/<[^>]+>/g, "")
  text = text.replace(/&nbsp;/g, "")

  return text
}

// SEARCH CALENDAR

function myCalendar(planilha, dataini, datafim) {
  var calendar = CalendarApp.getCalendarById("gaesp@mppr.mp.br")
  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 < 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("A2:H")

  intervalo.sort({ column: 1 })
}

html.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
 <body style="font-family: Segoe, 'Segoe UI', 'DejaVu Sans', 'Trebuchet MS', Verdana, sans-serif; font-size: 90%">
  <p>Selecione o mês para transferir as reuniões:<br>
    <select id="Campo1" title="Mês" style="width:188px">
    <option value = "" disabled selected>Selecione</option>
    <option value = "Janeiro">Janeiro</option>
    <option value = "Fevereiro">Fevereiro</option>
    <option value = "Março">Março</option>
    <option value = "Abril">Abril</option>
    <option value = "Maio">Maio</option>
    <option value = "Junho">Junho</option>
    <option value = "Julho">Julho</option>
    <option value = "Agosto">Agosto</option>
    <option value = "Setembro">Setembro</option>
    <option value = "Outubro">Outubro</option>
    <option value = "Novembro">Novembro</option>
    <option value = "Dezembro">Dezembro</option>
    </select>
  </p>
  <span style="text-align: center">
    <button onclick="clickOk()" style="color:#FFFFFF;background:#073763">Ok</button>
    &nbsp;
    <button onclick="google.script.host.close()">Cancelar</button>
  </span>
  <script>
    function clickOk() {
      var Resp1 = document.getElementById('Campo1').value

      google.script.run.switch(Resp1)
    }
  </script>
  </body>
</html>
google-sheets google-apps-script javascript html
1 Answers

In your situation, how about the following modification? In this modification, your New code.gs and html.html are modified.

New code.gs:

In this modification, from New code.gs, I modified atualReunioes() and added a new function of nextFunction(result). Other functions are not modified. Because from It works, I guessed that your other functions of myCalendar(aba, dtIni, dtFim); and ordenaPlan(aba); work fine. Please be careful about this.

By the way, in your showing HTML, the filename is html.html. But in your showing script, it's html2.html. Please be careful about this.

// I modified this function.
function atualReunioes() {
  var form = HtmlService.createTemplateFromFile("html.html");
  var eita = form.evaluate().setSandboxMode(HtmlService.SandboxMode.IFRAME).setHeight(335).setWidth(260);
  SpreadsheetApp.getUi().showModalDialog(eita, 'Atualizar Reuniões ⏹');
}

// Added this new function.
function nextFunction(result) {
  result = Number(result);
  switch (result) {
    case 1:
      dtIni = new Date("2022-01-01T00:00:00");
      dtFim = new Date("2022-01-31T23:59:00");
      break;
    case 2:
      dtIni = new Date("2022-02-01T00:00:00");
      dtFim = new Date("2022-02-29T23:59:00");
      break;
    case 3:
      dtIni = new Date("2022-03-01T00:00:00");
      dtFim = new Date("2022-03-31T23:59:00");
      break;
    case 4:
      dtIni = new Date("2022-04-01T00:00:00");
      dtFim = new Date("2022-04-30T23:59:00");
      break;
    case 5:
      dtIni = new Date("2022-05-01T00:00:00");
      dtFim = new Date("2022-05-31T23:59:00");
      break;
    case 6:
      dtIni = new Date("2022-06-01T00:00:00");
      dtFim = new Date("2022-06-30T23:59:00");
      break;
    case 7:
      dtIni = new Date("2022-07-01T00:00:00");
      dtFim = new Date("2022-07-31T23:59:00");
      break;
    case 8:
      dtIni = new Date("2022-08-01T00:00:00");
      dtFim = new Date("2022-08-31T23:59:00");
      break;
    case 9:
      dtIni = new Date("2022-09-01T00:00:00");
      dtFim = new Date("2022-09-30T00:00:00");
      break;
    case 10:
      dtIni = new Date("2022-10-01T00:00:00");
      dtFim = new Date("2022-10-31T00:00:00");
      break;
    case 11:
      dtIni = new Date("2022-11-01T00:00:00");
      dtFim = new Date("2022-11-30T00:00:00");
      break;
    case 12:
      dtIni = new Date("2022-12-01T00:00:00");
      dtFim = new Date("2022-12-31T23:59:00");
      break;
  }
  var aba = "Reuniões";
  myCalendar(aba, dtIni, dtFim);
  ordenaPlan(aba);
}

html.html:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
 <body style="font-family: Segoe, 'Segoe UI', 'DejaVu Sans', 'Trebuchet MS', Verdana, sans-serif; font-size: 90%">
  <p>Selecione o mês para transferir as reuniões:<br>
    <select id="Campo1" title="Mês" style="width:188px">
    <option value = "" disabled selected>Selecione</option>
    <option value = "1">Janeiro</option>
    <option value = "2">Fevereiro</option>
    <option value = "3">Março</option>
    <option value = "4">Abril</option>
    <option value = "5">Maio</option>
    <option value = "6">Junho</option>
    <option value = "7">Julho</option>
    <option value = "8">Agosto</option>
    <option value = "9">Setembro</option>
    <option value = "10">Outubro</option>
    <option value = "11">Novembro</option>
    <option value = "12">Dezembro</option>
    </select>
  </p>
  <span style="text-align: center">
    <button onclick="clickOk()" style="color:#FFFFFF;background:#073763">Ok</button>
    &nbsp;
    <button onclick="google.script.host.close()">Cancelar</button>
  </span>
  <script>
    function clickOk() {
      var Resp1 = document.getElementById('Campo1').value;
      console.log(Resp1)
      google.script.run.nextFunction(Resp1);
    }
  </script>
  </body>
</html>

  • In this modified script, when you run atualReunioes(), a dialog is opened. When you select one of options in the dropdown list and click "ok" button, the number value is returned to nextFunction function. By this, the script works.

  • In your showing script, at google.script.run.switch(Resp1), you might have tried to run switch (result) {}. In this case, the script cannot be run. Please be careful about this.

Tanaike0
Related