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.
In your situation, how about using IMPORTRANGE? When your script is modified, it becomes as follows.
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);
}
}