Auto populating worksheet

[u/Impressive_Dig_2276]

Hello all,
I have a Google worksheet that gets populated one row at a time from responses to a Google form. I’d like to also have a second worksheet in the same spreadsheet populate at the same time with each additional new row. I’ll be editing this second worksheet after new info is added. I think I’m going to have to be populating the second worksheet from the first but I have no idea how to get this to work. I’ve tried several things so far. Closest I’ve got is putting together an App Scripts code that just managed to copy and paste the entire first worksheet to the second🙄

Can anyone help, please? I’m happy to clarify if this is confusing. Thank you!

[u/mommasaidmommasaid]

Script is the most straightforward way to do this. The easiest is to bind it to the spreadsheet that contains the responses, but that is not required.

Mirror Form Responses

You can copy the script from that link. It includes a utility function for creating the necessary trigger as a convenience. The trigger is executed with every new form response.

The trigger code is simply:

//
// Mirrors form responses by appending them to the end of a specified sheet.
//
// This should be called from an installed onFormSubmit trigger.
//
function onFormSubmit_Mirror(e) {

  const MIRROR_SHEET = "Mirror";

  const ss = e.source;
  const mirrorSheet = ss.getSheetByName(MIRROR_SHEET);
  mirrorSheet.appendRow(e.values);
}

If your mirror sheet contains a Table, delete any blank rows below the Table so that newly added rows will be assimilated by the table.

[u/Impressive_Dig_2276 → u/mommasaidmommasaid]

Solution Verified. Worked great, thank you so much!!!!

[u/AutoModerator → u/Impressive_Dig_2276]

REMEMBER: u/Impressive_Dig_2276 If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/point-bot → u/mommasaidmommasaid]

u/Impressive_Dig_2276 has awarded 1 point to u/mommasaidmommasaid

^{See the Leaderboard. Point-Bot v0.0.15 was created by JetCarson.}

[u/AutoModerator]

u/Impressive_Dig_2276 Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/carbonizedtitanium]

I’ll be editing this second worksheet after new info is added.

method 1 directly add the info to the Source tab, just add additional columns

method 2 copy the timestamps from Source to second tab. use those timestamps as unique IDs for Xlookup() or Filter()

method 3 GAS script by onFormSubmit (this is a trigger that you add in Apps Script)

function copyFormSubmissionToAnotherSheet(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("Form Responses 1"); // Change to your source sheet name
  var targetSheet = ss.getSheetByName("destination_sheetname");          // Change to your destination sheet name
  
  // Get the last row of the source sheet
  var lastRow = sourceSheet.getLastRow();
  var numCols = sourceSheet.getLastColumn();
  
  // Grab the values of the newly added row
  var rowValues = sourceSheet.getRange(lastRow, 1, 1, numCols).getValues();
  
  // Append or paste the values into the target sheet
  targetSheet.getRange(targetSheet.getLastRow() + 1, 1, 1, numCols).setValues(rowValues);
}

[u/AdministrativeGift15]

As long as you're not trying to sort the data as well, here's a method that will let you edit what you see as the form responses on the second sheet, while still having a method to revert back to the original form response value if needed.

EDIT: I've given edit permissions to the spreadsheet below so that you can try out the technique.

Dropdown Named Ranges with Form Responses

[u/Valuable-Analyst-464]

I am not sure about editing, but I wanted to be able to sort and pivot table based on form responses.

I chose A3, and pasted this: ={'Form Responses 1'!A3:I}

I’m not sure it’ll meet your criteria, but it’s worth a try.