Create a response table with a Google sheet which saves responses in another sheet

Question

I'm trying to create a response table with a Google sheet that lets people enter their responses. Then compile that information into another Google Sheet by specified columns at the bottom of the previous response.

I have tried numerous ways but I cannot seem to get the script to work.

Broken Down, I need the Script/Guide to be able to:

1. Allow multiple people to record their response individually by the given link of Google sheet Form
2. Give a dynamic Sum Total in the Total Row
3. Have an Add row Icon so as to let respondent add rows dynamically and make their own choice of inputs
4. Create a Submit Icon, which records the response to another google sheet

The intended sheet is below

https://docs.google.com/spreadsheets/d/1cQTBONtpLiMbRmtVgSyOT_mShzzwbrX7-w46zy9w9I4/copy?usp=sharing

This may be a simple question that I didn't sound any solution

Answer 1

Please see if this works for you. Modified enquiry form
1. Allow multiple people to record their response individually by the given link of Google sheet Form

You need to share a copy of your spreadsheet. Do not share the original. In the URL you have posted, replace 'edit' with 'copy'.

The modified enquiry form
2. Gives a dynamic Sum Total in the Total Row as your user records their inputs by using onEdit function.
3. Has an Add row and Delete row Icon so as to let respondent add and delete rows dynamically and make their own choice of inputs. To add a row, the active row number should be 4 or above
4. Creates a Place Order Image, which appends the response to another google sheet

To achieve point 4, you need to create a separate spreadsheet having 1 row only. This serves as the column heading and can be in a form as shown below. This spreadsheet contains the details of responses from all users.

Note: Updating a spreadsheet automatically when the user clicks the Submit button would require you to make the spreadsheet shareable via email addresses with editor access at a minimum. This is potentially an unsafe way of saving user responses.

Answer 2

function sendData() {           

  var sprsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = sprsheet.getActiveSheet();

  var range = sheet.getRange("A2:G4");
  var values = range.getValues();

  var mainsprsheet = SpreadsheetApp.openById("main sheet id"); 

// 1. this "main sheet id" should say something like: "1cQTBONtpLiMbRmtVgSyOT_mShzzwbrX7-w46zy9w9I4", you can xopy/paste the id from your address bar in your browser

// ==> https://docs.google.com/spreadsheets/d/1cQTBONtpLiMbRmtVgSyOT_mShzzwbrX7-w46zy9w9I4/edit?ts=5ed39726#gid=0

  var targetSheet = mainsprsheet.getActiveSheet();

targetSheet.appendRow(values); 

// 2. appendRow() accepts an array like for example this array with strings ["Fabric Code", "Thickness","Width","Length","Finish","Quality type","Quantity"]

// method getValues() returns an array of arrays (or 2D array) like for example this one[["Fabric Code", "Thickness","Width","Length","Finish","Quality type","Quantity"]]

  // Think of the outer [..] as row indicator and the inner [..] as the array that holds the columns(fields) of the record.

    // i would change your code to: (remove the //s)
    // var lr = targetSheet.getLastRow()
    // var lc = targetSheet.getLastColumn()
    // targetSheet.getRange(lr+1 , 1, values.length, values[0].length).setValues(values)
    //Clear the cells (optional)
      range.clearContent();

}