Script in Google Apps Script that copies values in a range to another sheet

Question

I am working on a tool that will allow my company to track its financial investments.

I need to create a simple data entry form for users to input transaction data, which will then populate a master sheet which is the basis for the analyses the tool does. I cannot do this via Google Forms because the data entry form uses a lot of conditional formatting based on other data in the sheet.

I have uploaded a very simplified sheet to illustrate what I need: this

What I am looking for is a script that, upon clicking Submit in the Data Entry sheet, copies the values (NOT the formulas) in B12:E12 to the first empty row (in this case, row 8) in the "Master Table" sheet. Ideally, clicking "Submit" will also clear the data entry fields in C4:C7 in the "Data Entry" sheet.

I have looked through various forums for a solution but have not found anything that does exactly that. I am sorry to say I am a complete newbie at Google Apps Script, therefore I could not write my own code to share, which I am aware is customary when asking a question here.

If anyone could point me in the right direction regardless, it would be much appreciated. I am currently trying to learn JavaScript and Google Apps Script using online resources, but for this specific project, it would take too long for me to reach a level where I could help myself.

Answer 1

Thank you very much, GEOWill!

Your answer solved my problem and thanks to your comments, I was able to understand exactly what your code does. I changed the code only to remove the Menu (but thank you very much for showing me how that is done anyway) - I tied the function to a Submit button inserted as a drawing. I also added some code to clear the contents of the entry range after clicking the button (this was suggested by someone else).

The final code that I used is:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var entry_sheet = ss.getSheetByName("Data Entry");
var master_sheet = ss.getSheetByName("Master Table");

function mySubmit() {
  var entry_range = entry_sheet.getRange("B12:E12")
  var val = entry_sheet.getRange("B12:E12").getDisplayValues().reduce(function (a, b) {
        return a.concat(b);
    });
 Logger.log(val);
  master_sheet.appendRow(val);
 entry_sheet.getRange("C4:C7").clearContent()
}

I hope this helps others with a similar problem! Love how supportive this community is. Thanks for helping out.

Answer 2

I would begin by using a menu function to run your code in (rather than a cell button). You could use a cell button, but I believe you need to insert an image and assign a javascript function to that image anyways.

Basically, you begin by going to tools, script editor. Create an onOpen function and create a trigger that runs the onOpen() function each time the spreadsheet is opened. Inside the onOpen() function, we create a menu into which a physical menu item (a kind of button) exists (called 'Submit'). Finally, we associate a function to this button (I called it mySubmit()).

Inside of the mySubmit() function is where most of the functionality you are looking for exists. At this point, it is just a matter of copying from one range of cells and pasting them to another range of cells. For this, you'll notice that I had to setup a few variables ahead of time (ss, entry_sheet, master_sheet, entry_range, and master_row).

One last thing, you may want to protect the Master table sheet because if someone accidentally edits a cell beyond the last one edited, the input row would be copied to that row (due to how the getLastRow() function operates).

Hope this helps!

var ss = SpreadsheetApp.getActiveSpreadsheet();

var entry_sheet = ss.getSheetByName("Data Entry");
var master_sheet = ss.getSheetByName("Master Table");

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('MyMenu')
    .addItem('Submit', 'mySubmit')
    .addToUi();
}

function mySubmit() {
  var entry_range = entry_sheet.getRange("B12:E12");
  var master_row = (master_sheet.getLastRow() + 1);

  entry_range.copyValuesToRange(master_sheet, 1, 3, master_row, master_row);
}