Google Script Function - Copy Paste

Question

I was writing a script through Google Script about the function of a button when clicked. What I want to happen is SHEET 1 Values gets copied to SHEET 2 AS VALUES (Not copying the Google Sheets Formulas), then SHEET 1 VALUES will get cleared. However, it seems I'm having an issue with the values getting copied to SHEET 2.

I tried to search for something that could resolve this, but I'm not really that an expert when it comes to writing scripts since I'm a newbie to this.

// Display a dialog box with a message and "Yes" and "No" buttons.
  var ui = SpreadsheetApp.getUi();
  var response = ui.alert("Do you want to capture all data?", ui.ButtonSet.YES_NO);

  // Process the user's response.
  if (response == ui.Button.YES) {
  }

function remove() {
  var spreadsheet = SpreadsheetApp.getActive().getSheetByName("2019")
  var destsheet = SpreadsheetApp.getActive().getSheetByName("Handled Tickets");

  var getLastContentRow = spreadsheet.getRange("A8:I").getValues();
  var destination = destsheet.getRange(destsheet.getLastRow()+1,1);
  var source = spreadsheet.getRange("A8:I").getValues();
  getLastContentRow.copyTo(destination.CopyPastType.PASTE_VALUES); 

  spreadsheet.getRange('C8:E').clearContent()
  spreadsheet.getRange('F8:H').clearContent()
}

Expected Flow: 1) When the button has been clicked, whatever data in spreadsheet will be copied to destsheet. 2) Once copied, data in spreadsheet will be cleared.

Additional rules: 1) Once copied to destsheet, data will not be overwritten by other values when the button is clicked again. Instead, it will look for the last row (empty cell) and copy the data there. 2) If all cells have been used, automatically there will be additional 100 rows added.

Error: Cannot find function copyTo in object

Benoît Wéry Benoît Wéry · Accepted Answer · 2019-08-23T09:48:18

There are several issues with your code above (syntax, format, structure, missing semicolons to finish statements,...).

Assuming only the remove() function was being a problem, here is my version below with several comments.

You may also want to review the part with the UI above (e.g. embed it in a function that your button will call, make sure there is some code in your if statement,...).

function remove() {
  var source_sheet = SpreadsheetApp.getActive().getSheetByName("2019"); // better not use "spreadsheet" as variable name here, this is confusing, your content is a sheet
  var dest_sheet = SpreadsheetApp.getActive().getSheetByName("Handled Tickets");

  var getLastContentRow = source_sheet.getRange("A8:I"); // The "copyTo" method applies to ranges, not to arrays, so remove the ".getValues()"
  // --> the "getLastRow" variable name makes me believe you're only looking at copying the last row, but your current range will copy all rows starting at 8. 
  // --> as the same content is captured in "source" below, this might just be a misleading variable name, though, in which case you may want to simply rename it

  var destination = dest_sheet.getRange(dest_sheet.getLastRow()+1,1);

  // var source = spreadsheet.getRange("A8:I").getValues();
  // --> this is duplicate with getLastContentRow, and not use in your function, so presumed useless. Can be removed. 

  getLastContentRow.copyTo(destination, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); 
  // --> the example in the documentation is misleading, but this function requires a third argument for "transposed"

  // spreadsheet.getRange('C8:E').clearContent()
  // spreadsheet.getRange('F8:H').clearContent() 
  // --> why two different calls instead of 1 on C8:H directly? 
  // --> also, why not the same range as the one copied? 

  getLastContentRow.clearContent(); // This will remove all the copied content from the "source_sheet"
}

Google Script Function - Copy Paste

1 Answers