How to Copy Values from one Google Sheet to Another Google Sheet after submitting button

Question

Hy, I am using Google Sheet which is used to calculate the commission of agents. I am not familiar with the Google Script. Here is the link of the Sheet

enter link description here

I need a script that enables me to copy data from Calculator range B3:B5, D3:D5, and F3:F5 on the RawData sheet.

The script should enable me to copy data every time when I will press the "Submit" button and every time the record should be on the next empty row. I hope this makes sense.

Answer 1

Explanation:

The following script will copy the data from the Calculator sheet; ranges B3:B5, D3:D5 and F3:F5 to the next available row of the RawData sheet when you click a button called Submit. In your case, I created the button under the Scripts menu as you can see in the screenshot below. I already implemented the solution for you in the shared document, therefore you just have to try it out.

---

Solution:

function copyToRawData() {
  
  const ss = SpreadsheetApp.getActive();
  const cal_sh = ss.getSheetByName('Calculator');
  const raw_sh = ss.getSheetByName('RawData');
  const ranges = ['B3:B5','D3:D5','F3:F5'];
  const values = [];
  ranges.forEach(r=>values.push(...cal_sh.getRange(r).getValues().flat()));
  raw_sh.getRange(raw_sh.getLastRow()+1,1,1,values.length).setValues([values]);
  
}

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

---

Result:

Here is the submit button that you need to press in order to perform this operation.

Answer 2

• Use getRangeList(a1Notations) to retrieve the RangeList corresponding to B3:B5, D3:D5, F3:F5.
• Use getRanges(), getValues(), map() and flat() to transform the RangeList to a simple array of values corresponding to the source ranges.
• Use appendRow(rowContents) to append these values to the first empty row of your destination sheet.
• Attach this function to your desired button (ref: Clickable images and drawings in Google Sheets).

Code sample:

function copyData() {
  const ss = SpreadsheetApp.getActive();
  const sourceSheet = ss.getSheetByName("Calculator");
  const targetSheet = ss.getSheetByName("RawData");
  const rangeList = sourceSheet.getRangeList(['B3:B5', 'D3:D5', 'F3:F5']);
  const values = rangeList.getRanges().map(range => range.getValues()).flat(2);
  targetSheet.appendRow(values);
}

Answer 3

You do not need a script for that. Please use the following formula:

=TRANSPOSE(flatten({ Calculator!B3:B5;Calculator!D3:D5;Calculator!F3:F5}))

Please remember that the data will always be updated.

Functions used:

• TRANSPOSE
• flatten Undocumented