I am trying to make a button in google sheets that, when clicked, it copies one tab (tab 1) to another (tab 2). I need it to paste Values Only. I also need it to overwrite the data in tab 2 each time it is copied.

I have been searching online and pulling different things together but I haven't been able to get something that works.

All help is greatly appreciated!

I have tried a few scripts I have found online but I haven't been able to put them together to get this to work.

CodePudding user response：

Based on your problem statement, you can try below sample script:-

function copyData(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const tab1 = ss.getSheetByName('tab1');
  const sourceData = tab1.getDataRange().getValues();// getting all the data from tab1    
  const tab2 = ss.getSheetByName('tab2')    
  tab2.getRange(1, 1, sourceData.length, sourceData[0].length).setValues(sourceData );// pasting all the data in tab2 with header 
}

You can add a button through drawing and assign the function copyData to it.

Reference:-

getDataRange()

CodePudding user response：

This is assuming:

1. The dataset is not too large (over 10k rows);
2. No header considered, as it gets the data from row 01

function moveData(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const tab1 = ss.getSheetByName('tab1');
  const data = tab1.getRange(1,1,tab1.getLastRow(),tab1.getLastColumn()).getValues();

  const tab2 = ss.getSheetByName('tab2')

  tab2.getRange(tab2.getLastRow()   1, 1, data.length, data[0].lengt).setValues(data);
}

Check this one for creating a custom menu link

If a button is required, add a drawing in the shape of a button, a text to it and assign moveData as the script to it, once this is on your spreadsheet.