google-app-enginegoogle-sheetsgoogle-sheets-macros

TypeError: Cannot read property '4' of undefined at Line 100

When executing function, it invoques the other function ( registrarOcupacion() invoques to procesarHojaOcup(hojaOrigen))then, when processing the macro I get an error on line 100:

Line 100: matriz.push(aux.concat(semana,fechassemana,fechassemana,fechas[semana][6],datos[f][c] * 8,'','Ocupación'))

Message Error: TypeError: Cannot read property '4' of undefined (line 100, file"macroPlaneacion")Close

Before it works pretty well, but now I try to scope data source to other bigger sheet and doesn't work at concat fechassemana,fechassemana,fechas[semana][6]

Logger registery:

10 jun. 2020 13:55:44 Información 27.0 10 jun. 2020 13:55:45 Información null 10 jun. 2020 13:55:45 Error TypeError: Cannot read property '4' of undefined at procesarHojaOcup(macroPlaneacion:101:55) at registrarOcupacion(macroPlaneacion:29:7)

My maternal language is Spanish, this is why sometimes you will find comments in spanish

SOURCE CODE

function registrarOcupacion(){
  let archivoDestino = SpreadsheetApp.getActive();
  let hojaDestino = archivoDestino.getSheetByName('BD');
  var hojaOrigen = SpreadsheetApp.getActive();

  let archivoOrigen = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/187Mvtj_ExSQ9nil_NbwNmSTcjZ_X1i6GfXcOMMMnn7Y/edit#gid=858356230');

  //crear fechas
  fechas = archivoDestino.getSheetByName('Calendario').getDataRange().getValues()
  personas = archivoDestino.getSheetByName('Personas').getDataRange().getValues()

  let hojasArchivoOrigen = archivoOrigen.getSheets()
  for(let h of hojasArchivoOrigen){
    let nombre = h.getName()
    if(nombre.substr(0,1) != '_'){
      let hojaOrigen = h
      procesarHojaOcup(hojaOrigen)
    }
  }

  let rangoDestino = hojaDestino.getRange(2, 1, matriz.length, matriz[0].length)
  rangoDestino.setValues(matriz)
  Browser.msgBox('Fin del proceso')
}


function procesarHojaOcup(hojaOrigen) {

  let datos = hojaOrigen.getDataRange().getValues()
  for(let f = 3, l = datos.length; f < l; f++){
    if(datos[f][0] != '' && datos[f][0] != 'Totales'){
      let persona = personas.filter(p => p[0] == datos[f][0])
      let aux = [
        hojaOrigen.getName()
      ]
      aux = aux.concat(persona[0])
      aux = aux.concat(
        datos[f][1],
        datos[f][2],
        datos[f][3]
      )
      for(let c = 21; c <= 57; c++){
        if(datos[f][c] !=''){
          let semana = datos[2][c]
          matriz.push(aux.concat(semana,fechas[semana][4],fechas[semana][5],fechas[semana][6],datos[f][c] * 8,'','Ocupación'))
        }
      }
    }
  }
  SpreadsheetApp.getActive().toast('', hojaOrigen.getName())
}

Google Sheet Data:

Calendar: enter image description here

People is: enter image description here

And the data extracted: enter image description here

Expected results:

enter image description here

The macro do as well as expected the job, but I don't know what I did

Resolvin that, in hours bussy I need this.

enter image description here

Solution

  • Your problem originates from you query your data

    What to do?