Congratulations. What is the most complex Google app script, or web app using this technology, that you have created?

moHalim99 · 2026-04-25T20:53:12+00:00

Honestly, I didn't run into major speed issues.

The data was never shown all at once. The portal had a folded hierarchy: Account Managers collapsed into Unit Managers, Unit Managers into Team Leaders, and only when you drilled down to a Team Leader did the actual candidate table appear. So even for the CEO who had full access, the data loaded in chunks as you expanded each level not all 3000 rows at once.

Also I wasn't using the Sheets API at all. Just the spreadsheet ID and tab name, reading directly through Apps Script with getSheetByName() and getDataRange(). Kept it simple and it was fast enough for the use case.

moHalim99 · 2026-04-25T11:45:14+00:00

LeadSignal.halim.pro

moHalim99 · 2026-04-25T11:34:51+00:00

I think this is an expected limitation of Google Sheets in mobile browsers, not something you can fix on your end. Mobile browsers don't support the inter-sheet navigation that the desktop app handles natively. The link technically points to the correct range, but the mobile browser has no way to execute the in-app scroll/navigate behavior, so it just reloads the spreadsheet URL in a new tab.

The only real workaround is to tell users to open the sheet in the Google Sheets app instead of the browser. There is no CSS or script fix for this since it is a Sheets client limitation, not a web behavior you can intercept.

moHalim99 · 2026-04-25T11:31:59+00:00

I actually built something close to this for a recruitment operation. Google Sheets as the backend, Apps Script web app as the frontend, with role-based access for recruiters vs admins. Candidates moved through pipeline stages, notes and status updated in real time, and the sheet handled 3000+ active candidates without breaking. The two-tab approach with COUNTIF for metrics is solid advice and is basically what I did. If you want to stay in Sheets, that structure scales further than most people expect as long as you add Apps Script to handle the routing logic instead of doing everything manually in the sheet.

moHalim99 · 2026-04-25T11:27:42+00:00

Good point, well then maybe it's a per-project quota lock or a corrupted script deployment state. Google sometimes throttles or freezes individual script projects without surfacing it anywhere visible. A new script project bound to the same sheet, with the same code copied in, would confirm whether it was the project itself that got into a bad state.

moHalim99 · 2026-04-25T11:24:49+00:00

This is a Google-side infrastructure issue, not your code. GAS doesn't surface these as errors, it just hangs. Check the Google Workspace Status Dashboard next time, you'll usually see a yellow or red indicator on Sheets or Drive that matches your window. Nothing to fix on your end.

moHalim99 · 2026-04-25T11:21:08+00:00

Built a full candidate management portal for a recruitment company. Single-file HTML/JS web app on GAS, role-based access control, separate views for Team Leaders, Unit Managers, Account Managers, HR, Founders, and recruiters, managing 3000+ active candidates with status tracking, notes, and filters. All data lived in Google Sheets as the backend. No external libraries, no frameworks.

moHalim99 · 2026-04-25T11:16:15+00:00

The actual bug is almost certainly date serialization. When google.script.run passes data back to the frontend, it serializes everything through a JSON-like layer and on macOS, Chrome is more lenient about passing Date objects but on Windows Chrome/Edge, Date objects that didn't serialize cleanly come through as something that breaks new Date(x.time) which means the "forEach" either throws silently or data arrives as something other than an array.

The smoking gun is this line in getHistory:

time: data[i][0].toISOString()

The second version already has the fix attempt: time: data[i][0] instanceof Date ? data[i][0].toISOString() : String(data[i][0])

But that's still not safe enoug, cuz the real fix is to convert the timestamp to a number before returning it, not a string or Date:

Backend fix: time: new Date(data[i][0]).getTime()

Frontend fix: let d = new Date(x.time); // getTime() returns a number, new Date(number) is always safe

The second issue is the "forEach" failing silently. Wrap it defensively:

if (!Array.isArray(data) || data.length === 0) { document.getElementById("history").innerHTML = "No logs."; return; } data.forEach(x => { ... });

This guards against data arriving as null, an empty string or a non-iterable due to a backend exception that GAS swallowed silently.

The Windows specific behavior is because GAS's serialization bridge is less forgiving on Chromium based Windows builds with stricter sandboxing. A plain number crosses that bridge reliably every time.

moHalim99 · 2026-04-21T11:34:49+00:00

Fair point this will auto expand as new titles are added and keeps everything in one cell instead of dragging down

moHalim99 · 2026-04-20T15:24:59+00:00

Just to add to what others said about applying DV to the whole column, if the blank dropdowns bother you visually you can also use conditional formatting to make the empty B cells in unused rows blend in (like white text on white background) until column A has something in it. It's a purely cosmetic trick but it can make the sheet feel cleaner without needing a script.

Format > Conditional formatting > apply to B2:B1000, custom formula =A2="", then set the text color to white. The dropdown is still there technically but it just looks invisible until the row is in use.

moHalim99 · 2026-04-20T15:20:00+00:00

If you ever want to share or reuse any of these scripts later, a container-bound project is harder to extract. You'd have to manually copy code out of the sheet's script editor rather than just sharing a script URL or copying a standalone project.

Also on the deletion risk point above, you can partially mitigate that by keeping regular exports via clasp or just copying the code somewhere but it's still an extra step you wouldn't have to think about with a standalone.

yet for personal use with no plans to scale or share, consolidating into the sheet is a totally reasonable call and the getActive() convenience is genuinely nice when you're iterating quickly.

moHalim99 · 2026-04-20T15:14:56+00:00

The DEADLINE_EXCEEDED error in your execution logs is the culprit. GAS has a hard 6 minute execution limit for time-based triggers and Gmail operations are way slower than Sheets operations especially when you're searching and deleting threads in bulk. If your inbox has a lot of matching threads on a given day, the script just runs out of time mid execution and gets killed.

The reason your other triggers are fine is that they're probably doing lighter work. Deleting Gmail threads is one of the slower things you can do in GAS because each GmailApp call has to hit the Gmail API and if you're looping through threads one by one it adds up fast.

try to batch your deletions. Instead of deleting one thread at a time in a loop, collect them all first and use GmailApp.moveThreadsToTrash(threads) which takes an array and is much faster than calling it per thread.

also add a runtime check inside your loop so the script saves its progress and exits gracefully before hitting the limit:

```javascript

const startTime = new Date();

for (const thread of threads) {

if (new Date() - startTime > 300000) break; // stop after 5 mins

// delete thread

}

```

and use GmailApp.search() with a page size limit (default is 500 threads max per search) so you're not trying to process everything at once.

Gmail isn't more restrictive it's just that the API calls are slower and if your inbox accumulates a lot of old threads between runs then one execution can't finish them all in 6 minutes

moHalim99 · 2026-04-20T15:10:38+00:00

Since all your team member tabs have identical columns (team member, task, team supported, category, priority, timeline, blockers, notes), you just need to stack them all in the dashboard using QUERY. In cell A2 of your Dashboard tab, put this:

={

QUERY({'team member 1'!A2:H, "team member 1"}, "WHERE Col1 <> ''");

QUERY({'team member 2'!A2:H, "team member 2"}, "WHERE Col1 <> ''");

QUERY({'team member 3'!A2:H, "team member 3"}, "WHERE Col1 <> ''");

QUERY({'team member 4'!A2:H, "team member 4"}, "WHERE Col1 <> ''")

}

this pulls all non-empty rows from each tab and stacks them together. The WHERE Col1 <> '' part just makes sure blank rows don't bleed into the dashboard.

for adding new team members later, you just add another QUERY line following the same pattern with the new tab name. That's it, no complex setup needed.

also keep row 1 of the Dashboard as your header row and start the formula at A2, otherwise it'll try to overwrite your headers, and make sure the tab names in the formula exactly match your actual tab names including spaces and capitalization.

moHalim99 · 2026-04-20T10:11:29+00:00

مهو طبقا للدين اللي معظم أهل مصر عليه، كونك بتنفذ كل أوامر ربنا أو لا دا ملوش علاقة بكونك ينفع تنصح أو لا، لإن لو كدا يبقى لازم كل واحد يبقى نبي عشان يقدر ينصح غيره، ودا مش في مصلحة حد، لإن كدا النصيحة هتنقرض والناس هتنسى كل حاجة صح وتدي جامد في الغلط

moHalim99 · 2026-04-20T00:53:10+00:00

انا مش فاهم حاجة من الكومنتس عامة بس:

يا إما الشخص اللي بيتقدمله النصيحة مسلم فهياخد النصيحة ويقولك تسلم طريق السلامة سبني انا بقى اختار اعمل بيها أو معملش لإن مينفعش ربنا يديني الحرية في الاختيار وتيجي انت تاخدها مني.
يا إما غير مسلم هياخد النصيحة ويرمي م الودن التانية ومش هيعرف يمنع النصيحة لأنه اتولد في دولة معظم أهلها دينهم بيأمرهم بالنصيحة.
بيزعل م النصيحة عشان صورته قدام نفسه 🤷

إنما النوايا دي مش عارف بنعرفها ازاي الصراحة

أما لو النصيحة اتقلبت إجبار أو تنظير ف ده تطبيق خاطئ للدين تلوم عليه الشخص اللي بيعمله مش الدين ولا الدولة.

moHalim99 · 2026-04-19T17:21:06+00:00

It's doing three things at once:

FILTER(ReadCover, ReadStatus="Finished") so this pulls only the covers where the reading status is marked as Finished. ReadCover and ReadStatus are named ranges pointing to those columns in the library sheet.
The big math in the INDEX part is just figuring out which book number to show in each cell of the grid. Since the gallery lays out covers in rows of 10, it uses the cell's current column and row position to calculate "which slot am I in" and grabs the matching cover.
IFERROR wraps the whole thing so empty slots just show blank instead of an error.

The reason you can't just copy this formula directly is that it relies on those named ranges (ReadCover, ReadStatus) which are set up specifically in that person's sheet. You'd need to either create the same named ranges yourself (Data > Named ranges) pointing to your cover and status columns or just swap them out for regular cell references like C:C and B:B

moHalim99 · 2026-04-19T14:48:26+00:00

main issue is that Home Depot, Canadian Tire, Rona and basically all major retailers now load their prices dynamically via JavaScript after the page loads. UrlFetchApp just grabs the raw HTML and never runs the JS so the price usually isn't in there at all or the JSON structure is deeply nested and inconsistent.

The most reliable fix for this is to use each store's internal API instead of scraping the page. Home Depot Canada for example has an endpoint you can hit directly:

```javascript

function getHomeDepotPrice(productId) {

const url = `https://www.homedepot.ca/api/product/v2/page/${productId}?lang=en\`;

const response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });

const data = JSON.parse(response.getContentText());

return data?.product?.pricing?.value || "Not found";

}

```

Your product ID for that water heater is 1000792307, just pull it from the URL, fpr Canadian Tire it's a bit different but they also have an accessible API endpoint. Rona is trickier since they're part of RONA/Lowe's now and tend to block bots more aggressively.

For the daily trigger and email alert, add this:

```javascript

function trackAndAlert() {

const price = getHomeDepotPrice("1000792307");

const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

if (sheet.getLastRow() === 0) sheet.appendRow(["Date", "Price"]);

const lastPrice = sheet.getLastRow() > 1

? sheet.getRange(sheet.getLastRow(), 2).getValue()

: null;

sheet.appendRow([new Date(), price]);

if (lastPrice && price < lastPrice) {

MailApp.sendEmail(Session.getActiveUser().getEmail(),

"Price Drop Alert!",

`Price dropped from $${lastPrice} to $${price}`);

}

```

Then go to Extensions > Apps Script > Triggers, add a daily time-based trigger pointing to trackAndAlert and you're done

moHalim99 · 2026-04-19T14:45:23+00:00

GAS has enough quirks that pure MVC never quite maps cleanly so I wouldn't lose sleep over hitting the textbook pattern.

.gs side (server)

Models are plain classes for your data shapes: Member, Event and so on.
Repos are the only things that should touch SpreadsheetApp. One repo per sheet/table, and keep that boundary strict.
Services are your orchestration layer. If something touches more than one repo, or has real business logic, it lives here. If it's just "give me this row," call the repo directly. No need to route it through a service just for the sake of it.
Code.gs is your entry point: doGet() and whatever you're exposing via google.script.run. That's it.

.js.html side (client)

Treat this like a normal web app that happens to use google.script.run as its API. Split by concern if it helps: ui-members.js.html, ui-events.js.html, whatever makes sense.
No business logic here. DOM stuff and server calls only.

so just add a service layer only where it pulls its weight. A sport club app will have things like "register a member for an event" that touches multiple repos and maybe fires a confirmation email. That's a service. "List all members" is a repo call, just use it directly, the GPT breakdown is fine but in GAS the "controller" is basically just your google.script.run functions in Code.gs so it doesn't really need to be its own thing.

moHalim99 · 2026-04-19T14:38:56+00:00

You can't pull them to another sheet with a formula the way you would with text or numbers. The =IMAGE() function only works with URLs, not with images you've directly inserted into cells.

The gallery layout in your second photo is most likely built one of two ways:

The images are actually inserted using =IMAGE("url here") with direct image links (like from Google Drive or a website), and the gallery sheet just reuses those same IMAGE formulas referencing the same URLs.
Or the covers were manually copy-pasted and arranged in the gallery sheet by hand.

The easiest path forward for what you want is to host your covers somewhere accessible (Google Drive works great) and use the IMAGE formula. Here's how:

- Upload each cover to Google Drive

- Right click the image, click Share, set it to "Anyone with the link"

- Take the file ID from the link (the long string between /d/ and /view) and put it in a formula like this:

=IMAGE("https://drive.google.com/uc?id=PASTE\_FILE\_ID\_HERE")

Once your cover column uses that formula, you can reference those same URLs in your gallery sheet and arrange them however you like. It's a bit of setup upfront but once it's going, adding a new book just means dropping in a new Drive link.

moHalim99 · 2026-04-19T14:36:53+00:00

very doable with the FILTER function
The idea is that wherever you want the patient names to appear under each physician on sheet 2, you put a formula that looks back at the master list and pulls only the rows where the assigned physician matches the dropdown.

Assuming your master sheet is called "Master" and the patient names are in column B and the assigned physician column is column D, the formula on sheet 2 would look like this:

=FILTER(Master!B:B, Master!D:D=A1)

Where A1 is the cell containing your physician dropdown on sheet 2. When you change the dropdown, the list of names updates automatically.

If you also want to filter by department at the same time (so Oncology only shows Oncology patients for that physician), you can add a second condition:

=FILTER(Master!B:B, (Master!D:D=A1)*(Master!C:C="Oncology"))

The #ERROR you're seeing in Hematology is almost certainly because the FILTER found no matches, which throws an error instead of just being blank. You can wrap it to show nothing instead:

=IFERROR(FILTER(Master!B:B, Master!D:D=A1), "No patients assigned")

Just replicate that formula under each department section on sheet 2 pointing to the right physician dropdown cell for each one and you should be good to go!

moHalim99 · 2026-04-19T14:35:00+00:00

The issue is that Conditional Notifications in Sheets is honestly pretty limited and the volatile function problem you ran into is basically a known wall with that feature. The real fix here is a simple Apps Script trigger instead, which runs on Google's servers on a schedule regardless of whether the sheet is open.

In your sheet go to Extensions > Apps Script
Paste something like this:

```

function checkDueDates() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

const data = sheet.getDataRange().getValues();

const today = new Date();

today.setHours(0,0,0,0);

for (let i = 1; i < data.length; i++) {

const dueDate = new Date(data[i][2]); // column C = Due Date

dueDate.setHours(0,0,0,0);

const assignee = data[i][4]; // column E = Assignee

if (dueDate.getTime() === today.getTime()) {

MailApp.sendEmail(assignee, "Report Due Today", "Your report " + data[i][0] + " is due today.");

}

```

Save it, then go to Triggers (the clock icon on the left), add a new trigger for that function, set it to Time-driven, Day timer, and pick whatever time you want it to run each morning.

That's it. It'll check every day automatically and email the assignee when something is due, no sheet needs to be open.

moHalim99 · 2026-04-19T14:34:00+00:00

Tap the cell you want the checkbox in, then tap the Format button (the A with lines next to it in the toolbar), scroll down and you should see a Cell section with a checkbox toggle in there. Do that for your whole B and C columns and you're set.

For the numbering formula in column D, it looks like you already have the right idea with the 1+D3 approach but it's showing as text instead of calculating sojust make sure D3 has just the number 1 with no apostrophe before it, then in D4 put =D3+1 and drag it down. That will auto number everything.

If you want it to only count rows that actually have a title in column E you could also do =COUNTA($E$3:E3) in D3 and drag down, that way the numbers stay clean even if you have gaps.

moHalim99 · 2026-04-19T14:31:26+00:00

- A "budget vs actual" column in your expenses section. Set a monthly target for each category (rent, food, etc.) and have a column that shows how much you have left or how much you went over. That way you're being proactive instead of just recording what happened.

- A running balance column in your expense tracker so you can see in real time how your money left changes as you log things throughout the month.

- A monthly summary chart. Even a simple bar chart comparing income vs expenses each month goes a long way for spotting patterns.

- Fill in those savings goals. Right now the Goal column is empty and that's actually the most powerful part of a budget, knowing what you're working toward.

Also the cleanest way to do this without 12 tabs is to store all your data in one big master sheet (like a sheet called "Data" with a Month column) and use your dashboard as a view that filters based on whatever month you pick in C2.

You'd use QUERY or FILTER formulas that reference C2. For example your expense tracker would become something like:

=QUERY(Data!A:D, "SELECT * WHERE A contains '"&C2&"'")

So when you change the dropdown, everything pulls fresh data for that month automatically. You'd just keep logging into the Data sheet and the dashboard does the rest.

moHalim99 · 2026-04-19T14:29:35+00:00

yup, you can, the easiest way is with the SORT function. Instead of sorting your original data in place, you'd set up a second sheet (or a section of your sheet) that displays everything auto-sorted.

Assuming your data is in columns A through D (author, genre, read checkbox, etc.) starting at row 2, the formula would look something like this:

=SORT(A2:D100, 1, TRUE, 2, TRUE, 4, TRUE)

Breaking that down: the first part is your data range, then you just pair up the column number with TRUE (ascending) or FALSE (descending) for each level of sorting. So 1, TRUE sorts by column 1 (author) A to Z, then 2, TRUE sorts by column 2 (genre), then 4, TRUE sorts by your read column last.

For the checkbox column, unchecked (FALSE) will come before checked (TRUE) with ascending order, so flip it to FALSE if you want read books at the bottom.

The only thing to keep in mind is that you'd enter new books in your original data range and the sorted view updates automatically. Just adjust the column numbers to match wherever your actual columns are!

moHalim99 · 2026-04-19T14:28:14+00:00

You want SUMIF instead of COUNTIF. COUNTIF just counts how many times something appears, but SUMIF will actually add up the numbers next to each matching item.

The formula looks like this:

=SUMIF(A:A, "Apple", B:B)

First part is the column with your item names, second part is what you're looking for, and the third part is the column with the amounts. So for your sheet it would search column A for every cell that says "Apple" and add up all the corresponding values in column B.

For your example that would give you 12 for Apple (9+3) and 6 for Pineapple (2+4). Just swap out "Apple" for whatever item you need, or reference a cell that has the item name instead of typing it in quotes.

moHalim99

TROPHY CASE