I was trying to copy/fetch data from specific columns of the last row from my "Total" sheet, and pass those to another sheet named "vnSheet". The code below which i found earlier works fine. But, it copies all the row & column data from the mother sheet, which I don't want to happen.

function copySheet() {
  var sourceSheet  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Total");
  var destSheet  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("vnSheet");
  var columns_to_be_copied = ['A', 'B', 'D', 'F'];
  var columns_to_be_pasted = ['A', 'B', 'D', 'F'];

 for (column in columns_to_be_copied) {

  var copy_range_string = columns_to_be_copied[column]   ':'   columns_to_be_copied[column];
  var paste_range_string = columns_to_be_pasted[column]   ':'   columns_to_be_pasted[column];

  var source = sourceSheet.getRange(copy_range_string);
  var destination  = destSheet.getRange(paste_range_string);
 source.copyTo(destination, {contentsOnly:true});
 }
}

Here, I want to Copy 'A', 'B', 'D', 'F'(except 'C' & 'E' ) column data only from Last row of "Total" and paste those data into the same columns of the "vnSheet" but in the Last row. I searched almost all over the internet and found no solution of my problem, all I found was similar to the code above.

As I'm new to this, my coding experience is limited but I'm a gd learner :) Any help to solve that problem will be greatly appreciated. Thank you.

CodePudding user response：

Use sourceSheet.getLastRow() before the for loop to get the row number of the last row in your "Total" sheet, then add this value after each column letters.

Let's say that you assign the result of the above to a lastRow, then you can use this variable this way:

var copy_range_string = columns_to_be_copied[column]   lastRow   ':'   columns_to_be_copied[column]   lastRow;

NOTE:

You might find helpful to read https://developers.google.com/apps-script/guides/support/best-practices. This article suggest to avoid doing calls to Google Apps Script classes as they are slow. One way to reduce the number of calls do achieve the result that you are looking is by using getRangeList at it allows to get multiple ranges at once. Another way is by using the Advanced Sheets Service as it allow to do batch updates.

CodePudding user response：

So there are a few things

• You currently are not getting the last row, as far as I can tell
• You currently have duplicate variables to hold the same array
• You currently are copying an entire row, which is not what you want

Assuming there are already values in columns C and E in your Target Sheet there are a few ways to approach a solution:

1. You get the existing values from Target Sheet and then overwrite the columns you want to overwrite, then you update the entire row using one array
2. You only overwrite the particular cells you want to update, leaving the rest as is

Option 1 above will be quicker, but we are speaking fractions of a second. Therefore I recommend option 2, as it does not touch an existing row with field you don't want to change.

My proposal is here:

function copyHandler(){
  const activeSheet = SpreadsheetApp.getActive()
  var sourceSheet  = activeSheet.getSheetByName("Total");
  const sourceLastRowNum = sourceSheet.getLastRow()
  var destSheet  = activeSheet.getSheetByName("vnSheet");
  const destLastRowNum = destSheet.getLastRow()   1 

  const lastRow = sourceSheet.getRange(sourceLastRowNum, 1, 1, 6).getValues()[0]
  
  lastRow.forEach( (value, index) => {
    // Skip column C and column E 
    if( index == 2 || index == 4) return

    destSheet.getRange(destLastRowNum, index 1).setValue(value)
  })

  
}