Pasar datos desde un spreadsheet de google drive a otro spreadsheet

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.

Aquí se explica cómo escribir código fuente dentro de los documentos de googledrive.

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 funcion 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

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

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

var ultima_destino;

ultima_destino = sheet_destino.getLastRow();

var destinySheetName = "Resumen";

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

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

                   

    }

}