I'm keep getting REF error in all the cells that I paste the data to after writing the formula-CodePudding

So I wrote this code that will paste the first 7 columns from sheet2(sourcing) to sheet1(sheet1) and I also wrote a formula to every cell so if the code in the sourcing sheet changes then the one in sheet1 would change, but when I did that I got #REF! in every cell so I went to file --> settings and changed the calculations --> Iterative calculation from Off to On and the error was gone but i got 0 in every cell which isnt the right value of the cell.

Here is the code.

function moveCode() {
  try {
   const ss1 = SpreadsheetApp.getActive();
   const ss2 = SpreadsheetApp.openById("1A3tJiIEkDP_5R6mEY25IeAsO2XV_oveJwnUovxRd-1U");//or url whatever
   const ssh = ss1.getSheetByName('Sheet1');
   const dsh = ss2.getSheetByName('Sourcing');
   const lastRow = dsh.getLastRow();
   if (lastRow < 2) return;

   var srange = dsh.getDataRange();
   var formulas = [];
   var i=0;
   var j= 0;
   var row = null;
    // srange.getNumRows()-1 because skip the first row
   for( i=0; i<srange.getNumRows()-1; i   ) {
      row = [];
      for( j=1; j<8; j   ) {
        row.push("=Sheet1!" String.fromCharCode(64 j) (i 2).toString());  // A = 65
      }
      formulas.push(row);
    }
    ssh.getRange(2,1,formulas.length,formulas[0].length).setValues(formulas);
  }
  catch(err) {
    Logger.log(err);
  }
}

I think there is something wrong with the formula but I'm not sure whats wrong.

If you need more explanation please let me know.

Thank you.

CodePudding user response：

In your situation, how about using IMPORTRANGE? When your script is modified, it becomes as follows.

Modified script:

function moveCode() {
  try {
    const ss1 = SpreadsheetApp.getActive();
    const ss2 = SpreadsheetApp.openById("1A3tJiIEkDP_5R6mEY25IeAsO2XV_oveJwnUovxRd-1U");//or url whatever
    const ssh = ss1.getSheetByName('Sheet1');
    const dsh = ss2.getSheetByName('Sourcing');
    const lastRow = dsh.getLastRow();
    if (lastRow < 2) return;
    ssh.getRange(2, 1).setFormula(`=IMPORTRANGE("${ss2.getUrl()}","'Sourcing'!A2:G${lastRow}")`);
  }
  catch (err) {
    Logger.log(err);
  }
}

After the formula was put in the cell, please accept for loading the values from the Spreadsheet.

Modified script:

References: