CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Timun 123
Timun 123

Reputation: 1

Duplicate google form responses to another sheet as value automatically

is there any script or formula that can help me do the data cleaning. For example in the sheet below, https://docs.google.com/spreadsheets/d/1mLZ8TYNpNHVN1Kc8QF80Ns3RX-EXdSz5s9WmHoIS8Pw/edit?usp=sharing, I have "Form responses 1" which capture the data entry from google form and "cleaning data" sheet. I tried to use Arrayformula and Importrange but i cannot edit the data in "Cleaning data" sheet. Currently, I just copy and paste manually to the other sheet.

What I wanna do

  1. Respondent enter data in google form.
  2. Data appear in "Form responses 1" sheet.
  3. Updated data in "Form responses 1" automatically copied and paste in the "Cleaning data" sheet by row.

Thank you!

Upvotes: 0

Views: 2008

Answers (1)

Tanaike
Tanaike

Reputation: 201553

In your situation, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet. And, please install OnSubmit trigger to the function onSubmit. When you use this script, please submit your Google Form. By this, the script of onSubmit is run by firing the OnSubmit trigger.

function onSubmit(e) {
  const range = e.range;
  const srcSheet = range.getSheet();
  const dstSheet = e.source.getSheetByName("Cleaning Data");
  const srcRange = srcSheet.getRange(range.rowStart, 1, 1, srcSheet.getLastColumn());
  const dstRange = dstSheet.getRange(dstSheet.getLastRow() + 1, 1);
  srcRange.copyTo(dstRange, {contentOnly: true});
}

Note:

  • In this script, please don't directly run this function with the script editor. Because this script uses the event object of OnSubmit trigger. When you directly run this script an error like TypeError: Cannot read property 'range' of undefined occurs. Please be careful this.

References:

Upvotes: 0

Related Questions