Range.SetValues() does not insert data on one sheet, on the other works. What is the reason?-CodePudding

I have a GoogleSheet with basically two sheets, which are very similar in terms of data collected. I need to calculate same values for both sheets, but source data is in different columns. Therefore I created three files in AppsScript:

Common.gs - with common function definitions
sheet1.gs
sheet2.gs - both sheet1 and sheet2 have only definitions of proper ranges in particular columns and one function to run script, which essentially calls functions defined in Common.gs, like so in sheet1.gs:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet1")
var createdColumn = sheet.getRange("E2:E").getValues()
var ackColumn = sheet.getRange("G2:G").getValues()
var resColumn = sheet.getRange("I2:I").getValues()
var timeToAckColumn = sheet.getRange(2,14,ackColumn.length,1)
var timeToResColumn = sheet.getRange(2,15,resColumn.length,1)
var yearAndWeekRange = sheet.getRange(2,16,createdColumn.length,2)


function calculateMetricsSheet1() {
  calculateTimeDiff(createdColumn, ackColumn, timeToAckColumn)
  calculateTimeDiff(ackColumn, resColumn, timeToResColumn)
  calculateWeek(createdColumn, yearAndWeekRange)
}

example function implementation (they are basically very similar with minor differences):

function calculateWeek(createdColumn, yearAndWeekRange) {
  var arrData = []
  for(var i=0;i<createdColumn.length;i  ) {
    if(createdColumn[i][0].toString()=="") {
      arrData.push(["",""])
      continue
    }
    var createdDate = new Date(createdColumn[i][0])
    var year = createdDate.getFullYear()
    var week = Utilities.formatDate(createdDate, "GMT 1", "w")
    arrData.push([year, week])
  }
  yearAndWeekRange.setValues(arrData)
}

the sheet2.gs is basically different column definitions, the functions called within calculateMetricsSheet2() are the same.

So what is the problem?

The script works perfectly fine for sheet2.gs, but for sheet1.gs it does collect proper data, calculates proper data, but the data does not appear in proper columns after Range.setValues() call.

No exceptions or errors appear in the console.

Documentation does not provide any kind of information what could be the problem.

I have really ran out of ideas what could be the cause of the issue.

Does anyone have any idea what is going on?

edit: It may be useful to put emphasis on the fact that each script runs function calling 3 other functions -> all of them end with Range.setValues({values}). And for one sheet all of them work, and for the other - none. That's the reason I assume there is something wrong with the sheet itself, maybe some permissions/protection? But I couldn't find anything :(

edit2: I modified my code to iterate through the sheet 10 rows at a time, because I thought maybe when I get a whole column, something bad happens with data and breaks setValues() function. Unfortunately - even if my code iterated 1 row at a time, it still did not work on sheet1, but worked on sheet2. So not a data problem.

CodePudding user response：

The code you show always puts values in yearAndWeekRange which is always in the 'sheet1' sheet. To put the data into another sheet, you need to change the target range appropriately.

The dimensions of the range must match the dimensions of the array you put there. Use this pattern:

  yearAndWeekRange.offset(0, 0, arrData.length, arrData[0].length).setValues(arrData);

CodePudding user response：

I found out what is the problem.

Two scripts were pretty identical, even with naming of variables - ie ackColumn, resColumn etc. Those were stored as a global variables, so even if I was running script1.gs, it used global variables from script2.gs, effectively writing proper data to wrong sheet.

separating global variables names fixed the issue.

Perhaps a rookie mistake, but I missed the fact, that if I have a variable defined outside any function, it becomes global and could be overwritten from other file