google apps script - Identifying Form destination (Spreadsheet AND SHEET)

Question

I'm working on a script that interacts with Google Form' response sheet.

FormApp.getActiveForm().getDestinationId()

give me the spreadsheet id, but I don't find a way to get the sheet itself. User can change its name and position, so I need to get its id, like in

Sheet.getSheetId()

I also have to determine the number of columns the responses uses. It's not equal to the number of questions in the form. I can count the number of items in the form:

Form.getItems().length

and then search for gridItems, add the number of rows in each and add them minus one:

+ gridItem.getRows().length - 1

Finally, I think there's no way to relate each question with each column in the sheet, but by comparing somehow columns names with items title.

Thank you

Answer 1

@tehhowch came very close to the correct answer, but there is a problem with the code: there is no guarantee that form.getPublishedUrl() and sheet.getFormUrl() will return exactly the same string. In my case, form.getPublishedUrl() returned a URL formed as https://docs.google.com/forms/d/e/{id}/viewform and sheet.getFormUrl() returned https://docs.google.com/forms/d/{id}/viewform. Since the form id is part of the URL, a more robust implementation would be:

function get_form_destination_sheet(form) {
    const form_id = form.getId();
    const destination_id = form.getDestinationId();
    if (destination_id) {
        const spreadsheet = SpreadsheetApp.openById(destination_id);
        const matches = spreadsheet.getSheets().filter(function (sheet) {
            const url = sheet.getFormUrl();
            return url && url.indexOf(form_id) > -1;
        });
        return matches.length > 0 ? matches[0] : null; 
    }
    return null;
}