Error while trying to make a code on adding multiple google sheets from a folder into a new single google sheet.

[Fakir Fashion]

Hi,

I am to totally new in coding and tried to make an exact code after watching a YouTube video.

But I am stuck in middle and can't find the mistake I made. It's about collecting data from multiple google sheet saved in one folder into a new google sheet.

My code.gs info:

/ source Folder

const folderId = '1ytmDneYZHgFnLlKo8zSlZfzEHAKwEQZo';

 

//destination

const destId = '1xMuT_5ea44b_wv_r129-kLyZ9-fbQaJPg2hjPIA84nU';

const sheet1Id = 0;

function main() {

  var folderIds = folderExtractor(folderId);

 

  var payload = [];

  for(i in folderIds) {

    var sheet = getSheetById(folderIds[i], sheet1Id);

Info of my another script name getSheetById.gs:

function getSheetById(wb_id, sheet_id) {

  var wb = SpreadsheetApp.openById(wb_id);

  var sheets = wb.getSheets();

  for(i in sheets) {

    if(sheets[i].getSheetId == sheet_id) {

      var sheetName = sheets[i].getSheetName();

    }

  }

When I run a Logger.log(sheet); at code.gs I am getting following error:

Error: Unexpected error while getting the method or property valueOf on object function () { [native code] }.

getSheetById

@ getSheetById.gs:6

main

@ Code.gs:14

Can someone help please.

[George Ghanem]

You are missing some parentheses.

This line:

sheets[i].getSheetId ==

Change it to:

sheets[i].getSheetId() ==

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/5c4e23f9-0eeb-401d-8eac-990b3c5cfcbbn%40googlegroups.com.

[Kildere S Irineu]

❌ O erro:

Na função getSheetById, você escreveu:

if (sheets[i].getSheetId == sheet_id) {

Mas getSheetId é um método, não uma propriedade — ou seja, falta os parênteses () para chamá-lo.

---

✅ Correção:

function getSheetById(wb_id, sheet_id) {
  var wb = SpreadsheetApp.openById(wb_id);
  var sheets = wb.getSheets

();

  for (var i in sheets) {
    if (sheets[i].getSheetId() == sheet_id) {  // <-- parênteses aqui

      var sheetName = sheets[i].getSheetName

();
      return wb.getSheetByName(sheetName);     // retorna o objeto Sheet
    }
  }

  throw new Error('Sheet ID ' + sheet_id + ' not found in workbook ' + wb_id);
}

---

🧠 Explicação:

• getSheetId() é um método do objeto Sheet (documentação oficial).

• Quando você usou getSheetId sem os parênteses, o Apps Script tentou comparar a função nativa em si (um objeto Function), e isso causou o erro:

• Unexpected error while getting the method or property valueOf on object function () { [native code] }
  

• — basicamente, ele não conseguiu converter a função em número.

---

🔧 Extra: boas práticas

1. Sempre use for (var i = 0; i < sheets.length; i++) em vez de for (i in sheets) — o for..in percorre também propriedades herdadas, o que pode causar erros inesperados.

2. Adicione Logger.log() dentro do loop para depurar:

   Logger.log('Checking sheet: ' + sheets[i].getName() + ' (' + sheets[i].getSheetId() + ')');
   

3. Retorne o próprio Sheet (como no exemplo corrigido), não apenas o nome — isso facilita manipular os dados diretamente.

---

💡 Exemplo completo e funcional:

// Folder com as planilhas de origem
const folderId = '1ytmDneYZHgFnLlKo8zSlZfzEHAKwEQZo';

// Planilha de destino

const destId = '1xMuT_5ea44b_wv_r129-kLyZ9-fbQaJPg2hjPIA84nU';
const sheet1Id = 0;

function main(

) {
  const folder = DriveApp.getFolderById(folderId);
  const files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
  const destSS = SpreadsheetApp.openById(destId);
  const destSheet = destSS.getSheets()[0];

  while (files.hasNext()) {
    const file = files.next();
    const srcSS = SpreadsheetApp.openById(file.getId());
    const sheet = getSheetById(srcSS.getId(), sheet1Id);
    const data = sheet.getDataRange().getValues();

    destSheet.getRange(destSheet.getLastRow() + 1, 1, data.length, data[0].length).setValues(data);
  }
}

function getSheetById(wb_id, sheet_id) {
  const wb = SpreadsheetApp.openById(wb_id);
  const sheets = wb.getSheets();

  for (let i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetId() == sheet_id) {
      return sheets[i];
    }
  }

  throw new Error('Sheet ID ' + sheet_id + ' not found in ' + wb_id);
}

--

[Martin Molloy]

Hi 

The problem with this script is that you are using 0 as the sheetId for the tab in each spreadsheet. The sheetId for a tab within a spreadsheet will be an 11 digit number - if you open a spreadsheet in your browser you can see the sheetID it is shown as eg gid=12345678901  at the end of the URL.

I assume what you are trying to do is get the 1st tab within the spreadsheet. You can get that by selecting the sheet from within the spreadsheet's set of sheets using its index number - which is 0 for the first sheet. You are also not returning the sheet to the main script

So that means that you need to change your function from 

function getSheetById(wb_id, sheet_id) {

  var wb = SpreadsheetApp.openById(wb_id);

  var sheets = wb.getSheets();

  for(i in sheets) {

    if(sheets[i].getSheetId == sheet_id) {

      var sheetName = sheets[i].getSheetName();

    }

  }

to

function getSheetById(wb_id, sheet_id) {

  var wb = SpreadsheetApp.openById(wb_id);

  var sheets = wb.getSheets();

var sheet = sheets[0]

console.log('Sheetname is: ', sheet.getSheetName()

return sheet

  }

I put the console log message in so that you can see which sheet is being returned

Hope that helps

--