使用Google Script将列内容转换为行的问题求助

Hey there! Let's work through your Google Script problem step by step. You're trying to convert a column of data into a row, but your current code isn't producing any results—let's break down the issues and fix them.

Issues in Your Original Code #

• You're opening the same Spreadsheet twice (ss and s use the same ID) which is redundant and unnecessary.
• Your for loop is incomplete (values.leng...), which would prevent the script from executing the core conversion logic.
• Most importantly: You never write the processed data to your target sheet, so even if the logic worked, you wouldn't see any output.

Corrected Script #

Here's a revised version that will convert your column to a row and write the result to your target sheet:

function convertColumnToRow() {
  // Open your spreadsheet once (replace "XXXX" with your actual spreadsheet ID)
  const ss = SpreadsheetApp.openById("XXXX");
  
  // Get source and target sheets
  const sourceSheet = ss.getSheetByName('iGV');
  const targetSheet = ss.getSheetByName('Sheet1');

  // Fetch the column data (F11:F17)
  const columnValues = sourceSheet.getRange("F11:F17").getValues();
  
  // Convert the column (2D array) to a row
  // SpreadsheetApp.transpose() swaps rows and columns automatically
  const rowValues = SpreadsheetApp.transpose(columnValues);

  // Write the converted row to the target sheet (starting at A1 here)
  targetSheet.getRange(1, 1, rowValues.length, rowValues[0].length).setValues(rowValues);
}

How This Works #

1. Single Spreadsheet Reference: We only open the spreadsheet once to avoid redundant operations.
2. Data Fetch: getValues() returns a 2D array for your column (e.g., [["val1"], ["val2"], ...]).
3. Column-to-Row Conversion: SpreadsheetApp.transpose() takes your column array and converts it to a row array ([["val1", "val2", ...]]), which is the format needed for writing a row.
4. Write Data: setValues() writes the converted row to your target sheet. We specify the starting cell (A1 in this example) and match the range size to the converted data to ensure proper placement.

Alternative Manual Conversion (No Transpose) #

If you prefer to handle the array conversion manually instead of using transpose(), you can flatten the column array like this:

// Manually flatten the 2D column array into a 1D row array
const rowValues = [columnValues.flat()];

This achieves the same result—converting [["a"], ["b"]] to [["a", "b"]].

Quick Notes #

• Double-check that your spreadsheet ID, sheet names (iGV and Sheet1), and source range (F11:F17) are all correct.
• When you run the script for the first time, you'll need to grant the necessary permissions for it to access your spreadsheet.

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