Create search box on google Docs

Question

Create search box on google Docs

Asked 6 years, 10 months ago

Viewed 669 times

0

Good,

I have this VBA code and needed to build a Javascript for Google Docs that do the same thing. It is possible to convert VBA to Javascript?

Thank you.

Private Sub CommandButton1_Click()
Dim F1 As Worksheet
Dim intervalo As Range
Dim LastRow As Long
'Declara a Planilha pelo nome
Set F1 = ThisWorkbook.Worksheets("Folha1")
'Última Linha
LastRow = F1.Cells(F1.Rows.Count, "A").End(xlUp).Row

Set intervalo = F1.Range("A2:A" & LastRow)
'Caso não encontre TextBox2
If TextBox2 = "" Then
    MsgBox "Insira o nº da ficha a consultar."
'Se os campos de TextBox estiverem preenchidos
ElseIf TextBox2 > "" And TextBox1 > "" Then
'Realiza a procura
    With intervalo
        Set cellFound = .Find(TextBox1, LookIn:=xlValues)
        If Not cellFound Is Nothing Then
            FirstAddress = cellFound.Address
            Do
                'Realiza Ação após encontrar, faz offset de uma coluna para direita e segue o hyperlink. Então após encontrar na Coluna A, segue o hyperlink da coluna B.
                cellFound.Offset(0, 1).Hyperlinks(1).Follow
                Set cellFound = .FindNext(cellFound)
            Loop While Not cellFound Is Nothing And cellFound.Address <> FirstAddress
        End If
    End With
End If
TextBox2.SetFocus

End Sub

Private Sub TextBox1_Change()
    Dim intervalo As Range
    Dim texto As String
    Dim codigo As Integer
    Dim pesquisa
    Dim mensagem
    Dim F1 As Worksheet
    Set F1 = ThisWorkbook.Worksheets("Folha1")
    LastRow = F1.Cells(F1.Rows.Count, "A").End(xlUp).Row
   On Error Resume Next
    codigo = TextBox1.Text

    Set intervalo = F1.Range("A2:B" & LastRow)
    pesquisa = Application.WorksheetFunction.VLookup(codigo, intervalo, 2, False)

    TextBox2.Text = pesquisa


End Sub

'Private Sub UserForm_Terminate()
'ActiveWorkbook.Close Savechanges:=False
'End Sub

1 answer

Browser other questions tagged javascript google html-select

You are not signed in. Login or sign up in order to post.

by FilipeTD • **118** points · Answer 1 · 2018-03-16T17:58:05+00:00

I made a solution in App Script that would be a conversion of your VBA. I hope it is what you need.

The working file link is this: https://docs.google.com/document/d/1Kl7v3NnIBxOB_V6sIMiIyzFRN-CGsizq8_iZEtQVeBM/edit?usp=sharing

Two files were created in the App Script, being them:

Code.Gs

function onOpen(e) {
   DocumentApp.getUi()
       .createMenu('Pesquisar')
       .addItem('Documentos', 'showSidebar')
       .addToUi();
}

function onInstall(e) {
  onOpen(e);
}

function showSidebar() {
  var ui = HtmlService.createHtmlOutputFromFile('Sidebar')
      .setTitle('Pesquisar Documentos');
  DocumentApp.getUi().showSidebar(ui);
}

function BuscarCodigo(codigo) {

  if(codigo != null) {
    DocumentApp.getUi().alert(codigo);
  }

  var table = DocumentApp.getActiveDocument().getBody().getTables();

  var codigos = [];
  var links = [];
  var x = 1;
  while(x < table[0].getNumRows()) {
    codigos.push(table[0].getCell(x, 0).getText());
    links.push(table[0].getCell(x, 1).getText());
    x++;
  }

  var y = 0;
  var link = "";
  while(y < codigos.length) {
    if(codigo == codigos[y]) {
      DocumentApp.getUi().alert(links[y]);
      link = links[y];
      break;
    }
    y++;
  }

  AbrirLink(link);

}

function AbrirLink(link) {
  var ui = HtmlService.createHtmlOutput(
  "<!DOCTYPE html> " +
  "<html> " +
  "<head> " +    
    "<script type='text/javascript'> " +    
    "function AbrirLink(){ " +
    " var popup = window.open('http://" + link + "'); " +
    " google.script.run.showSidebar(); " +     
    "} " +  
    "</script> " +  
  "</head> " +
  "<body onload='AbrirLink()'> " +
  "</body> " +  
  "</html> "  
  );
  DocumentApp.getUi().showSidebar(ui);
}

Sidebar.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
  </head>
  <body onload="document.formulario.codigo.focus();">

    <style>
    .hidden {
      display: none;
    }
    </style>

    <br />

    <form role="form" id="formulario" name="formulario" align="center">

      <div class="form-group col-xs-12">
        <label class="sr-only" for="codigo">Código</label>
        <input type="text" class="form-control" name="codigo" id="codigo" placeholder="Insira o código do Documento">
      </div>

      <button class="btn btn-success hidden" align="center" id="pesquisaroculto">
        Pesquisar
      </button>

      <button class="btn btn-success" align="center" id="pesquisar">
        Pesquisar
      </button>

    </form>

    <script src="https://code.jquery.com/jquery.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script> 
  </body>

  <script type="text/javascript">

    $(function() {
        $('#pesquisar').click(runPesquisa);
        $('#pesquisaroculto').click(runPesquisaOculto);
    });

    function runPesquisaOculto() {

      $("#pesquisaroculto").prop("disabled",true);
      $( "#pesquisar" ).removeClass("hidden");
      $("#pesquisar").prop("disabled",false);
      $( this ).addClass("hidden");

      var codigo = $('input[name=codigo]').val();

      google.script.run.BuscarCodigo(codigo);

    }

    function runPesquisa() {

      $("#pesquisar").prop("disabled",true);
      $( "#pesquisaroculto" ).removeClass("hidden");
      $("#pesquisaroculto").prop("disabled",false);
      $( this ).addClass("hidden");

      var codigo = $('input[name=codigo]').val();

      google.script.run.BuscarCodigo(codigo);

    }

  </script>

</html>

It is only the logic without validation of blank fields that can be done in html normally. All new values inserted in table will work.

There is no way to do a search field inside Google Docs.

What I did was a Document Search menu in the menu called "Search > Documents" that opens a sidebar with a field to enter the document code and a button to Search the document.

By clicking on this button the link of the searched document opens in a new tab.