How can I convert a Google Form responses header row into rows and export it as a PDF?

Below is one approach using Apps Script that reads the header row from your form responses, transposes it (so the header appears as a column rather than a row), writes it to a newly created spreadsheet, and then exports that new spreadsheet as a PDF. You can schedule this script (for example, via a time‐based trigger or on form submit) so that whenever new responses appear (and new header columns are added) the new spreadsheet is refreshed.

Below is an example script:

---

JSCopied
function createPDFWithTransposedHeader() {
  // Replace with your form responses spreadsheet ID
  var sourceSpreadsheetId = 'SOURCE_SPREADSHEET_ID';
  var sourceSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetId);
  var sourceSheet = sourceSpreadsheet.getSheets()[0];

  // Read the header row (assumes headers are in the first row)
  var lastColumn = sourceSheet.getLastColumn();
  var headerRow = sourceSheet.getRange(1, 1, 1, lastColumn).getValues()[0];

  // Transpose the header row into a column array (each header stays as a single element in an array)
  var transposedHeader = headerRow.map(function(headerItem) {
    return [headerItem];
  });

  // Create a new spreadsheet where the transposed header will be written
  var newSpreadsheet = SpreadsheetApp.create("Transposed Header");
  var newSheet = newSpreadsheet.getSheets()[0];

  // Write the transposed header to the new sheet (starting at cell A1)
  newSheet.getRange(1, 1, transposedHeader.length, 1).setValues(transposedHeader);

  // Export the new spreadsheet as a PDF
  var pdfBlob = DriveApp.getFileById(newSpreadsheet.getId()).getAs('application/pdf')
                    .setName("Transposed_Header.pdf");
  // (Optional) Save the PDF file in Drive
  var pdfFile = DriveApp.createFile(pdfBlob);
  
  // Log the URL to the PDF file (or email/sharing as needed)
  Logger.log("PDF created: " + pdfFile.getUrl());
}

---

How It Works

1. Load the Source Spreadsheet:
   The script opens your existing Google Form responses spreadsheet by its ID.

2. Read & Transpose the Header Row:
   It reads the first row (headers) and then uses the JavaScript map function to transform the one-dimensional array into a two-dimensional array where each header becomes its own row (i.e. column turned into rows).

3. Create a New Spreadsheet:
   A new spreadsheet is created and the transposed headers are written into its first sheet.

4. Export as PDF:
   The new spreadsheet file is retrieved from Drive and converted to a PDF blob using the getAs('application/pdf') method. Then, the script saves the PDF file to your Drive, logging the URL so you can access it.

Updating for New Responses

Since your form responses may change over time (with new columns added), you can:

• Schedule the Script: Use a time-driven trigger (for example, run the script daily) so that your new spreadsheet (and PDF) always reflects the current headers.
• On Form Submit Trigger: Alternatively, set up an onFormSubmit trigger if you want the update immediately when new responses are received.

This script can serve as a starting point. You might also want to incorporate error checking, formatting adjustments, or even overwrite/update an existing spreadsheet/PDF so that you always have a single up-to-date version.

Feel free to ask if you need further clarification!