CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Mr.Burns
Mr.Burns

Reputation: 700

Google Script - Unable to copy from one sheet to another

This is my first Google Script and I am coming from a VBA background so I have likely missed something obvious. I am attempting to copy from one sheet to another, some that is quite simple in VBA, but I am struggling with it and I dont understand what I am missing

The below code I would have though would copy a range in one sheet to another sheet. Not the whole range, just a section of it and only 1 row per an OnEdit()

I checked the ranges I am wanting to copy from and copy to using targetSheet.getRange(targetSheet.get1stNonEmptyRowFromBottom(1) + 1, 1,1,numColumns-1).activate(); & sheet.getRange(row, 1, 1, numColumns-1).activate(); and it selects the range I would expect

Currently the execution doesn't show any fails either

function onEdit(e) {
  
  //This is sheet we will be copied to 
  const VALUE = ["LEAD","LANDSCAPE"];
  //This is the column for the check box
  const COLUMN_NUMBER = 25
  //The main sheet that is being copied from
  const MAIN_SHEET = 'Form Responses 1'

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  var cell = sheet.getActiveCell();
  var row = cell.getRow();
  var lead_or_landscape = sheet.getRange(row,20).getValue();

  //This is so we dont run this by mistake on another cell edit or sheet
  if((COLUMN_NUMBER == cell.getColumn() && MAIN_SHEET == sheet.getName && cell.isChecked() == true) 
  && (lead_or_landscape == VALUE[0] || lead_or_landscape == VALUE[1])){
    
    //copies row from current sheet to the specificed sheet (Lead or landscape)
    var numColumns = sheet.getLastColumn();
    var targetSheet = spreadsheet.getSheetByName(lead_or_landscape);
    var target = targetSheet.getRange(targetSheet.get1stNonEmptyRowFromBottom(1) + 1, 1,1,numColumns-1);
    sheet.getRange(row, 1, 1, numColumns-1).copyTo(target());
  }
}

Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
  const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
  return search ? search.getRow() : offsetRow;
};

Upvotes: 1

Views: 220

Answers (2)

Muhammet Yunus
Muhammet Yunus

Reputation: 589

You've to use parentheses in sheet.getName() and not use them in copyTo(target())

And you can use sheet.appendRow() method instead of copyTo(), that is easier.

function onEdit(e) {
  
  //This is sheet we will be copied to 
  const sheets = ["LEAD","LANDSCAPE"];
  //This is the column for the check box
  const colNumber = 25;
  //The main sheet that is being copied from
  const mainSheet = 'Form Responses 1';

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  var cell = sheet.getActiveCell();
  var row = cell.getRow();
  var leadOrLandscape = sheet.getRange(row,20).getValue();

  //This is so we dont run this by mistake on another cell edit or sheet
  if(colNumber == cell.getColumn() 
     && mainSheet == sheet.getName() 
     && cell.isChecked() == true 
     && sheets.includes(leadOrLandscape)){
    
    //copies row from current sheet to the specificed sheet (Lead or landscape)
    var numColumns = sheet.getLastColumn();
    var targetSheet = spreadsheet.getSheetByName(leadOrLandscape);
    targetSheet.appendRow(sheet.getRange(row, 1, 1, numColumns-1).getValues()[0])
    
  }
}

Upvotes: 1

jp astier
jp astier

Reputation: 39

I just took a quick look at your code, are you sure about this line?

sheet.getRange(row, 1, 1, numColumns-1).copyTo(target());

it shouldn't rather be

sheet.getRange(row, 1, 1, numColumns-1).copyTo(target);

Upvotes: 1

Related Questions