Google App Script: Pasar datos desde un spreadsheet a otro por programación

En este caso vamos a trasladar datos desde varios spreadsheets de google drive hasta otro que recepcionará todos los datos. Lo primero que hay que hacer es definir variables para cada spreadshhet y sus correspondientes hojas si hay más de una.

Esta primera variable almacena la spreasheet abierta que es donde recopilaremos los datos de las

diferentes spreadsheets.

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

Si dentro de nuestra spreadsheet activa tenemos varias hojas, la siguiente variable contendrá la hoja donde queremos que vayan los datos

var sheet_destino = spreadsheet.getSheetByName('Hoja_de_la_spreadsheet_activa');

Utilizamos otra variable para almacenar la hoja de una de las la spreadsheets que leeremos

var sheet_origen = spreadsheet.getSheetByName('Hoja_de_la_spreadsheet_leida');

variable global que contiene la última fila con datos

var ultima_destino;

Esta variable contendrá la última fila escrita

ultima_destino = sheet_destino.getLastRow();

nombre de la spreadsheet de destino

var destinySheetName = "Resumen";

Ahora para cada spreadsheet leída necesitaremos su id, si abrimos la spreadshhet el ID aparece en su URL entre  /d  y /edit

https://docs.google.com/spreadsheets/d/1oTnGNCUdxQ-9UTkiCSmhBFM0iQFcDupk4Yu-TVNP5CM/edit#gid=638007461

en este ejemplo, 1oTnGNCUdxQ-9UTkiCSmhBFM0iQFcDupk4Yu-TVNP5CM  es el ID buscado, asignamos dicho ID a una variable por cada spreadsheet que deseamos leer

var ss = SpreadsheetApp.openById("12XGOQFy1nYoXRBJEBpCv5HW1Xc0ClcPsA2DvCB70mvI");   // Documento 1

var ssh =  SpreadsheetApp.openById("1w8gmuP9Gzots2-ALNjINYOoue8pS_EOWhgbI2c4fbW0");  // Documento 2

var ssph =  SpreadsheetApp.openById("1W5iiHtO21Bpu4HrBqgo3gbX3rFB-O-zHlSXw7TA5RVo");  // Documento 3

Ahora definimos una función que llame a las diferentes funciones de lectura de cada spreadsheet

function Importa_Docs()

{

  

    //Importa documento 1;

    ultima_destino = sheet_destino.getLastRow();  //obtiene la última fila con datos en el destino

    Importa_Documento1();

    ultima_destino = sheet_destino.getLastRow();  //obtiene la última fila con datos en el destino

    Importa_Documento2();

    ultima_destino = sheet_destino.getLastRow();  //obtiene la última fila con datos en el destino

    Importa_documento3();

}

// Esta función importa datos desde el documento1

function Importa_Documento1()

{

  //obtiene la hoja a leer

  var sheet = ss.getSheetByName('Hoja_de_Documento1');

definimos las variables locales que irán almacenando los datos de cada columna

  var range;

  var Col1

  var Col2;

  var Col3;

  var Valor_Calculado;

  var ultima= sheet.getLastRow();   //variable global que contiene la última fila con datos de la hoja de origen de los datos

  var i, j;

 

  j= 2  // leemos desde la fila 2 pues la primera fila contiene las cabeceras

Leemos  los datos de la hoja y pestaña de origen, este bucle recorre todas las filas del documento de destino con i y va leyendo los datos del documento de origen con la variable j para recorrer las filas del documento de origen, en este caso i y j coinciden pero hay casos en los que deben coincidir necesariamente.

for (i=(ultima_destino+1); i < ((ultima+1) + (ultima_destino-1)) ; i++) {

  

      range = sheet.getRange(j,1);  // los números hacen referencia a cada fila y columna de la hoja leida fila

      Col1 = range.getValue();  //asigna el valor leído de una celda a la variable Col1

      range = sheet.getRange(j,2); 

      Col2 = range.getValue(); //asigna el valor leído de una celda a la variable Col2

      range = sheet.getRange(j,6);  //podemos leer cualquier columna que deseemos

      Col3 = range.getValue(); //asigna el valor leído de una celda a la variable Col3

     

      Valor_Calculado = Calcula_Valor("Texto_Fijo", Col1);  // Podemos llamar a una funcion que calcule un valor en funcion del valor leído en el spreadsheet de origen

   

  //escribimos los resultados en la hoja y pestaña de destino

      sheet_destino.getRange(i,1).setValue("Texto Fijo");

      sheet_destino.getRange(i,2).setValue(Col1);

      sheet_destino.getRange(i,3).setValue(Col2);

      sheet_destino.getRange(i,4).setValue(Col2);

      sheet_destino.getRange(i,6).setValue(Valor_Calculado);

     

      j++   // pasa a la siguiente línea del documento de origen

                   

    }

}

Uno de los valores leídos en vez de utilizarlo como dato a mostrar podemos utilizarlo como entrada de una función  que devuelva un valor calculado en función del valor de entrada

function Calcula_Valor(Valor, Col1)

{

   switch (Col1)  {

        case "Valor1":

            return "Salida1";

            break;

        case "Valor2":

            return "Salida2";

            break;

         case "Valor3":

            return Sub_proceso(Col1);

            break;

        case "Valor_n":

            return "Salida_n";

            break;

        default:

            return "Valor_por_defecto";

            break;

    }

}

Los procesos se pueden anidar tanto como deseemos, en este caso desde la función  Calcula_Valor se llama a otro proceso que hemos llamado Sub_Proceso donde podemos tratar de nuevo los datos del spreadsheet como deseemos y complicarlo tanto como queramos.

function Sub_proceso(Valor)

{

     var sheet = sheet_origen;   //toma otra hoja como origen

     var Valor_leido;

     var ultima = sheet.getLastRow();  

    

      for (i=1; i < (ultima +1); i++)

      {

          range = sheet.getRange(i,1); 

          Valor_leido = range.getValue();

          if (Valor_leido == Valor)

          {

            return "Valor_retornado_si_cumple_condición";

            break;

          }

      }

      return "Valor_retornado_si_no_cumple_condición"; 

}

     

Para cada documento podemos definir una función, igual o distinta de  Importa_Documento1, en este caso leeremos el docuento2 de forma similar al domumento1 pero  en este caso vamos a buscar una subcadena dentro de otra.

function Importa_Documento2()

{

  var sheet = ssh.getSheetByName('Para PET');

  var range;

  var ultima= sheet.getLastRow();   //variable global que contiene la última fila con datos

  var Subcadena_dentro1 = "Subcadena1";

  var Subcadena_dentro2 = "Subcadena2";

    var Salida;

   var condicion_escritura_linea;

 

  var i,j;

Recorremos las hojas de origen y destino del mimo modo que en  Importa_Documento1

 

j= 2

  //lee los datos de la hoja y pestaña de origen

   for (i=(ultima_destino+1); i < ((ultima+1) + (ultima_destino-1)) ; i++) {

     

      range = sheet.getRange(j,6); 

      Subcadena_dentro1 = range.getValue();

     

     //solo tengo en cuenta Campos de España, si no es España me lo salto

      if (condicion_escritura_linea == "Condicion1") {

     

          range = sheet.getRange(j,4);

          Subcadena_dentro2 = range.getValue();

    

Esto  es un ejemplo de una función que busca si una cadena está dentro de otra,  Si la función indexOf retorna -1 es encontró la subcadena dentro de la cadena.

  

           // Tratamiento del campo Tipo Peticion Larga

          if(Labels.indexOf(Subcadena_dentro1) > -1) {

                 Salida = "Salida si encuentra Subcadena1";

          }

          else

          {

              if(Labels.indexOf(Subcadena_dentro2) > -1) {

                   Salida = "Salida si encuentra Subcadena2";

              }

              else

              {

                

                    Salida = "Salida si no encuentra ninguna de las dos cadenas";

              }

          }

     

                 

         }  // Fin de if condicion_escritura_linea

     else

     {

        i--  // si se salta el if debe restar 1 al indice de la linea para no dejar líneas en blanco.

        ultima_destino--

     }

      j++  

    }  //  Fin del for de carga

}

Finalmente una tercera función para leer una tercera spreadsheet, podemos definir tantas funciones como spreadsheets diferentes tengamos.

function Importa_Documento3()

{

  //obtiene la hoja a leer

  var sheet = ss.getSheetByName('Hoja_de_Documento3');

definimos las variables locales que irán almacenando los datos de cada columna

var range;

  var Col1

  var Col2;

  var Col3;

  var Valor_Calculado;

  var ultima= sheet.getLastRow();   //variable global que contiene la última fila con datos de la hoja de origen de los datos

  var i, j;

 

  j= 2  // leemos desde la fila 2 pues la primera fila contiene las cabeceras

  //lee los datos de la hoja y pestaña de origen

   for (i=(ultima_destino+1); i < ((ultima+1) + (ultima_destino-1)) ; i++) {

  

      range = sheet.getRange(j,1);  // los números hacen referencia a cada columna de la hoja leida, la j es la fila

      Col1 = range.getValue();

      range = sheet.getRange(j,2); 

      Col2 = range.getValue();

      range = sheet.getRange(j,6);  //podemos lleer cualquier columna que deseemos

      Col2 = range.getValue();

     

      Valor_Calculado = Calcula_Valor("Texto_Fijo", Col1);  // Podemos llamar a una funcion que calcule un valor en funcion del valor leido en el spreadsheet de origen

   

  //escribimos los resultados en la hoja y pestaña de destino

      sheet_destino.getRange(i,2).setValue(Col1);

      sheet_destino.getRange(i,3).setValue(Col2);

      sheet_destino.getRange(i,4).setValue(Col2);

      sheet_destino.getRange(i,6).setValue(Valor_Calculado);

     

      j++   // pasa a la siguiente línea

                   

    }

}

Aquí el programa completo

function Importa_Docs()

{

  

    //Importa spreadsheet 1

    ultima_destino = sheet_destino.getLastRow();  //obtiene la última fila con datos en el destino

    Importa_Documento1();

    //Importa spreadsheet 2

    ultima_destino = sheet_destino.getLastRow();  //obtiene la última fila con datos en el destino

    Importa_Documento2();

    //Importa spreadsheet 3

    ultima_destino = sheet_destino.getLastRow();  //obtiene la última fila con datos en el destino

    Importa_documento3();

}

// Esta funcion importa datos desde el documento1

function Importa_Documento1()

{

  //obtiene la hoja a leer

  var sheet = ss.getSheetByName('Hoja_de_Documento1');

  var range;

  var Col1

  var Col2;

  var Col3;

  var Valor_Calculado;

  var ultima= sheet.getLastRow();   //variable global que contiene la última fila con datos de la hoja de origen de los datos

  var i, j;

 

  j= 2  // leemos desde la fila 2 pues la primera fila contiene las cabeceras

   for (i=(ultima_destino+1); i < ((ultima+1) + (ultima_destino-1)) ; i++) {

  

      range = sheet.getRange(j,1);  // los números hacen referencia a cada fila y columna de la hoja leida fila

      Col1 = range.getValue();  //asigna el valor leído de una celda a la variable Col1

      range = sheet.getRange(j,2); 

      Col2 = range.getValue(); //asigna el valor leído de una celda a la variable Col2

      range = sheet.getRange(j,6);  //podemos leer cualquier columna que deseemos

      Col3 = range.getValue(); //asigna el valor leído de una celda a la variable Col3

     

      Valor_Calculado = Calcula_Valor("Texto_Fijo", Col1);  // Podemos llamar a una funcion que calcule un valor en funcion del valor leído en el spreadsheet de origen

   

  //escribimos los resultados en la hoja y pestaña de destino

      sheet_destino.getRange(i,1).setValue("Texto Fijo");

      sheet_destino.getRange(i,2).setValue(Col1);

      sheet_destino.getRange(i,3).setValue(Col2);

      sheet_destino.getRange(i,4).setValue(Col2);

      sheet_destino.getRange(i,6).setValue(Valor_Calculado);

     

      j++   // pasa a la siguiente línea del documento de origen

                   

    }

}

function Calcula_Valor(Valor, Col1)

{

   switch (Col1)  {

        case "Valor1":

            return "Salida1";

            break;

        case "Valor2":

            return "Salida2";

            break;

         case "Valor3":

            return Sub_proceso(Col1);

            break;

        case "Valor_n":

            return "Salida_n";

            break;

        default:

            return "Valor_por_defecto";

            break;

    }

}

function Sub_proceso(Valor)

{

     var sheet = sheet_origen;   //toma otra hoja como origen

     var Valor_leido;

     var ultima = sheet.getLastRow();  

    

      for (i=1; i < (ultima +1); i++)

      {

          range = sheet.getRange(i,1); 

          Valor_leido = range.getValue();

          if (Valor_leido == Valor)

          {

            return "Valor_retornado_si_cumple_condición";

            break;

          }

      }

      return "Valor_retornado_si_no_cumple_condición"; 

}

     

function Importa_Documento2()

{

  var sheet = ssh.getSheetByName('Para PET');

  var range;

  var ultima= sheet.getLastRow();   //variable global que contiene la última fila con datos

  var Subcadena_dentro1 = "Subcadena1";

  var Subcadena_dentro2 = "Subcadena2";

    var Salida;

   var condicion_escritura_linea;

 

  var i,j;

   j= 2

  //lee los datos de la hoja y pestaña de origen

   for (i=(ultima_destino+1); i < ((ultima+1) + (ultima_destino-1)) ; i++) {

     

      range = sheet.getRange(j,6); 

      Subcadena_dentro1 = range.getValue();

     

     //solo tengo en cuenta Campos de España, si no es España me lo salto

      if (condicion_escritura_linea == "Condicion1") {

     

          range = sheet.getRange(j,4);

          Subcadena_dentro2 = range.getValue();

    

  

          // Tratamiento del campo Tipo Peticion Larga

          if(Labels.indexOf(Subcadena_dentro1) > -1) {

                 Salida = "Salida si encuentra Subcadena1";

          }

          else

          {

              if(Labels.indexOf(Subcadena_dentro2) > -1) {

                   Salida = "Salida si encuentra Subcadena2";

              }

              else

              {

                

                    Salida = "Salida si no encuentra ninguna de las dos cadenas";

              }

          }

     

                 

         }  // Fin de if condicion_escritura_linea

     else

     {

        i--  // si se salta el if debe restar 1 al indice de la linea para no dejar líneas en blanco.

        ultima_destino--

     }

      j++  

    }  //  Fin del for de carga

}

function Importa_Documento3()

{

  //obtiene la hoja a leer

  var sheet = ss.getSheetByName('Hoja_de_Documento3');

  var range;

  var Col1

  var Col2;

  var Col3;

  var Valor_Calculado;

  var ultima= sheet.getLastRow();   //variable global que contiene la última fila con datos de la hoja de origen de los datos

  var i, j;

 

  j= 2  // leemos desde la fila 2 pues la primera fila contiene las cabeceras

  //lee los datos de la hoja y pestaña de origen

   for (i=(ultima_destino+1); i < ((ultima+1) + (ultima_destino-1)) ; i++) {

  

      range = sheet.getRange(j,1);  // los números hacen referencia a cada columna de la hoja leida, la j es la fila

      Col1 = range.getValue();

      range = sheet.getRange(j,2); 

      Col2 = range.getValue();

      range = sheet.getRange(j,6);  //podemos lleer cualquier columna que deseemos

      Col2 = range.getValue();

     

      Valor_Calculado = Calcula_Valor("Texto_Fijo", Col1);  // Podemos llamar a una funcion que calcule un valor en funcion del valor leido en el spreadsheet de origen

   

  //escribimos los resultados en la hoja y pestaña de destino

      sheet_destino.getRange(i,2).setValue(Col1);

      sheet_destino.getRange(i,3).setValue(Col2);

      sheet_destino.getRange(i,4).setValue(Col2);

      sheet_destino.getRange(i,6).setValue(Valor_Calculado);

     

      j++   // pasa a la siguiente línea

                   

    }

}