0

I want to copy IDs and dates and have written this script so far.

function copy(){  

  var s1=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");  
  var s2=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");  
  //s1.clear();   
   s2.getRange(3,7).copyTo(s1.getRange(s1.getLastRow()+1,1));  
   s2.getRange(23,4).copyTo(s1.getRange(s1.getLastRow()+0,2));    
}

The script also works, but the table gets longer and longer.
I copy with (3,7) the Id's and with (23,4) dates, Ids never change, but the dates changes a lot.
How can I overwrite or delete the line before copying?
Something like this s1.clear(s2.getRange(3,7));...

1
0

The option exists for you to delete the entire contents of the target sheet, or merely to insert each new update in the row immediately after the previous data. The two are not complimentary, so your choice is not an obvious one. In the following code, I have taken the option of insert the new update into the following row - this does NOT expand the existing rows of the sheet.

The copyto command is supplemented with the option "{contentsOnly:true}". This is the component that ensures that new additional rows are inserted.

function copy(){  

  // establish target sheet
  var s1=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");  
  //establish source sheet
  var s2=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");  

  // Establish the last row of data i the target sheet
  var s1lastrow = s1.getLastRow();

  // Establish the target ranges. Note that target01 uses the last row number Plus one, and target02 uses the last row number/ The effect is that the rows are offset.
  // In addition, the code does not work unless there is already existing data in the target sheet.
  var target01 = s1.getRange(s1lastrow+1,1); 
  var target02 = s1.getRange(s1lastrow+0,2);   


  // Establish the source ranges
  // source01 comes from G3; source02 comes from D23
  var source01 = s2.getRange(3,7);
  var source02 = s2.getRange(23,4);

  // copy source01 to target01 and source02 to target02
  // note the options 
  source01.copyTo(target01, {contentsOnly: true});
  source02.copyTo(target02, {contentsOnly: true});
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.