I'm currently trying to iterate thru an array using array.forEach to check whether a value is in another array (to then do other stuff).

However, i keep getting the error TypeError: Cannot read property '4' of undefined when going thru it, and i cant understand why, especially cause ive checked outside the foreach loop and the array has the data i need inside.

function sheetIO() {
  let exportSpreadSheet = SpreadsheetApp.openById("1o-x6qOSydD6dQmMEAvr78WN-dDxYVa6hvG7X3vdg7-o");
  let exportSheetDummy = exportSpreadSheet.getSheetByName('NUEVOS INGRESOS Y EXPEDIENTES EN TRAMITE');
  //cargamos registro de causas al script
  let numberOfRows = exportSheetDummy.getLastRow();
  let numberOfColumns = exportSheetDummy.getMaxColumns();
  //nos fijamos cuantas columnas y filas tiene el reg de causas
  let exportSheet = exportSpreadSheet.getSheetByName('NUEVOS INGRESOS Y EXPEDIENTES EN TRAMITE').getSheetValues(2,1,numberOfRows,numberOfColumns);
  //metemos los valores de reg_casos en una variable
  let caseNumberColumn = exportSpreadSheet.getSheetByName('NUEVOS INGRESOS Y EXPEDIENTES EN TRAMITE').getSheetValues(2,6,numberOfRows,1);  //metemos solo la columna de nro de causa en una variable
  let caseMateriaColumn = exportSpreadSheet.getSheetByName('NUEVOS INGRESOS Y EXPEDIENTES EN TRAMITE').getSheetValues(2,9,numberOfRows,3); 
  let importSpreadSheet = SpreadsheetApp.openById("1QF7Jfkdb5aj9dyTanXawleUVGqbeThvT4sY0SRAmSNM");
  let importSheet = importSpreadSheet.getSheetByName('set_de_datos_unificado');
  let numberOfImportRows = importSheet.getLastRow();
  let numberOfImportColumns = importSheet.getMaxColumns();
  let importSheetRange = importSheet.getSheetValues(2,1,numberOfImportRows,numberOfImportColumns);
  let index = 0;

  caseNumberColumn.forEach((item, index)=>{
    if (caseNumberColumn[index] != ""){
      let stringToReplace = caseNumberColumn[index].toString();
      stringToReplace = stringToReplace.replace("/","_").replace(" ","").replace(/-\d/, "");
      //console.log(stringToReplace);
      caseNumberColumn[index] = stringToReplace;
    }
  }); // formateo correctamente los strings para poder comparar con los de el set > falta chequear si estan o no en el set de datos, despues ya esta, es solo copiar y chau.
importSheetRange.forEach((i) => {
   if (caseNumberColumn.includes(importSheetRange[i][4])){

    }
   else if (importSheetRange[i][3] == "" || importSheetRange[i][5] == "" || importSheetRange[i][6] == "")
    {
      console.log(importSheetRange[i][4]);
    }
 });

  //console.log(numberOfColumns);
  //importSheetRange.setValues(exportSheet);
}

/* Que falta hacer?
      Pensar como chequear que cosas ya estan: Creo que no es mala idea agarrar solo los ids y chequear si ya estan o no 
      Si ya estan, no copiamos, si estan, copiamos.

      Slice-ear los arrays para poder cada coso en su lugar
      Filtrar las cosas si es que hace falta cambiar el formato
*/

I'd appreciate any details on why this aint working!

CodePudding user response：

Try it this way:

function sheetIO() {
  let ss = SpreadsheetApp.openById("1o-x6qOSydD6dQmMEAvr78WN-dDxYVa6hvG7X3vdg7-o");
  let sh = ss.getSheetByName('NUEVOS INGRESOS Y EXPEDIENTES EN TRAMITE');
  let xsh = ss.getSheetByName('NUEVOS INGRESOS Y EXPEDIENTES EN TRAMITE').getSheetValues(2,1,sh.getLastRow() - 1,sh.getLastColumn());
  let caseNumberColumn = ss.getSheetByName('NUEVOS INGRESOS Y EXPEDIENTES EN TRAMITE').getSheetValues(2,6,sh.getLastRow() -1,1).g;  
  let caseMateriaColumn = ss.getSheetByName('NUEVOS INGRESOS Y EXPEDIENTES EN TRAMITE').getSheetValues(2,9,sh.getLastRow() - 1,3); 
  let iss = SpreadsheetApp.openById("1QF7Jfkdb5aj9dyTanXawleUVGqbeThvT4sY0SRAmSNM");
  let ish = iss.getSheetByName('set_de_datos_unificado');
  let importSheetRange = ish.getSheetValues(2,1,ish.getLastRow() - 1,ish.getLastColumn());
  caseNumberColumn.getDisplayValues().flat().forEach((item, index)=>{
    if (item != ""){
      caseNumberColumn[index] = item.replace("/","_").replace(" ","").replace(/-\d/, "");
    }
  }); 
importSheetRange.getValues().forEach(i => {
   if (caseNumberColumn.getDisplayValues().flat().includes(i[4])){
    }
   else if (i[3] == "" || i[5] == "" || i[6] == "")
    {
      console.log(i[4]);
    }
 });
}

Not sure if this will work. You had a lot of errors in it. You cannot iterate through ranges. You must interate through value arrays supplied by methods like getValues() or getDisplayValues();

CodePudding user response：

Try to change the line:

let importSheetRange = importSheet.getSheetValues(2,1,numberOfImportRows,numberOfImportColumns);

this way:

let importSheetRange = importSheet.getSheetValues(2,1,numberOfImportRows-1,numberOfImportColumns);

If the error will disappear then I try to explain what's going on out there.