Script fails to get Google Sheet Name

[David Crowley]

My script is unable to read the name of my Google Sheet, "Independent Residents"

I've set the breakpoint to   const data = sheet.getDataRange().getValues();

My script and the debugger output are attached.

Thanks,

Dave Crowley,

St. Louis, MO

[Brett Grear]

Can you provide your full code? The data variable that you showed returns an any [][], not a sheet object.  What is it you're trying to do?

[David Crowley]

Here it is

[Brett Grear]

There's nothing outwardly wrong with your code when you run it. What era message do you get? The stack Trace that you sent isn't particularly useful. It just shows the current state of the variables, but you should have got some sort of error message or success message in the console at the bottom when you run it. I would suspect based on nothing other than what you've given so far that the sheet name may not be exactly spelled the same as what you've put in the variable name. But again, that's not based on anything other than what you've said so far.

[Keith Andersen]

I tested your code, it successfully accessed the sheet....as for reading the data, perhaps there's a problem with the data range read?

And as Brett said - make sure your tab name is exactly as it is in the code.make sure no trailing space too. 

--
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/846f1e9c-5857-41e3-908b-19465d3c42f8n%40googlegroups.com.

--

My Site: https://www.sites.google.com/view/klaweb/

Passions: God, Family, Friends, Scripture, Data Management, Google Sheets + App Script, MS Access, Programing, sharing and much more.

[David Crowley]

You mean it needs the name of the tab?

Sent from my iPhone ran it needs the tab name

On Nov 25, 2025, at 2:49 PM, Keith Andersen <contact...@gmail.com> wrote:



To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BFkY-N8gUnm8pfRhEm-7PbLtXaqWHtbxUHwXmh%3DhtWdwg%40mail.gmail.com.

[Brett Grear]

Your script is looking for a sheet (tab) within the active spreadsheet that has the exact name of
"Independent Residents"

[Keith Andersen]

Your code is designed to read data from tab named - Independent Residents. If no tab has that name - no data. 

Also, it requires you to be ON the Independent Residents tab to read data ON the Independent Residents tab.

What exactly do you need the code to do?

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/D24971A0-E56F-4C2C-972A-12B3292C6403%40gmail.com.

[David Crowley]

Yes, I'm on the tab named Independent Residents.

Debug log attached.

Thanks, again

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BHdAYUSfu-G%2BQ81LL6k4am_zWSjG_SBfLEfzLZjE%2By4%2BA%40mail.gmail.com.

[Keith Andersen]

There's no debugging. You simply have it executing and stopping on get values.

What does the Independent Residents sheet look like? What is the actual data range? 

It doesn't seem to find a bonafide data range to get values from.

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAO9qt%2BfcAjTHPuLPJT5gjnTc24pna%2B-fLAfSpjbTnacceNTKNA%40mail.gmail.com.

[Brett Grear]

Get rid of the break point and run again. Then send the execution log screenshot again. Your execution hasn't finished when you took the screenshot

[David Crowley]

Here's a sample sheet with fake residents' names to protect their privacy.

You have my script.

Many thanks again

Dave C

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/7cf7d371-d28f-4e1e-86c0-65600c7fb17en%40googlegroups.com.

[Keith Andersen]

Why would you share an xlsx file and not a Google Sheet? 

Create a Google Sheet set up exactly as the sheet you are having problems with.

Did you check if the tab name is exactly spelled as in the code? No extra spaces?

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAO9qt%2BcET88%3Dy3ihP1mzMOQB6es8pqsa0U2pmfTJnE4WjRRrjw%40mail.gmail.com.

[Brett Grear]

Although if you are using excel that would explain why your code doesn't work 

[Keith Andersen]

I converted the xlsx to Google sheet, changed the tab name to Independent Residents and inserted your code - it did get the data range values correctly.

If you want your sheet debugged / fixed - private share it with me.

Keith

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/4c9db160-3684-4746-9b90-46a0af7c92d1n%40googlegroups.com.

[David Crowley]

I'm a neophyte with google scripts and sheets. I'll see if I can share the Independent Resident sheet privately with 

 <contact...@gmail.com>  a bit later. Privacy of the resident's info is a huge concern for me.

Thank you,

Dave Crowley

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BHx5EBJPzXYSTRVVW044hOGeb8xhNDYKnxtRGVNxiYnQg%40mail.gmail.com.

[Keith Andersen]

See my site:

sites.google.com/view/klaweb

To offset the trust issue. But I get it.

Cheers

Keith 

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAO9qt%2BdUBKx8vGjw1uWfe%2Bjmj_u%3DT8EBD7FwX3KqJZpDdfQmrw%40mail.gmail.com.

[David Crowley]

So far, so good. The script reads the Dummy Residents sheet and reports the number of columns and rows correctly in debug mode.

It does not provide a link to the output doc. In debug mode, the script never executes the final instruction:  doc.saveAndClose();

[Brett Grear]

Doc.saveAndClose() just pushes batched changes through to the doc so it's visible to the user. I didn't see any console log command to display the doc link. 

[SMAARTE Group]

Happy Thanksgiving (or other holiday you might celebrate).

A reminder that Google provides 5 free Gemini 3.0 Pro Thinking queries a day and anyone who pays for premium gets 100 queries a day.  If uninterested in Google's Gemini, try ChatGPT, Claude or another model of your liking.  No matter the query, AI almost always has answers for any question or problem posted to this Google Group about Google Apps Script coding.  Leveraging AI for answers would greatly reduce back-and-forth discussion and concerns over data privacy.

Folks who pay for Google Workspace Business Standard ($14/mo + tax) have access to 100 queries a day. Google does not use any of the information submitted to Gemini in Workspace to improve its models: the information is confidential.

Regards,

Steve Horvath

SMAARTE Group

📞 802-444-1347

📅 Book: Initial Consult

[Brett Grear]

😂 especially when chat GPT wrote the script in the first place

[David Crowley]

Brett:

What is the command to display the link to the output doc?

Thanks,

Dave C.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/91e29437-c2d8-4092-a586-88dfdd179538n%40googlegroups.com.

[Brett Grear]

After the saveAndClose line try

console.log(doc.getUrl())

[David Crowley]

Works fine now. Many thanks!!!

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/3773e74f-85cb-4e51-99df-31103c6078f4n%40googlegroups.com.

[Kildere S Irineu]

RESOLUTIVE TECHNICAL OPINION – Integrated Analysis of All Provided Files (English Version)

A full analysis of the uploaded files (PDFs, JSON datasets, Google Workspace developer content, and extracted documentation) reveals a broad and coherent set of materials. Together, they form a complete reference framework for building, debugging, and automating systems using Google Apps Script integrated with Google Workspace services.

The following is the consolidated technical opinion.

---

1. Primary Findings

1.1. Nature of the Provided Content

The files include:

• Extensive official documentation for Google Workspace services:
  Forms, Sheets, Docs, Drive, Slides, Calendar, Admin Console, Reports API, etc.
  (e.g., , , , , , )

• Excerpts from the Apps Script textbook “Understanding Google Apps Script”
  (e.g., )

• Real-world problem discussions from the Google Groups developer community
  (e.g., spreadsheet name detection failure by Dave Crowley)
  

• Automatically extracted metadata and indexes (not operationally relevant).
  

1.2. Overall Content Categories Identified

• Official workspace documentation (2024–2025 updated).

• Practical implementation examples.

• Detailed guidance on:

  • APIs

  • Triggers

  • Web Apps

  • HTMLService UIs

  • File automation

  • Admin domain reporting

  • Enterprise-level automation practices

---

2. Technical Assessment

The materials collectively support the creation of a complete automation and integration platform based on Google Apps Script.

2.1. Full Project Capability

Using the provided content, you can implement:

Workspace Automations

• Sheets: Reading, writing, auditing, triggers.

• Forms: Dynamic creation, validation, automated processing.

• Docs: Template generation, merge automation.

• Drive: File manipulation, bulk processing, permission auditing.

• Slides: Automated slide creation and layout changes.

• Calendar: Event creation, synchronization workflows.

• Admin Console & Reports API:

  • User listing, login audit logs, system-wide reporting.

Apps Script Engineering

• Web Apps with HTML/CSS/JS.

• Sidebar and dialog UIs.

• Event-driven automation (installable triggers).

• Modular code architecture.

2.2. Best Practices Evident in Documents

• Modularization of logic.

• Separation of front-end and server code.

• Logging via Stackdriver / console.

• Use of advanced services and APIs.

• Secure, scalable design for enterprise workflows.

2.3. Identified Limitations

• Large volumes of duplicated or metadata-only extracted content.

• Material is reference-oriented, not structured as ready-to-use applications.

---

3. Resolutive Recommendations (Action Plan)

---

✔️ 3.1. Build a Unified Internal Documentation Repository

Organize all extracted content into a structured guide divided by services:

1. Sheets

2. Forms

3. Docs

4. Drive

5. Slides

6. Calendar

7. Admin Console / Reports

8. UI Development (HTMLService + CardService)

9. Web Apps

10. Triggers & Security

This will become the canonical reference manual for your Apps Script development.

---

✔️ 3.2. Adopt a Standardized Project Architecture

Recommended directory structure:

/config
/core
/services
/ui
/utils

Benefits:

• Improved maintainability

• Reusable components

• Cleaner debugging

• Standardization across multiple scripts

---

✔️ 3.3. Build an Internal Function Library

Core utilities recommended for your environment:

Sheets Utilities

function readSheet(name) {
  return SpreadsheetApp.getActive().getSheetByName(name);
}

function getData(sheet) {
  return sheet.getDataRange().getValues();
}

Docs Template Generator

function createDocFromTemplate(id, data) {
  const copyId = DriveApp.getFileById(id).makeCopy().getId();
  const body = DocumentApp.openById(copyId).getBody();
  
  Object.entries(data).forEach(([k, v]) => {
    body.replaceText(`{{${k}}}`, v);
  });

  return copyId;
}

Drive Audit Utility

function auditSharing(fileId) {
  const file = DriveApp.getFileById(fileId);
  return file.getSharingAccess() + ' / ' + file.getSharingPermission();
}

Calendar Event Creation

function createEvent(calendarId, event) {
  return CalendarApp.getCalendarById(calendarId)
    .createEvent(event.title, event.start, event.end);
}

---

✔️ 3.4. Establish a Proper QA & Debugging Workflow

Based on the debugging guidelines found in the Apps Script book ():

• Mandatory try/catch blocks.

• Structured logs using console.log().

• Stackdriver error monitoring for production environments.

• Breakpoint-based debugging during development.

---

✔️ 3.5. Build a Catalog of Automations

Based on the topics covered in the documentation:

High-value automations:

• Forms → Sheets → Docs/Drive workflows.

• Automated slide deck generation.

• Drive permission audits (security).

• Calendar event creation from submissions.

• Admin domain usage reporting (via Reports API).

• Bulk file manipulation and template cloning.

---

4. Conclusion

The collection of files constitutes a complete technical ecosystem for professional Apps Script development.

The materials:

• Enable development of enterprise-grade automation.

• Cover all major Workspace services.

• Include examples, reference documentation, and real-world troubleshooting.

• Provide the foundation for scalable and secure automation workflows.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAO9qt%2Be%3DhpGM-9q8R2OuPAFC55%2BaVJimt8%3DkrOaNwEjUoU7CpQ%40mail.gmail.com.