2

How do I get a substring from a cell in Google Apps Script?

I'm trying to retrieve some data from a website. I've already got the whole HTML code and put it into a cell (thou it could be better put into a String variable)

I'm stuck in a simpler task: How do I get the part of that cell/string I'm interested in, (since I know it is always located in the same position/offset)

I tried string.substring(offset, length) but it doesn't work.

How can I do that? Help appreciated.

1
  • 1
    More context (code) would help. Also, perhaps Stack Overflow would be a more suitable place for this question, since it is a programming question. There's a google-apps-script tag there too. Sep 11, 2017 at 17:55

2 Answers 2

3

Sorry. My mistake.

The right usage of substring method is:

string.substring(starting-offset, ending-offset)

Here's the whole code. Already ready. Improvements may come later.

function Agrega_Valores_Actualizados() {
      var Libro = SpreadsheetApp.getActiveSpreadsheet();
      var Hoja_0 = Libro.getSheets()[0];

      Hora = Utilities.formatDate(new Date(), 'GMT-4', 'HH');

      if (Hora > 17) {
        Fecha_B3_Simple = Hoja_0.getRange("B3").getDisplayValue(); 
        Hoy_Form_Simple = Utilities.formatDate(new Date(), 'GMT-4', 'dd-MM-yy');
        if (Fecha_B3_Simple != Hoy_Form_Simple) {
          //Inserta línea entre 3 y 4
          Hoja_0.insertRowAfter(3);

          //Copia línea 3 a la 4
          Linea3 = Hoja_0.getRange("B3:F3");
          Linea4 = Hoja_0.getRange("B4:F4");
          Linea3.copyTo(Linea4);
          F3 = Hoja_0.getRange("F3"); F3.setValue("=E3-E4");
          F4 = Hoja_0.getRange("F4"); F4.setValue("=E4-E5");

          //Borra contenido de celdas C3 y D3
          Valores_A_y_B_Linea3 = Hoja_0.getRange("C3:D3"); Valores_A_y_B_Linea3.clearContent();

          //Coloca fecha actual en celda B3
          Fecha_Linea3 = Hoja_0.getRange("B3"); Fecha_Linea3.setValue(Hoy_Form_Simple)

          //Busca Valor de Acciones en Página de Índice Bursátil
          var Contenido_URL = UrlFetchApp.fetch('http://www.bancaynegocios.com/indice-bursatil-caracas/');
          var HTML = Contenido_URL.getContentText();

          P1 = HTML.search("Mercantil Servicios Financieros Clase A");
          C3 = Hoja_0.getRange("C3"); C3.setValue(HTML.substring(P1+110,P1+110+8));

          P2 = HTML.search("Mercantil Servicios Financieros Clase B");
          D3 = Hoja_0.getRange("D3"); D3.setValue(HTML.substring(P2+110,P2+110+8));
        }
      }
 }
3

The string.substring(offset, length) works for me.

Let me show you:

var alfabeto = "ABCDEFGHIJKLMNOPQRSTUVWXTZabcdefghiklmnopqrstuvwxyz";
var letra = "";
letra = alfabeto.substring(0,1);

Result: letra = A

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.