Copy data without space between them in another spreadsheet with apps script-CodePudding

I want to copy the data from one spreadsheet and put it in another spreadsheet with no space between the rows, that is, to continue copying them. I tried to make a script using Apps Script, but that code copies my data starting with the last line. I'm just getting started and I'm trying to learn as much as I can about the application script

My code : I managed to make this code, but it writes to me cell by cell and due to too much data it exceeds the execution time of the script and stops. [1]: https://i.stack.imgur.com/vhlsT.png

I have attached a link to the spreadsheets to better understand. https://docs.google.com/spreadsheets/d/1lE8bTAZ1qtxZhHf_tCcDL4Fb9nQNRiDicc7GQyDiXmA/edit?usp=sharing

My code :

function myFunction (){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = SpreadsheetApp.openById("1lE8bTAZ1qtxZhHf_tCcDL4Fb9nQNRiDicc7GQyDiXmA")
var source_sheet= sheet.getSheetByName("Date importate");

  //var source_sheet = ss.getSheetByName("Date importate");
  var target = SpreadsheetApp.openById("1lE8bTAZ1qtxZhHf_tCcDL4Fb9nQNRiDicc7GQyDiXmA");
  var target_sheet = target.getSheetByName("Date prelucrate");
  var source_range = source_sheet.getRange("A3:BR100");
  var target_range = target_sheet.getRange("A2:BR1000")

  var lastRow_indexofsource = source_sheet.getLastRow();
  var lastRow_indexoftarget = target_sheet.getLastRow();

  var targetslno = lastRow_indexoftarget 1;
  Logger.log(targetslno);

 for(var i=2; i<=lastRow_indexofsource;  i)
  {
  for(var j=1; j<=70;  j)
 {
  var value =source_range.getCell(i,j).getValue();
    target_range.getCell(targetslno,1).setValue(targetslno 1);
   target_range.getCell(targetslno,j).setValue(value);
  }
  targetslno=targetslno 1;
  }

}

CodePudding user response：

Description

I believe what you are trying to do is copy values from source A4:BR(last row) to the next available row of target. But the values in column A are incremented by the number of existing rows in target. I say source A4 because your source_range starts in A3 and the getCell offset starts at row 2, making it A4.

Since you are copying contiguous rows and columns I would use getValues()/setValues() for performance. Also I used Array.forEach to increment the row numbers.

Script

function myFunction() {
  try {
    var sheet = SpreadsheetApp.openById("someid");
    var source_sheet = sheet.getSheetByName("Date importate");

    var target = SpreadsheetApp.openById("someid");
    var target_sheet = target.getSheetByName("Date prelucrate");
    var lastRow_indexoftarget = target_sheet.getLastRow();

    // your source_range starts A3 but your getCell starts row 2
    // get values from A4:BR??? (last row)
    var values = source_sheet.getRange(4,1,source_sheet.getLastRow()-3,70);

    // increase row numbers
    values.forEach( row => row[0] = row[0] lastRow_indexoftarget);

    // put values at last row  1
    target_sheet.getRange(lastRow_indexoftarget 1,1,values.length,values[0].length).setValues(values)
  }
  catch(err) {
    console.log(err);
  }
}

Reference

CodePudding user response：

Try this:

function myFunction() {
  const ss0 = SpreadsheetApp.openById("1lE8bTAZ1qtxZhHf_tCcDL4Fb9nQNRiDicc7GQyDiXmA");
  const ss1 = SpreadsheetApp.openById("1lE8bTAZ1qtxZhHf_tCcDL4Fb9nQNRiDicc7GQyDiXmA");
  if (ss0 && ss1) {
    const ssh = ss0.getSheetByName("Date importate");
    const svs = ssh.getRange("A3:BR100").getValues();
    const tsh = ss1.getSheetByName("Date prelucrate");
    if (ssh && svs && tsh) {
      tsh.getRange(tsh.getLastRow()   1, 1, svs.length, svs[0].length).setValues(svs);
    } else {
      SpreadsheetApp.getUi().alert("Invalid Sheets or Ranges");
    }
  } else {
    SpreadsheetApp.getUi().alert("Invalid Source or Target or both ");//Check ssid
  }
}