How to get some data from a spread sheet to another

user_15655973 0
Viewed 66

Im trying to bring the first 7 column from a spreadsheet to another spreadsheet using google app script but I honestly searched a lot and didn't find a way to do so.

function MoveCode(){
      var ss1 = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1zU__ccPIMst54whmyrbmRnDRRjOtQBFPzXhw6NsFqpU/edit#gid=432949714");//369
      var sheet1 = ss1.getSheetByName("Sourcing");
      var wpLink1 = sheet1.getRange("A2:G").getValues();
      var ssData1 = SpreadsheetApp.openByUrl("This-Spread-sheet");
      var sheetPrime1 = ssData1.getSheetByName("Sheet1");
      var data = sheet1.getRange("A2:G").getValues();
      sheetPrime1.
}

I want to move the code in a way where if i update the first one it would be updated in the second one.

If you need more explanation please let me know. Thank you.

google-apps-script google-sheets javascript
2 Answers

Move first seven columns

function MoveCode(){
  const ss1 = SpreadsheetApp.getActive();
  const ss2 = SpreadsheetApp.openById("id");//or url whatever
  const ssh = ss1.getSheetByName('Sourcing');
  const dsh = ss2.getSheetByName('Sheet1');
  const vs = ssh.getRange(2,1,ssh.getLastRow() - 1,7).getValues();
  dsh.getRange(2,1,vs.length,vs[0].length).setValues(vs);
}
user_72150910

Expanding on Coopers example. I think what you want is everytime you change a value in sheet1 it will automatically update in sheet2. You could either use an onEdit limited to the first 7 columns of sheet1 to copy the value to sheet2. Or here is script that creates a forumla in each cell of sheet2 to the cell in sheet1. But you really only need to run this once unless you change the size of either spreadsheet.

function moveCode() {
  try {
    const ss1 = SpreadsheetApp.getActive();
    //const ss2 = SpreadsheetApp.openById("id");//or url whatever
    const ssh = ss1.getSheetByName('Sheet1');  // source
    const dsh = ss2.getSheetByName('Sheet2');  // destination
    var srange = ssh.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);
    }
    dsh.getRange(2,1,formulas.length,formulas[0].length).setValues(formulas);
  }
  catch(err) {
    Logger.log(err);
  }
}
user_36567390
Related