I have set up a script that creates a Google form and links it to a spreadsheet, is there a way to collect the responses and place them in certain cells in another sheet then unlinks and deletes the form response sheet.

the script I need will have to be flexible enough that it won't matter what the response sheet is named as I will be making multiple one use forms hence why I would also like to delete the response sheet after the answer is moved

for example say the answer is A (a1), B (b1) and C (c1) and I want to move it to sheet 'C' and into columns F, G and H after that's done i would like the response sheet to unlink and be deleted

any help would be greatly appreciated

CodePudding user response：

Issue:

You want form response data to be submitted to a sheet of your choice, not the one that is created when linking the form to the spreadsheet.

Solution:

In that case, I'd suggest not linking the form to the spreadsheet at all, and use an onFormSubmit trigger to write the submitted data to your desired sheet.

Workflow:

1. Install an onFormSubmit trigger. You can do that manually, following these steps, or programmatically, by executing this function once:

const SOURCE_FORM_ID = "YOUR_FORM_ID"; // Change according to your needs

function installOnFormSubmitTrigger() {
  const form = FormApp.openById(SOURCE_FORM_ID);
  ScriptApp.newTrigger("onFormSubmitTrigger")
    .forForm(form)
    .onFormSubmit()
    .create();
}

2. Once the trigger is installed, a function named onFormSubmitTrigger (it doesn't have to be named that way) will execute every time someone submits a response to the form. This function should append the response data to your desired sheet. It could be something like this (check inline comments):

const TARGET_SPREADSHEET_ID = "YOUR_SPREADSHEET_ID"; // Change according to your needs
const TARGET_SHEET_NAME = "Sheet1"; // Change according to your needs

function onFormSubmitTrigger(e) {
  const targetSpreadsheet = SpreadsheetApp.openById(TARGET_SPREADSHEET_ID);
  const targetSheet = targetSpreadsheet.getSheetByName(TARGET_SHEET_NAME);
  if (targetSheet.getLastRow() === 0) { // Add headers if they don't exist yet
    const itemTitles = e.source.getItems().map(item => item.getTitle()); // Get item titles
    itemTitles.unshift("Timestamp"); // Append "Timestamp" to the sheet (if desired)
    targetSheet.appendRow(itemTitles); // Append form item titles to the sheet
  }
  const itemResponses = e.response.getItemResponses();
  const responses = itemResponses.map(itemResponse => itemResponse.getResponse()); // Get user responses
  responses.unshift(new Date()); // Add today's date to the responses (if desired)
  targetSheet.appendRow(responses); // Append responses to the sheet
}

Note:

• If you don't want to submit to the first columns in the spreadsheet, simply add empty strings to the responses array, or use Range.setValues instead.

Reference:

• Installable Triggers
• appendRow