2

I am trying to convert calendar dates to fiscal dates. I've been successfully able to do all that. My problem is trying to write the fiscal date values back into the spreadsheet. Here is the code:

var dates = [];
var fyear = [];
var fiscalDates = [];
for (var x=1; x < getLastRows;x++) {
    dates[x] = getFiscalValues[x][9];
    fyear[x] = getFiscalValues[x][37];
    fiscalDates[x] = [];

    if (fyear[x] == "") {

      var fiscalRowNumber = fiscalUpdate(dates[x]);

      var openFiscalSS = SpreadsheetApp.openById(fiscalRowNumber[0]);
      var openFiscalYear = openFiscalSS.getSheetByName(fiscalRowNumber[2]);
      var fiscalCols = openFiscalYear.getDataRange().getLastColumn();
      var fiscalValues = openFiscalYear.getRange(fiscalRowNumber[1],1,1,fiscalCols).getValues(); 
      // this array now contains all the values required for the current fiscal

      fiscalDates[x][0] = fiscalRowNumber[2];
      fiscalDates[x][1] = fiscalValues[0][0];
      fiscalDates[x][2] = fiscalValues[0][1];
      fiscalDates[x][3] = fiscalValues[0][2];
      fiscalDates[x][4] = fiscalValues[0][3];

    } // end of if statement that checks if Fiscal year is blank


  } // end of the for loop that updates the fiscal dates for each row
var openTM = SpreadsheetApp.openById(tmFileString);
var openTmResponse = openTM.getSheetByName(tmResponses);
openTmResponse.getRange(2,40,getLastRows,5).setValues([fiscalDates]);

The error I am getting when I attempt to write back the values to the spreadsheet is "Incorrect range height, was 1 but should be 132". I checked the values of getLastRows and the length of fiscalDates array - both numbers are the same.

I can't seem to figure out what I am doing wrong with the last statement.

  • Now I get this error "Cannot convert Array to Object[][]." after removing the square brackets. – catry Dec 21 '15 at 17:47
  • One problem is that we don't know what you assigned to fiscalDates[x][0]. It comes from some fiscalUpdate thing that's not here. Try putting Logger.log(fiscalDates) before the last line, to see what's in your array. – user79865 Dec 21 '15 at 19:48
  • That function just returns 2 values - the fileID where the fiscal values are stored and the row number whose value is between the from and end dates for each week. The actual array for fiscalDates[x][y] is populated in the script above. I have two declarations: fiscalDates = [] and fiscalDates[x] = []. Maybe thats what the problem is. Logger.log(fiscalDates.length+'--'+fiscalDates[130].length) gave an output of 132 and 5 respectively. Just logging the values of fiscalDates shows the array [... [2016.0, 3.0, 9.0, December, 38.0]...] – catry Dec 21 '15 at 20:54
  • Okay, my first comment (now deleted) was correct after all: you should not have brackers around fiscalArray in the last line. Here is a minimal example, which your post sadly lacks: as written, it executes correctly but wrapping fiscalDates in [] will get you "incorrect range height". I don't get where the "cannot convert array" error comes from. Try reducing the example to something others can reproduce in their spreadsheet. – user79865 Dec 21 '15 at 21:06
  • Found the issue. Thanks for pointing me in the direction. One of the data sets in the column returned a NULL which messed up the array size. I fixed it and life is perfect now. – catry Dec 23 '15 at 2:37
1

The particular issue was sorted out in comments, but to have a generally useful answer here I include a helper function for checking whether an array is rectangular. If .setValues(something) throws an error, put checkArray(something) before that line. The function checks whether the argument is a double array in which all rows have equal length. Its output is found in the log, viewable by pressing Ctrl-Enter after running a script.

function checkArray(ar) {
  if (ar.constructor !== Array) {
    Logger.log('Not an array'); return;
  }  
  var height = ar.length; 
  if (ar[0].constructor !== Array) {
    Logger.log('Not a double array'); return;
  }
  var width = ar[0].length;
  for (var i = 0; i < height; i++) {
    if (ar[i].length !== width) {
      Logger.log('Not rectangular: row '+(i+1)+' has length '+ar[i].length+' instead of '+width); 
      return;
    }
  }
  Logger.log('Rectangular of height '+height+' and width '+width);
}

It would be great if Apps Script itself logged more detailed error messages of this sort, instead of just saying "Cannot convert Array to Object[][]".

0

The error you are getting because the selected range where you are trying to write the dates are not of matching size.

openTmResponse.getRange(2,40,numberOfRows,numberOfColumns).setValues([fiscalDates]);

Now you can see that the range size is numberOfRows x numberOfColumns. That mean it expects a 2D array of size numberOfRows x numberOfColumns. As per your code, the array size you are trying to write is 1x fiscalDates.length

  • Thanks Waqar. But isn't that what I am doing? I am saying start at row number 2, column 40 and write the range - number of Rows equal to "getLastRows" (in this case its 132 rows) and number of columns equal to 5. – catry Dec 21 '15 at 19:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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