Google-sheets – Script to copy values only

google sheetsgoogle-apps-scriptgoogle-apps-script-triggers

I have a script which copies new form data from one sheet to another. However, I only want the values and not the formatting to paste.

Here is the script:

function onFormSubmit() {
  // moves a row from a sheet to another when a magic value is entered in a column
  // adjust the following variables to fit your needs
  // see https://productforums.google.com/d/topic/docs/ehoCZjFPBao/discussion

  var sheetNameToWatch = "Form Results";

  var sheetNameToMoveTheRowTo = "Job List";

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveCell();


  if (sheet.getName() == sheetNameToWatch && range.getColumn()) {

    var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
    var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    sheet.getRange(range.getRow(), 1, 1, 
      sheet.getLastColumn()).copyTo(targetRange), {contentsOnly:true};
  }
}

Any ideas?

Best Answer

Your last line has a mislaid close-paren. Rather than:

sheet.getRange(range.getRow(), 1, 1, 
  sheet.getLastColumn()).copyTo(targetRange), {contentsOnly:true};

the copyTo "options"-object parameter must be inside the function's parameter list:

sheet.getRange(range.getRow(), 1, 1, 
  sheet.getLastColumn()).copyTo(targetRange, {contentsOnly:true});

Related Solutions

Google-sheets – Create a function to copy range (values only) to a new tab at specified intervals

I wasn't quite sure what you meant by Range as an input to your custom function. Is this the source range or destination range?

Anyways, this is something that should hopefully get you started. There is no timer element, nor is it a custom function. But can be converted to one easily.

The manual timer element can be achieved by adding a trigger. In script editor, go to resources, current project triggers. You can attach a script to a date trigger of your choosing.

function backup(){
/* Edit the vars below this line for your needs */
var sourceSheet  = "Sheet2" ;  // Enter the name of the sheet with the source data
var sourceRange = "A1:B20" ; // Enter the range of the cells with the source data
var targetSheet = "Sheet3" ; // Enter the name of the target sheet  
var targetRange = "A2:B21" ; // Enter the range of cells you wish to copy data to. Note this must be same size as source range.
/* No need to edit below this point */  


var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sourceSheet);
var values = sheet.getRange(sourceRange).getValues();
ss.getSheetByName(targetSheet).getRange(targetRange).setValues(values);

}

Google-sheets – Creating a copy of a sheet by copying values only

Replace the last line of the first function,

ss.insertSheet(sheetName, 4, {template: templateSheet});

with the following:

var sheet = ss.insertSheet(sheetName, 4, {template: templateSheet}); 
sheet.getDataRange().copyTo(sheet.getDataRange(), {contentsOnly:true});

Here, the first line gets the reference to the inserted sheet. The second line uses copyTo to copy the inserted sheet into itself, but pasting only the values.

Best Answer

Related Solutions

Google-sheets – Create a function to copy range (values only) to a new tab at specified intervals

Google-sheets – Creating a copy of a sheet by copying values only

Related Topic