CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Rilves
Rilves

Reputation: 156

Apps Script's spreadsheets "copyTo()" writes the range two times?

Henlo! I've been stuck on a weird bug in google apps scripts (relative to sheets) and I didn't find any response on internet anywhere.

I have a sheet with filters (~370 lines) and I'm trying to copy its values to another sheet. But when I do that, for some reason, the 370 lines get copied 2 times, and I have absolutely no idea why. Result sheet is 370 lines + one blank line + 370 lines again.

Here's the code I'm using :

  var spreadsheet = SpreadsheetApp.openById(spreadsheetID);
  var init_sheet = spreadsheet.getSheetByName("default");
  var final_sheet = spreadsheet.insertSheet("final");


  var soure_range = init_sheet.getRange("A1:K");
  var target_range = final_sheet.getRange("N1:X");
  soure_range.copyTo(target_range);

if anyone knows why the range gets copied 2 times, I'd be glad to get unstuck :x

EDIT :

As discussed in the comments, this bug seems to appear only when I use a sheets converted from xlsx (the function above works correctly with any other normal sheet). Here's the convertion function I use :

function convert(xlsxID, name, parentFolder) { 
  var xlsxBlob = DriveApp.getFileById(xlsxID).getBlob();
  var file = {
    title: ,
    //Which Drive Folder do you want the file to be placed in
    parents: [{'id':parentFolder}],
    key: 'XXXX',
    value: 'XXXX',
    visibility: 'PRIVATE'
  };

  file = Drive.Files.insert(file, xlsxBlob, {
    convert: true
  });

  return file.getId();
}

Upvotes: 2

Views: 1678

Answers (2)

Kristkun
Kristkun

Reputation: 5963

I was able to replicate the issue. Initially I did not filter the data from the converted xlsx file.

When copying range using copyTo(destination). Use the top-left cell position for your destination range.

Sample Code:

  var spreadsheet = SpreadsheetApp.openById(spreadsheetID);
  var init_sheet = spreadsheet.getSheetByName("Feuil1");
  var final_sheet = spreadsheet.insertSheet("final");


  var soure_range = init_sheet.getRange("A1:K");
  var target_range = final_sheet.getRange("N1"); //Top-left cell position
  soure_range.copyTo(target_range);

Output:

enter image description here

Upvotes: 2

Cooper
Cooper

Reputation: 64110

const ss = SpreadsheetApp.openById(spreadsheetID);
const ish = ss.getSheetByName("default");
const fsh = ss.insertSheet("final");
const srg = ish.getRange(1,1,ish.getLastRow(),11);
const vs = srg.getValues();
const trg = fsh.getRange(1,14,vs.length,vs[0].length);
srg.copyTo(trg);

Upvotes: 0

Related Questions