How to Find Google Sheets Linked to your Google Forms

How to Find Google Sheets Linked to your Google Forms

by

Hey, friends today you will learn How to Find Google Sheets Linked to your Google Forms. How to find the destination Google Spreadsheet and Sheet that is storing responses of the current Google Form with Google Apps Script

How to Find Google Sheets Linked to your Google Forms

When a user submits your Google Form, the response can be saved as a new row in a Google Spreadsheet or saved as a new row in the Google Form itself. Multiple Google Forms can be linked to a single spreadsheet, and their responses will be saved in different sheets of the same spreadsheet.

If you have numerous Google Forms in your Drive that are writing answer data to the same Google Page, you can use Google Scripts to determine the name of the spreadsheet and the sheet where those forms’ responses are being stored.

To acquire the name of the linked sheet, open the Google Script editor, replace the formId with the Id of your Google Form, and run the script.

function getResponseSheetForGoogleForm() {
  const formId = "<<Google Form Id>>";

  // Open an existing Google Form by Id
  const form = FormApp.openById(formId);

  // Are the form responses stored in Google Sheets
  const destinationType = form.getDestinationType();

  if (destinationType !== FormApp.DestinationType.SPREADSHEET) {
    Logger.log("This form is not saving responses in Google Sheets");
  } else {
    // Get the Id of the response spreadsheet
    const destinationId = form.getDestinationId();

    // Open the Google Workbook and iterate through each sheet
    const formSpreadsheet = SpreadsheetApp.openById(destinationId);

    const [formSheet] = formSpreadsheet.getSheets().filter((sheet) => {
      // Returns the URL of the associated Google form
      // that is sending its user responses to this sheet
      const associatedFormUrl = sheet.getFormUrl();
      return associatedFormUrl && associatedFormUrl.indexOf(formId) !== -1;
    });

    Logger.log(`The form responses are stored in ${formSheet.getName()}`);
  }
}

You might also like our trending code snippets

Leave a Comment