Simple Google Spreadsheet Script to copy and paste cells gives error

Question

I have some relatively simple code below that throws an error: : "Cannot convert NaN to (class)".

All I want to do is copy some cells from one place to another!

Can anyone please let me know what is wrong with this code?

Many thanks in advance.

function myFunction() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet 1');   
  var values = sheet.getDataRange().getValues()  

  for( var row = values.length -1; row >= 0; --row )
    if (values[row][5] == 'Here')
     var maxRows = sheet.getMaxRows()
     var startRow = (row)+1
     var numRows = (maxRows)-(row)
     var Range = sheet.getRange(startRow, 3, numRows, 3).getValues()
     sheet.getRange(row, 3, numRows, 3).setValues(Range)     // (row, column, numRows, numColumns)      
  }

So as the code hopefully shows, I want to copy cells in the range C:E but only rows x to getMaxRows(), where x is the row number where 'Here' is found in column F, plus 1. Then I want to paste this into the same columns C:E but one row higher than originally (into the same row as 'Here' in column F).

Any help would be very much appreciated. Thanks for looking.

EDIT: The error is on this line :

var Range = sheet.getRange(startRow, 3, numRows, 3).getValues()

In my sheet, the cells to be copied could countain blank cells and even entire blank rows. Could this be causing the issue?

Did you mean to have newRange in there? because that variable is never defined. Also, does it give you a line number that is throwing that error? — Phil Bozak
Hi again Phil. Thanks for pointing that out. I've edited the question to remove that undefined variable. The error still shows, and therefore the question still stands! I've also added which line the error appears. Many thanks, vin — Vin

Serge insas Serge insas · Accepted Answer · 2013-02-11T09:35:05

Starting from your description instead of your code (why don't you use {} in loops and conditions ?) I suggest you try this

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet 1');   
  var values = sheet.getDataRange().getValues()  
  var maxRows = sheet.getLastRow()
  var datatoCopy = []   
  for( var row = values.length -1; row >= 0; --row ){
    if (values[row][5] == 'here'){
     var whereToCopy = row+1
     Logger.log(whereToCopy);
     break
     }
     }
   for(row=whereToCopy-1;row<maxRows;++row){
    datatoCopy.push([values[row][2]+'**']);// store column data in an array - remove the ** that I used to see what was copied ;-)
    }
    Logger.log(datatoCopy)
sheet.getRange(whereToCopy, 5, datatoCopy.length, 1).setValues(datatoCopy);// overwrite data to column E
}

Following your comment, try this version ?

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet 1');   
  var values = sheet.getDataRange().getValues()  
  var maxRows = sheet.getLastRow()
  var datatoCopy = []   
  for( var row = values.length -1; row >= 0; --row ){
    if (values[row][5] == 'here'){
     var whereToCopy = row
     Logger.log(whereToCopy);
     break
     }
     }
   for(row=whereToCopy-1;row<maxRows-1;++row){
    var rowData=[]
    rowData.push(values[row+1][2]+'*C*');// I added these 'indicators' to show what happens... delete them when the result is ok ;-)
    rowData.push(values[row+1][3]+'*D*');//
    rowData.push(values[row+1][4]+'*E*');//
    datatoCopy.push(rowData);// store column data in an array - remove the ** that I used to see what was copied ;-)
    }
    Logger.log(datatoCopy)
sheet.getRange(whereToCopy, 3, datatoCopy.length, datatoCopy[0].length).setValues(datatoCopy);// overwrite data to column E
}

Simple Google Spreadsheet Script to copy and paste cells gives error

1 Answers