Trying to write a custom copy/paste Google Sheets script

Question

I am trying to create a custom Google Sheets script that when the user has selected a specific cell and hits the custom function button from the menu it copies the data from that cell and re-pastes it into the same cell using the "values only" paste option. Ideally it would also do the same for the next 8 cells directly beneath the selected cell.

Is this even possible?

Yes, it's possible. You can create a custom menu item, or insert an image that is styled to look like a button, and associate a script to the image. You could also use a simple onEdit() function trigger that would run when the user updated the cell. Your question is to general, and does not adhere to the stackoverflow rules. You're supposed to post code of what you have tried. — Alan Wells

BMcV BMcV · Accepted Answer · 2015-12-09T18:08:54

Your first step would be to create a menu to run your function in the spreadsheet. You can find docs and sample code here: https://developers.google.com/apps-script/guides/menus?hl=en

Next, you can use just a few methods to accomplish this task (replacing formulas with their result or "values only").

You need the getActiveRange method, the getValues method, and setValues method. Since the getValues method does not copy formulas, you can just set the result right back to your active range. Documentation for these methods here: SpreadsheetApp

You could hard code in a range that includes the 8 cells below, but in this function it just uses the highlighted range of cells which is more intuitive (I think) and more versatile.

Your code might look like this:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Custom Menu')
      .addItem('Values Only', 'valuesOnlyFunction')
      .addToUi();
}

function valuesOnlyFunction() {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeRangeArr = activeRange.getValues();  
  activeRange.setValues(activeRangeArr);
}

Trying to write a custom copy/paste Google Sheets script

1 Answers