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;
}

Answer 2

There is now a way to verify which sheet in a Google Sheets file with multiple linked forms corresponds to the current Form - through the use of Sheet#getFormUrl(), which was added to the Sheet class in 2017.

function getFormResponseSheet_(wkbkId, formUrl) {
  const matches = SpreadsheetApp.openById(wkbkId).getSheets().filter(
    function (sheet) {
      return sheet.getFormUrl() === formUrl;
    });
  return matches[0]; // a `Sheet` or `undefined`
}
function foo() {
  const form = FormApp.getActiveForm();
  const destSheet = getFormResponseSheet_(form.getDestinationId(), form.getPublishedUrl());
  if (!destSheet)
    throw new Error("No sheets in destination with form url '" + form.getPublishedUrl() + "'");
  // do stuff with the linked response destination sheet.
}

If you have unlinked the Form and the destination spreadsheet, then obviously you won't be able to use getDestinationId or getFormUrl.

Answer 3

I needed this also, and remarkably there is still no apps script method that facilitates it. In the end I set about finding a reliable way to determine the sheet id, and this is what I ended up with by way of programmatic workaround:

1. Add a temporary form item with a title that's a random string (or something similarly suitable)
2. Wait for the new corresponding column to be added to the destination sheet (typically takes a few seconds)
3. Look though each sheet in the destination until you find this new form item title string in a header row
4. Delete the temporary form item that was added
5. Wait for the corresponding column in the sheet to unlink from the form and become deletable (typically takes a few seconds)
6. Delete the column corresponding to the temporary form item
7. Return the sheet ID

I'm sure some won't like this approach because it modifies the form and spreadsheet, but it does work well.

With the necessary wait times included it takes about 12 seconds to perform all the look up / clean up operations.

Here's my code for this method in case anyone else might like to use it.

// Takes Apps Script 'Form' object as single paramater
// The second parameter 'obj', is for recursion (do not pass a second parameter)
// Return value is either: 
// - null (if the form is not linked to any spreadsheet)
// - sheetId [int]
// An error is thrown if the operations are taking too long

function getFormDestinationSheetId(form, obj) {

  var obj = obj || {}; // Initialise object to be passed between recursions of this function

  obj.attempts = (obj.attempts || 1);

  Logger.log('Attempt #' + obj.attempts);

  if (obj.attempts > 14) {
    throw 'Unable to determine destination sheet id, too many failed attempts, taking too long. Sorry!';
  }

  obj.spreadsheetId = obj.spreadsheetId || form.getDestinationId();

  if (!obj.spreadsheetId) {
    return null; // This means there actually is no spreadsheet destination set at all.

  } else {
    var tempFormItemTitle = '### IF YOU SEE THIS, PLEASE IGNORE! ###';

    if (!obj.tempFormItemId && !obj.sheetId) { // If the sheet id exists from a previous recusion, we're just in a clean up phase
      // Check that temp item does not already exist in form
      form.getItems(FormApp.ItemType.TEXT).map(function(textItem) {
        var textItemTitle = textItem.getTitle();
        Logger.log('Checking against form text item: ' + textItemTitle);
        if (textItemTitle === tempFormItemTitle) {
          obj.tempFormItemId = textItem.getId();
          Logger.log('Found matching form text item reusing item id: ' + obj.tempFormItemId);
        }
        return 0;
      }); // Note: Just using map as handy iterator, don't need to assign the output to anything

      if (!obj.tempFormItemId) {
        Logger.log('Adding temporary item to form');
        obj.tempFormItemId = form.addTextItem().setTitle(tempFormItemTitle).getId();
      }
    }

    obj.spreadsheet = obj.spreadsheet || SpreadsheetApp.openById(obj.spreadsheetId);
    obj.sheets = obj.sheets || obj.spreadsheet.getSheets();
    obj.sheetId = obj.sheetId || null;

    var sheetHeaderRow = null;

    for (var i = 0, x = obj.sheets.length; i < x; i++) {
      sheetHeaderRow = obj.sheets[i].getSheetValues(1, 1, 1, -1)[0];

      for (var j = 0, y = sheetHeaderRow.length; j < y; j++) {
        if (sheetHeaderRow[j] === tempFormItemTitle) {
          obj.sheetId = obj.sheets[i].getSheetId();
          Logger.log('Temporary item title found in header row of sheet id: ' + obj.sheetId);
          break;
        }
      }
      if (obj.sheetId) break;
    }

    // Time to start cleaning things up a bit!
    if (obj.sheetId) {

      if (obj.tempFormItemId) {
        try {
          form.deleteItem(form.getItemById(obj.tempFormItemId));
          obj.tempFormItemId = null;
          Logger.log('Successfully deleted temporary form item');
        } catch (e) {
          Logger.log('Tried to delete temporary form item, but it seems it was already deleted');
        }
      }

      if (obj.sheetId && !obj.tempFormItemId && !obj.tempColumnDeleted) {
        try {
          obj.sheets[i].deleteColumn(j + 1);
          obj.tempColumnDeleted = true;
          Logger.log('Successfully deleted temporary column');
        } catch (e) {
          Logger.log('Could not delete temporary column as it was still attached to the form');
        }
      }

      if (!obj.tempFormItemId && obj.tempColumnDeleted) {
        Logger.log('Completed!');
        return obj.sheetId;
      }
    }

    SpreadsheetApp.flush(); // Just in case this helps!

    // Normally this process takes three passes, and a delay of 4.5 secs seems to make it work in only 3 passes most of the time
    // Perhaps if many people are submitting forms/editing the spreadsheet, this delay would not be long enough, I don't know.
    obj.delay = ((obj.delay || 4500));

    // If this point is reached then we're not quite finished, so try again after a little delay
    Logger.log('Delay before trying again: ' + obj.delay / 1000 + ' secs');
    Utilities.sleep(obj.delay);
    obj.attempts++;

    return getFormDestinationSheetId(form, obj);
  }
}

Answer 4

To get the spreadsheet, once you have the DestinationID, use SpreadsheetApp.openById(). Once you have that, you can retrieve an array of sheets, and get the response sheet by index, regardless of its name.

var destId = FormApp.getActiveForm().getDestinationId();
var ss = SpreadsheetApp.openById(destId);
var respSheet = ss.getSheets()[0];  // Forms typically go into sheet 0.
...

From this point, you can manipulate the data in the spreadsheet using other Spreadsheet Service methods.

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... (but that doesn't match the spreadsheet)

You're right - the number of current items does not equal the number of columns in the spreadsheet. The number of columns each response takes up in the destination sheet includes any questions that have been deleted from the form, and excludes items that are not questions. Also, the order of the columns in the spreadsheet is the order that questions were created in - as you re-arrange your form or insert new questions, the spreadsheet column order does not reflect the new order.

Assuming that the only columns in the spreadsheet are from forms, here's how you could make use of them:

...
var data = respSheet.getDataRange().getValues(); // 2d array of form responses
var headers =  data[0];  // timestamp and all questions
var numColumns = headers.length;  // count headers
var numResponses = data.length - 1; // count responses

And your last point is correct, you need to correlate names.

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.