寻求将横向数据（表单提交内容）转置粘贴至另一工作表的脚本

Got it, let's tackle this problem—converting horizontal form submissions to a vertical layout in another sheet is a super common task, and scripting makes it way faster than doing it manually. Here are two tailored solutions depending on which spreadsheet tool you're using:

If you're using Microsoft Excel, this VBA macro will grab your horizontal form data, transpose it, and paste it vertically into your target sheet without overwriting existing content.

Step-by-step setup: #

1. Open your Excel workbook
2. Press Alt + F11 to open the VBA Editor
3. Right-click your workbook in the Project Explorer > Insert > Module
4. Paste the code below, then adjust the sheet names to match your file

Sub TransposeFormData()
    ' Define source and target worksheets
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Set sourceSheet = ThisWorkbook.Sheets("FormResponses") ' Replace with your source sheet name
    Set targetSheet = ThisWorkbook.Sheets("VerticalData") ' Replace with your target sheet name
    
    ' Grab the latest horizontal form row (adjust row number if needed)
    ' This example uses the last non-empty row in column A of the source sheet
    Dim sourceRow As Long
    sourceRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
    Dim sourceRange As Range
    Set sourceRange = sourceSheet.Range(sourceSheet.Cells(sourceRow, 1), sourceSheet.Cells(sourceRow, sourceSheet.Columns.Count).End(xlToLeft))
    
    ' Find the first empty row in the target sheet (column A)
    Dim targetStartRow As Long
    targetStartRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row + 1
    
    ' Transpose and paste values (avoids formatting issues)
    sourceRange.Copy
    targetSheet.Cells(targetStartRow, "A").PasteSpecial Paste:=xlPasteValues, Transpose:=True
    
    ' Clear clipboard to prevent lingering copy prompts
    Application.CutCopyMode = False
    
    MsgBox "Form data transposed successfully!", vbInformation
End Sub

Customization tips: #

• If your form data is always in a specific row (not the last one), replace sourceRow = sourceSheet.Cells(...) with a fixed number like sourceRow = 5
• To include formatting, change xlPasteValues to xlPasteAll

For Google Sheets users (especially those linking to Google Forms), this script will automate transposing form submissions to a vertical sheet, and you can even set it to run automatically when a new form is submitted.

Step-by-step setup: #

1. Open your Google Sheet
2. Go to Extensions > Apps Script
3. Delete any default code in the editor, then paste the code below
4. Adjust the sheet names to match your file, save the project, and run it once to grant permissions

function transposeFormSubmission() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName("Form Responses 1"); // Replace with your source sheet name
  const targetSheet = ss.getSheetByName("Vertical Responses"); // Replace with your target sheet name
  
  // Get the latest form submission row (last non-empty row in source sheet)
  const lastSourceRow = sourceSheet.getLastRow();
  const sourceData = sourceSheet.getRange(lastSourceRow, 1, 1, sourceSheet.getLastColumn()).getValues()[0];
  
  // Filter out empty cells (in case some form fields were left blank)
  const filteredData = sourceData.filter(value => value !== "");
  
  // Find the first empty row in the target sheet
  const lastTargetRow = targetSheet.getLastRow();
  const targetRange = targetSheet.getRange(lastTargetRow + 1, 1, filteredData.length, 1);
  
  // Convert horizontal array to vertical (each value in its own row)
  const verticalData = filteredData.map(value => [value]);
  
  // Write the transposed data to the target sheet
  targetRange.setValues(verticalData);
  
  SpreadsheetApp.getUi().alert("Form data transposed and saved!");
}

Auto-run on form submission: #

To make this fully automated:

1. In the Apps Script editor, click the clock icon (Triggers) in the left sidebar
2. Click "Add Trigger"
3. Set:
   • Choose which function to run: transposeFormSubmission
   • Choose which deployment to run: Head
   • Select event source: From spreadsheet
   • Select event type: On form submit
4. Click Save

内容的提问来源于stack exchange，提问作者Vendel Utto