Duplicate specific cell values from one sheet to specific cells in another using Google script

Question

After spending 10 hours of figuring out a script, I'm going to give it a try and ask it here. I already read all of the relevant questions on Stackoverflow, but I still can't get my script work. I want two buttons on sheet1 (invoice). Button 1 duplicates cell values to sheet2 and Button 2 clears the content of the sheet. Button 2 is already working.

What the script for button 1 needs to do:

Duplicate cell values from sheet1 to sheet2. But the difficult part (for me) is that the cells are specific (B4, C12, C13, F29, F30) and they need to be duplicated to the first free row on sheet2, but in specific columns (F6, B6, D6, G6, I6). And I don't know how to do this.

After trying a lot of scripts I'm now having this:

    function DuplicatetoSheet2()
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange('B4:F30');  
  var data = range.getValues();

  var ts = "sheet2";
  ts.getRange(ts.getLastRow()+1, ??? ).setValues(data);
}

And since sheet1 is an invoice I also tried this piece of code, but I don't know if I can use it:

var client = sheet.getRange("B4").getValue();
var dateInvoice = sheet.getRange("C12").getValue();
var numInvoice = sheet.getRange("C13").getValue();
var subtotal = sheet.getRange("F29").getValue();
var vat = sheet.getRange("F30").getValue();

I really hope that someone can help me out.

Thanks in advance!

specific columns (F6, B6, D6, G6, I6) do you mean column F, B, D, G, I ? — Umair Mohammad

Umair Mohammad Umair Mohammad · Accepted Answer · 2018-03-14T08:54:34

You can try something like this :

function moveFromInvoiceSheetToSheet2() {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SHEET_1_NAME");
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SHEET_2_NAME");
  var last_row = sheet2.getLastRow(); //This will fetch the last row index = first free row
  // copyTo can also be used instead of moveTo
  sheet1.getRange("B4").moveTo(sheet2.getRange(last_row + 1, 6)); //F = 6
  sheet1.getRange("C12").moveTo(sheet2.getRange(last_row + 1, 2)); //B = 2
  sheet1.getRange("C13").moveTo(sheet2.getRange(last_row + 1, 4)); //D = 4
  sheet1.getRange("F29").moveTo(sheet2.getRange(last_row + 1, 7)); //G = 7
  sheet1.getRange("F30").moveTo(sheet2.getRange(last_row + 1, 9)); //I = 9
}

You don't need to clear content after copying because you're moving. If you want you can use copyTo also

Duplicate specific cell values from one sheet to specific cells in another using Google script

1 Answers