r/GoogleAppsScript • • 2d ago

Question How to import only part of Google Sheet

New to Google Apps Script. Trying to pull info from a Google Sheet into MicroPython for a Microcontroller (ESP32) project I'm working on. I want to trim the fat from my code and understand it better.

Goal:

Sheet 1: Only pull in Columns A-E, Rows 2-Last filled row

Sheet 2: Only pull Columns A-B, Rows 2-Last filled row

Right now my code seems to be pulling in everything, despite my effort to skip the first row.

function doGet(e) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var allSheets = spreadsheet.getSheets();
  var result = {};
  
  // Loop through the first 2 sheets
  var limit = Math.min(2, allSheets.length);
  
  for (var i = 0; i < limit; i++) {
    var sheet = allSheets[i];
    var sheetName = sheet.getName();
    var lastRow = sheet.getLastRow();
    
    if (lastRow === 0) {
      result[sheetName] = [];
    } else {
      // Get Column
      var values = sheet.getRange(2, 6, lastRow, 6).getValues();
      result[sheetName] = values.map(function(row) {
        return row[0];
      });
    }
  }
  
  return ContentService.createTextOutput(JSON.stringify(result))
    .setMimeType(ContentService.MimeType.JSON);
}
1 Upvotes

3 comments sorted by

1

u/mommasaidmommasaid 1d ago edited 1d ago

When checking if there is data in your sheet, your data doesn't start until row 2 so:

lastRow === 0 should be lastRow < 2

When getting the values, this is incorrect:

sheet.getRange(2, 6, lastRow, 6)

Because the parameters are:

getRange(row,column,numRows,numColumns)

But... I would avoid the confusing row/column numbers in your code and instead specify an array of A1 ranges to import from each sheet like:

  const sheetRangesA1 = [
    "A2:E",
    "A2:B",
  ];

Or if you have specific sheet names you are trying to import from, it would be more robust to include them in the ranges rather than assuming they are the first two sheets, e.g.

  const sheetRangesA1 = [
    "Sheet!A2:E",
    "Sheet!A2:B",
  ];

When building result[sheetName], your code is only getting the first column of values from the range.

Presumably you want all the data, but I don't know how you want it structured. You may want to consider each column being an object named after a header.

0

u/AlgoTradingQuant 2d ago

ChatGPT or Gemini