如何修改Google Sheets脚本将数据转换为字典数组格式？

Got it, let's sort this out for you. The problem right now is that your startSync() function is pulling the raw 2D array from your sheet and sending it straight to Firebase, but we need to transform that data into the array of objects format you're expecting.

Here's the updated startSync() function that will do exactly what you need:

function startSync() {
  // Get the currently active sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  // Get the number of rows and columns which contain some content
  var [rows, columns] = [sheet.getLastRow(), sheet.getLastColumn()];
  // Get the data contained in those rows and columns as a 2 dimensional array
  var rawData = sheet.getRange(1, 1, rows, columns).getValues();
  
  // Extract the header row (first row) to use as object keys
  var headers = rawData[0];
  
  // Transform the raw data into an array of objects
  var formattedData = rawData.slice(1).map(row => {
    var obj = {};
    headers.forEach((header, index) => {
      // Convert price to number (since Sheets might return it as a string depending on formatting)
      if (header === 'price') {
        obj[header] = Number(row[index]);
      } else {
        obj[header] = row[index];
      }
    });
    return obj;
  });
  
  // Send the formatted data to Firebase
  syncMasterSheet(formattedData);
}

Let's break down what's changed: #

• Raw Data Handling: I renamed the original data variable to rawData to keep things clear—this is still the 2D array pulled directly from the sheet.
• Header Extraction: We take the first row of rawData (rawData[0]) to use as the keys for our objects (like id, name, health, price).
• Data Transformation:
  • rawData.slice(1) skips the header row so we only process the actual data rows.
  • For each row, we create a new object, then loop through the headers to map each row value to the corresponding key.
  • We add a check for the price key to ensure it's converted to a number (since Google Sheets might return numeric values as strings in some cases, which matches your expected format where price is a number, not a string).
• Sync Formatted Data: Finally, we pass the formattedData array to syncMasterSheet() instead of the raw 2D array.

This will give you exactly the format you're looking for:

[{id: "abchdha", name: "Orange", health: "fruit", price: 50}, {id: "123fsf", name: "Apple", health: "fruit", price: 50}]

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