Google Apps Script not reading a value needing to be copied elsewhere

Question

Let me preface this with the fact that I'm not a developer. I'm an avid ChatGPT user, and the scripts were written by ChatGPT. I can read scripts, but I cannot write them from scratch.

I've built a sheet that connects with other sheets via IMPORTRANGE. I think this is important, but the issue only appears with one value that I need to copy/paste. Let me elaborate.

I have a list of data in AX1:AX37. When I click appropriate button/run the script, it copies all those values into another workbook. This works flawlessly aside from one issue.

The UI recognizes the value in BG7 as 3, but Google Apps Script does not.

Where do I go from here?

First, the script that I'm using:

function ExportDataV2() {
  var sourceSpreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
  var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataToCopy = sourceSheet.getRange('AX1:AX37');
  var destinationSpreadsheetId = "sheetID";
  var destinationSheetName = "Sheet1";

  // Check if there is data in cell D5
  var cellD5 = sourceSheet.getRange('D5').getValue();

  if (cellD5 !== "") {
    var sourceValues = dataToCopy.getValues();
    var transposedValues = transposeArray(sourceValues);

    var destinationSpreadsheet = SpreadsheetApp.openById(destinationSpreadsheetId);
    var destinationSheet = destinationSpreadsheet.getSheetByName(destinationSheetName);
    var lastRow = destinationSheet.getLastRow();
    var targetRange = destinationSheet.getRange(lastRow + 1, 1, transposedValues.length, transposedValues[0].length);
    targetRange.setValues(transposedValues);
  } else {
    // Display warning message
    SpreadsheetApp.getUi().alert('Warning, no value in D5. Submission failed!');
  }
}

// Function to transpose a 2D array
function transposeArray(array) {
  return array[0].map(function (_, columnIndex) {
    return array.map(function (row) {
      return row[columnIndex];
    });
  }`);
}

The troublesome value is AX27, which is a simple reference to BG7. The formula in BG7 is

 =if(X15="",AK14,X15)

BG7 exists because of some calculations that originate from adding together values from IMPORTRANGE formulas.

I wanted to test if Google Apps Script (GAS) recognized the value in BG7, so I did this:

function copyBG7toAA15() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var value = sheet.getRange("BG7").getValue();
  sheet.getRange("AA15").setValue(value);
}

The value it copied was 0, whereas the actual value in BG7 is 3.

I did the same with AK14, whose formula is as such:

=if(and(D5="",X7=""),"",AE12+AF14)

It resulted in the same thing: 0, when the actual value is 3.

AE12 and AF14 are simple SUM formulas, but it gets complicated from here as AF12 and the other formulas include IMPORTRANGE formulas, for instance AF12 looks like this:

=if(and(D5="",X7=""),"",IFERROR(SUM(QUERY({IMPORTRANGE("sheet-url", "SC-Restock!O:O"), IMPORTRANGE("sheet-url", "SC-Restock!F:F"), IMPORTRANGE("sheet-url", "SC-Restock!G:G")}, "SELECT Col1, Col2, Col3 WHERE Col2 = '"&IF(X7="", D5, X7)&"' AND Col3 = 'New'")), 0))

I wondered if there was an issue with Google Sheets in general, so I wrote a simple formula to check BG7:

=if(BG7=3,"yes","no")

The result was "yes".

So... the UI recognizes the value in BG7 as 3, but Google Apps Script does not.

Where do I go from here?

Answer 1

I finally solved the issue.

I have to admit I'm not 100% sure what the issue was, however, let me explain what I did.

Initially, I had to do a massive rewrite of my sheet, including adding some columns, which moved some merged cells. This appears to have broken the GAS' ability to read the merged cell's data. Once I unmerged and merged those cells again, the script seems to read the data appropriately and move it over to the desired sheet.

Should you ever have an issue where a script stops working after adding columns/rows, check if you have merged cells, as that is what appears to being the issue here.