TypeError: file1(...).copyColumns.copyTo is not a function. I am getting this error on apps script-CodePudding

I am creating a script that copies data from one sheet and pastes to another. The sheets can be in different workbooks and the data range can be multiple columns. Although I have made all the script that worked perfectly fine earlier, but I made some changes in it. Now it is showing TypeError: file1(...).copyColumns.copyTo is not a function this error. I know there might be just a small mistake that I am missing. I have spent 3-5 hours finding it but no luck. any help would be highly appreciated.

Script for File 1

function file1(url, sheet, selected_columns, headerRow) {
  url = "Workbook 1 url"
  sheet = "sheet to copy from"
  selected_columns = ['Column 1 Name', 'Column 2 Name', 'Column 3 Name']
  headerRow = '1'

  var file = SpreadsheetApp.openByUrl(url)
  copyColumns = []
  sheetsNames = []
  var sheets = file.getSheets();
  for (var i=0 ; i<sheets.length ; i  ) sheetsNames.push(sheets[i].getName())
  var selected_sheet = file.getSheetByName(sheet);
  var lastCol = selected_sheet.getDataRange().getLastColumn();
  var columns = selected_sheet.getRange(headerRow,1,headerRow,lastCol).getValues();
  for (i=0; i<selected_columns.length; i  ){
    let column = columns[0].indexOf(selected_columns[i]);
    if (column != -1) {
      var copy = copyColumns.push(selected_sheet.getRange(1, column   1, selected_sheet.getMaxRows()));
      Logger.log(column  1)
    }
  }

  Logger.log(sheetsNames)
  Logger.log(selected_columns)
  Logger.log(copyColumns)
  return {url, sheet, copyColumns}
}

Script for File 2

function file2(url, sheet, selected_columns, headerRow) {


  url = "workbook 2 url"
  sheet = "sheet to paste in"
  selected_columns = ['Column 1 Name', 'Column 2 Name', 'Column 3 Name']
  headerRow = '1'

  var file = SpreadsheetApp.openByUrl(url)
  pasteColumns = []
  sheetsNames = []
  var sheets = file.getSheets();
  for (var i=0 ; i<sheets.length ; i  ) sheetsNames.push(sheets[i].getName())
  var selected_sheet = file.getSheetByName(sheet);
  var lastCol = selected_sheet.getDataRange().getLastColumn();
  var columns = selected_sheet.getRange(headerRow,1,headerRow,lastCol).getValues();
  for (i=0; i<selected_columns.length; i  ){
    let column = columns[0].indexOf(selected_columns[i]);
    if (column != -1) {
      var copy = pasteColumns.push(selected_sheet.getRange(1, column   1, selected_sheet.getMaxRows()));
      Logger.log(column  1)
    }
  }

  Logger.log(sheetsNames)
  Logger.log(columns)
  Logger.log(selected_columns)
  Logger.log(pasteColumns)
  return {url, sheet, pasteColumns}
}

The script that runs both above functions together

function agg_test(){
  Logger.log(file1().copyColumns)
  for (i=0; i<file1().copyColumns.length; i  ){
    file1().copyColumns[i].copyTo(file2().pasteColumns[i]);
  }
}

CodePudding user response：

Issue and solution:

I think that your showing error message of TypeError: file1(...).copyColumns.copyTo is not a function might be due to file1().copyColumns.copyTo( instead of file1().copyColumns[i].copyTo(. So I'm worried that your showing script might be different from the script for replicating your showing error message.
If your currently showing script is used, I think that from url = "Workbook 1 url" of file1() and url = "workbook 2 url" of file2(), another error occurs like Target range and source range must be on the same spreadsheet.. I'm worried about this as the 2nd issue.

And, in your following script,

  function agg_test(){
    Logger.log(file1().copyColumns)
    for (i=0; i<file1().copyColumns.length; i  ){
      file1().copyColumns[i].copyTo(file2().pasteColumns[i]);
    }
  }

file1() and file2() is run several time. Please be careful this.

If your currently showing script is modified, how about the following modification?

Modified script:

function file({ url, sheet, selected_columns, headerRow }) {
  var file = SpreadsheetApp.openByUrl(url)
  copyColumns = []
  sheetsNames = []
  var sheets = file.getSheets();
  for (var i = 0; i < sheets.length; i  ) sheetsNames.push(sheets[i].getName())
  var selected_sheet = file.getSheetByName(sheet);
  var lastCol = selected_sheet.getDataRange().getLastColumn();
  var columns = selected_sheet.getRange(headerRow, 1, headerRow, lastCol).getValues();
  for (i = 0; i < selected_columns.length; i  ) {
    let column = columns[0].indexOf(selected_columns[i]);
    if (column != -1) {
      var copy = copyColumns.push(selected_sheet.getRange(1, column   1, selected_sheet.getMaxRows()));
      Logger.log(column   1)
    }
  }
  Logger.log(sheetsNames)
  Logger.log(selected_columns)
  Logger.log(copyColumns)
  return { url, sheet, copyColumns }
}

function agg_test() {
  var source = {
    url: "Workbook 1 url",
    sheet: "sheet to copy from",
    selected_columns: ['Column 1 Name', 'Column 2 Name', 'Column 3 Name'],
    headerRow: '1'
  };
  var destination = {
    url: "workbook 2 url",
    sheet: "sheet to paste in",
    selected_columns: ['Column 1 Name', 'Column 2 Name', 'Column 3 Name'],
    headerRow: '1'
  }

  var ss = SpreadsheetApp.openByUrl(destination.url);
  var sheet = SpreadsheetApp.openByUrl(source.url).getSheetByName(source.sheet).copyTo(ss);
  source.sheet = sheet.getSheetName();
  source.url = destination.url;

  var a = file(source).copyColumns;
  var b = file(destination).copyColumns;
  for (i = 0; i < a.length; i  ) {
    a[i].copyTo(b[i]);
  }
  ss.deleteSheet(sheet);
}

In this modification, the source sheet is copied to the destination sheet. And, the range is copied using your script. By this, the method of copyTo(destination) of Class Range can be used.

Note:

At the above-modified script, it supposes that the source sheet has no formulas which are using other sheets. If your actual source sheet is such the sheet, please modify agg_test() as follows.

  function agg_test() {
    var source = {
      url: "Workbook 1 url",
      sheet: "sheet to copy from",
      selected_columns: ['Column 1 Name', 'Column 2 Name', 'Column 3 Name'],
      headerRow: '1'
    };
    var destination = {
      url: "workbook 2 url",
      sheet: "sheet to paste in",
      selected_columns: ['Column 1 Name', 'Column 2 Name', 'Column 3 Name'],
      headerRow: '1'
    }

    var src = SpreadsheetApp.openByUrl(source.url);
    var temp = src.getSheetByName(source.sheet).copyTo(src);
    var range = temp.getDataRange();
    range.copyTo(range, {contentsOnly: true});
    var dst = SpreadsheetApp.openByUrl(destination.url);
    var sheet = temp.copyTo(dst);
    source.sheet = sheet.getSheetName();
    source.url = destination.url;

    var a = file(source).copyColumns;
    var b = file(destination).copyColumns;
    for (i = 0; i < a.length; i  ) {
      a[i].copyTo(b[i]);
    }
    src.deleteSheet(temp);
    dst.deleteSheet(sheet);
  }

Issue and solution:

Modified script:

Note:

References: