Sheets script: button activated, copies data from a cell to sheet, then clears range of data

Question

I'm pretty good using formulas with google sheets to do a substantial amount of work. Right now, though, I'm finding that I need a system that is "button operated" and scripting is something I am just beginning to dabble in.

I'm trying to create a script that will read the contents of a cell (D2) on sheet called 'Tester Page' and duplicate cell D2 onto a separate sheet in the same document. Once copied, I need the sheet to clear range B5:B16 and D5:d16 on the 'Tester Page.' The data in those two ranges are selected from dropdown menus using data validation.

The final kicker is that it needs to be button activated.

So, in my initial attempts, I'm trying right now to copy the data from cell D2 and just append it to the bottom of the page. It doesn't work. Any help on this step or other steps would be huge. Here's what I have so far:

function addProduct() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Tester Page');
var value = sheet.getRange(2,4)
sheet.appendRow(value);
}

Edit So I got the script up and running (including using a clickable button). Everything went well until we decided to make some changes to how the page would work. Rather than copying the contents of 1 cell onto the second page (called 'Transactions'), we decided we want to take an entire row of data (row 22). I've added my script as it is right now, which will copy a single cell 20 times...not a string of 20 columns. I really would like the entire row, not just 20 columns worth, but can't figure it out.

function mainThing() {
//get content
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Tester Page');
var cell = sheet.getRange(22,1).getValues();

//copy content
var destination = ss.getSheetByName('Transactions');//whatever page
var destCell = destination.getRange(2, 1, 1, 20)
destCell.setValue(cell);

//Add clean row
var add = ss.getSheetByName('Transactions')
add.insertRowBefore(2)

Thanks again for the help. You guys are awesome.

Answer 1

Based on changes, it'd be something like this ...

function mainThing() {
  //get content
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Tester Page');
  var lastCol = ss.getLastColumn();//get last column in the ss
  var cell = sheet.getRange(2, 1, 1, lastCol).getValues(); //get contents of row

  //copy content
  var destination = ss.getSheetByName('Transactions');
  var destRange = destination.getRange(2, 1, 1, lastCol);
  destination.insertRowBefore(2);
  destRange.setValues(cell);

  //clear content
  clearThis("B5:B16");
  clearThis("D5:D16");
}



//content clearer
function clearThis(range){
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheetByName('Tester Page');
   var theRange = sheet.getRange(range);
   theRange.clearContent();  
}

//straight from https://developers.google.com/apps-script/guides/menus

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Do Magic')
      .addItem('Presto', 'mainThing')      
      .addToUi();
}

Answer 2

I got everything working the way I wanted. It duplicates a row of cells to a new sheet, inserts a blank row at the top of the new sheet (so my duplicated data doesn't get covered over), and clears out the data in the ranges of cells I need cleaned. I also have it run when the user clicks a button on screen. Overall, I'm happy that it works.

Here it is in all of its glory:

function mainThing() {
//get content
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Store Page');
var cell = sheet.getRange("A22:Z22").getValues();

//copy content
var destination = ss.getSheetByName('Transactions');//whatever page
var destCell = destination.getRange("A3:Z3");
destCell.setValues(cell);

//Add clean row
var add = ss.getSheetByName('Transactions')
add.insertRowBefore(3)


//clear content
clearThis("C5:C16");
clearThis("E5:E16");
clearThis("C2");
}



//content clearer
function clearThis(range){
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName('Store Page');
 var theRange = sheet.getRange("C5:C16");
 var theRange2 = sheet.getRange("E5:E16");
 var theRangeName = sheet.getRange(2, 3);
 theRange.clearContent();
 theRange2.clearContent();
 theRangeName.clearContent();
}