Apps Script keeps reading “WAIT” even though the cell shows “OK” in the sheet UI

33 views

Skip to first unread message

Neeraj Mandloi

unread,

Jul 23, 2025, 11:19:15 AM Jul 23

to Google Apps Script Community

function queryPnLData() {

/* 0️⃣ Execution timer */

const startTime = new Date();

Logger.log('⏱️ START queryPnLData at %s', startTime);

/* ------------------------------------------------------------------ *

* Sheet bookkeeping

* ------------------------------------------------------------------ */

const ss = Sprea dsheetApp.getActiveSpreadsheet();

const OUT_NAME = "L/S/I Te rm_Filter";

let out = ss.getSheetByName(OUT_NAME);

if (!out) out = ss.insertSheet(OUT_NAME);

Logger.log('📄 Output sheet = "%s"', OUT_NAME);

/* 1️⃣ Clear old output (rows 4-end, cols A-Z) */

out.getRange(4, 1, out.getMaxRows() - 3, 26).clear();

Logger.log('🧹 Cleared previous output rows');

/* ------------------------------------------------------------------ *

* 2️⃣ Read run-time parameters (Term + four new filters)

* ------------------------------------------------------------------ */

const startDate = new Date(out.getRange("B1").getValue());

const endDate = new Date(out.getRange("D1").getValue());

const rawTerm = out.getRange("F1").getValue().toString().trim().toLowerCase();

const TERM_MAP = { "long term": "Long Term", "short term": "Short Term",

"intraday": "Intraday", "all": "All" };

const term = TERM_MAP[rawTerm] || rawTerm;

function readFilter_(rangeA1) {

const v = out.getRange(rangeA1).getValue().toString().trim();

return v === "" ? "ALL" : v;

}

const tickerFilter = readFilter_("E3");

const assetClassFilter = readFilter_("J3");

const brokerFilter = readFilter_("K3");

const userFilter = readFilter_("L3");

Logger.log('🔧 Parameters → start=%s end=%s term=%s ticker=%s asset=%s broker=%s user=%s',

startDate, endDate, term, tickerFilter, assetClassFilter, brokerFilter, userFilter);

/* ------------------------------------------------------------------ *

* 3️⃣ Helper : wildcard compare

* ------------------------------------------------------------------ */

function matches_(value, filter) {

if (filter === "ALL") return true;

return String(value).trim().toLowerCase() === String(filter).trim().toLowerCase();

}

/* ------------------------------------------------------------------ *

* 3️⃣-bis NEW helper : wait until AA4 shows "OK"

* ------------------------------------------------------------------ */

function waitForReady_(sheet, cellA1 = "AA4", timeoutMs = 30000) {

const flag = sheet.getRange(cellA1);

const t0 = Date.now();

Logger.log('⏳ Waiting for sheet "%s" sentinel %s', sheet.getName(), cellA1);

while (Date.now() - t0 < timeoutMs) {

SpreadsheetApp.flush();

const disp = flag.getDisplayValue();

const val = String(disp).trim().toUpperCase();

Logger.log('%s AA4 = "%s"', sheet.getName(), val);

if (val === "OK") {

Logger.log('✅ Sheet "%s" ready', sheet.getName());

return;

}

Utilities.sleep(1500);

}

throw new Error(`Timed-out: data in sheet “${sheet.getName()}” not ready.`);

}

/* ------------------------------------------------------------------ *

* 4️⃣ Fetch + multi-filter routine (used by both PnL & PnL_i)

* ------------------------------------------------------------------ */

function fetchData(sheetName) {

const src = ss.getSheetByName(sheetName);

if (!src) throw new Error(`Sheet “${sheetName}” not found`);

Logger.log('📥 Fetching data from "%s"', sheetName);

waitForReady_(src); // ensure data loaded

const header = src.getRange("A4:W4").getValues()[0];

const rows = src.getRange("A5:W" + src.getLastRow()).getValues();

const idxDate = (sheetName === "PnL") ? 2 : 1;

const idxTicker = header.findIndex(h => /ticker/i.test(h));

const idxAssetClass = header.findIndex(h => /asset\s*class/i.test(h));

const idxBroker = header.findIndex(h => /broker/i.test(h));

const idxUser = header.findIndex(h => /^user$/i.test(h));

const idxTerm = 22;

const inRange = rows.filter(r => {

const d = new Date(r[idxDate]);

return d >= startDate && d <= endDate;

});

let termFiltered;

if (sheetName === "PnL") {

termFiltered = (term === "All")

? inRange.filter(r => r[idxTerm] === "Long Term" || r[idxTerm] === "Short Term")

: inRange.filter(r => r[idxTerm] === term);

} else {

termFiltered = (term === "Intraday" || term === "All") ? inRange : [];

}

const fullyFiltered = termFiltered.filter(r =>

matches_(r[idxTicker], tickerFilter) &&

matches_(r[idxAssetClass], assetClassFilter) &&

matches_(r[idxBroker], brokerFilter) &&

matches_(r[idxUser], userFilter)

);

Logger.log('🔎 "%s": total rows=%d after date=%d after term=%d after filters=%d',

sheetName, rows.length, inRange.length, termFiltered.length, fullyFiltered.length);

return { src, header, filtered: fullyFiltered };

}

/* ------------------------------------------------------------------ *

* 5️⃣ Write-out helpers

* ------------------------------------------------------------------ */

let serial = 1;

function writePnl(startRow) {

const { src, header, filtered } = fetchData("PnL");

const cols = header.length;

Logger.log('✍️ Writing PnL block: %d rows at R%d', filtered.length, startRow);

out.getRange(startRow, 2, 1, cols).setValues([header]);

src.getRange("A4:W4").copyTo(out.getRange(startRow, 2, 1, cols), { formatOnly: true });

if (filtered.length) {

out.getRange(startRow + 1, 2, filtered.length, cols).setValues(filtered);

src.getRange(5, 1, filtered.length, cols)

.copyTo(out.getRange(startRow + 1, 2, filtered.length, cols), { formatOnly: true });

const seq = filtered.map((_, i) => [serial + i]);

out.getRange(startRow + 1, 1, seq.length, 1).setValues(seq);

serial += filtered.length;

} else {

out.getRange(startRow + 1, 2).setValue("No data found for the selected criteria.");

}

return filtered.length;

}

function writeIntraday(startRow) {

const { src, header, filtered } = fetchData("PnL_i");

const outHeader = header.slice();

outHeader.splice(1, 0, "");

const cols = outHeader.length;

Logger.log('✍️ Writing Intraday block: %d rows at R%d', filtered.length, startRow);

out.getRange(startRow, 2, 1, cols).setValues([outHeader]);

src.getRange("A4").copyTo(out.getRange(startRow, 2), { formatOnly: true });

src.getRange("B4:W4")

.copyTo(out.getRange(startRow, 4, 1, cols - 3 + 1), { formatOnly: true });

if (filtered.length) {

const outData = filtered.map(rw => {

const nr = [rw[0], "", rw[1]];

for (let i = 2; i < rw.length; i++) nr.push(rw[i]);

return nr;

});

out.getRange(startRow + 1, 2, outData.length, cols).setValues(outData);

src.getRange(5, 1, filtered.length, 1)

.copyTo(out.getRange(startRow + 1, 2, filtered.length, 1), { formatOnly: true });

src.getRange(5, 2, filtered.length, header.length - 1)

.copyTo(out.getRange(startRow + 1, 4, filtered.length, cols - 3 + 1), { formatOnly: true });

const seq = outData.map((_, i) => [serial + i]);

out.getRange(startRow + 1, 1, seq.length, 1).setValues(seq);

serial += outData.length;

} else {

out.getRange(startRow + 1, 2).setValue("No data found for the selected criteria.");

}

return filtered.length;

}

/* ------------------------------------------------------------------ *

* 6️⃣ Dispatcher

* ------------------------------------------------------------------ */

const R0 = 4;

if (term === "All") {

const cnt = writePnl(R0);

const labelRow = R0 + cnt + 2;

out.getRange(labelRow, 2).setValue("Intraday Data:")

.setFontWeight("bold").setHorizontalAlignment("left");

writeIntraday(labelRow + 1);

} else if (term === "Intraday") {

writeIntraday(R0);

} else {

writePnl(R0);

}

/* ------------------------------------------------------------------ *

* 7️⃣ Time stamp + duration

* ------------------------------------------------------------------ */

const now = new Date();

out.getRange("M1")

.setValue(Utilities.formatDate(now, "Asia/Kolkata", "dd-MMM-yyyy HH:mm:ss"));

out.getRange("R1")

.setValue(`Execution time: ${((now - startTime) / 1000).toFixed(2)} seconds`);

Logger.log('🏁 FINISHED in %sms', (now - startTime));

}

in this code see this part :-

/* ------------------------------------------------------------------ *

* 3️⃣-bis NEW helper : wait until AA4 shows "OK"

* ------------------------------------------------------------------ */

function waitForReady_(sheet, cellA1 = "AA4", timeoutMs = 30000) {

const flag = sheet.getRange(cellA1);

const t0 = Date.now();

Logger.log('⏳ Waiting for sheet "%s" sentinel %s', sheet.getName(), cellA1);

while (Date.now() - t0 < timeoutMs) {

SpreadsheetApp.flush();

const disp = flag.getDisplayValue();

const val = String(disp).trim().toUpperCase();

Logger.log('%s AA4 = "%s"', sheet.getName(), val);

if (val === "OK") {

Logger.log('✅ Sheet "%s" ready', sheet.getName());

return;

}

Utilities.sleep(1500);

}

throw new Error(`Timed-out: data in sheet “${sheet.getName()}” not ready.`);

}

Problem is in sheet UI I can see OK displayed in both PnL and PnL_i sheet but Script reads it WAIT

Formula in both PnL and PnL_i sheet Cell AA4 :-

=LET(

rng, A:Z,

errs, COUNTIF(rng,"#N/A") + COUNTIF(rng,"Loading...") + COUNTIF(rng,"No Data"),

nums, COUNT(rng),

IF( errs = 0, IF(nums > 0, "OK", "WAIT"), "WAIT")

)

Script Execution Log:-

Execution log

1:06:30 PM

Notice

Execution started

1:06:29 PM

Info

⏱️ START queryPnLData at Tue Jul 22 13:06:29 GMT+05:30 2025

1:06:30 PM

Info

📄 Output sheet = "L/S/I Term_Filter"

1:06:30 PM

Info

🧹 Cleared previous output rows

1:06:31 PM

Info

🔧 Parameters → start=Sat Apr 01 00:00:00 GMT+05:30 2000 end=Sun Jul 20 00:00:00 GMT+05:30 2025 term=All ticker=ALL asset=ALL broker=ALL user=YKM

1:06:31 PM

Info

📥 Fetching data from "PnL"

1:06:31 PM

Info

⏳ Waiting for sheet "PnL" sentinel AA4

1:06:32 PM

Info

PnL AA4 = "WAIT"

1:06:34 PM

Info

PnL AA4 = "WAIT"

1:06:36 PM

Info

PnL AA4 = "WAIT"

1:06:38 PM

Info

PnL AA4 = "WAIT"

1:06:40 PM

Info

PnL AA4 = "WAIT"

1:06:43 PM

Info

PnL AA4 = "WAIT"

1:06:45 PM

Info

PnL AA4 = "WAIT"

1:06:47 PM

Info

PnL AA4 = "WAIT"

1:06:49 PM

Info

PnL AA4 = "WAIT"

1:06:52 PM

Info

PnL AA4 = "WAIT"

1:06:54 PM

Info

PnL AA4 = "WAIT"

1:06:56 PM

Info

PnL AA4 = "WAIT"

1:06:58 PM

Info

PnL AA4 = "WAIT"

1:07:00 PM

Info

PnL AA4 = "WAIT"

1:07:01 PM

Error

Error: Timed-out: data in sheet “PnL” not ready.

waitForReady_

@ 10. L/S/I Term_Filter.gs:68

fetchData

@ 10. L/S/I Term_Filter.gs:79

writePnl

@ 10. L/S/I Term_Filter.gs:124

queryPnLData

@ 10. L/S/I Term_Filter.gs:185

UI Shows OK

I just can't figure out whats going wrong!!

Plz Help!!

Regards.

Neeraj Mandloi

unread,

Jul 29, 2025, 4:09:38 AM Jul 29

to Google Apps Script Community

**in this code see this part :-**

> > /* ------------------------------------------------------------------
> > *
> > * 3️⃣-bis NEW helper : wait until AA4 shows "OK"
> > * ------------------------------------------------------------------ */
> >function waitForReady_(sheet, cellA1 = "AA4", timeoutMs = 30000) {
> > const flag = sheet.getRange(cellA1);
> > const t0 = Date.now();
> > Logger.log('⏳ Waiting for sheet "%s" sentinel %s', sheet.getName(), cellA1);
> > while (Date.now() - t0 < timeoutMs) {
> > SpreadsheetApp.flush();
> > const disp = flag.getDisplayValue();
> > const val = String(disp).trim().toUpperCase();
> > Logger.log('%s AA4 = "%s"', sheet.getName(), val);
> > if (val === "OK") {
> > Logger.log('✅ Sheet "%s" ready', sheet.getName());
> > return;
> > }
> > Utilities.sleep(1500);
> > }
> > throw new Error(`Timed-out: data in sheet “${sheet.getName()}” not ready.`); }

Problem is in sheet UI I can see **"OK"** displayed in both **PnL** and **PnL_i** sheet but Script reads it **"WAIT"**

**Formula in both PnL and PnL_i sheet Cell AA4 :-**

> =LET( rng, A:Z, errs, COUNTIF(rng,"#N/A") + COUNTIF(rng,"Loading...") + COUNTIF(rng,"No Data"), nums,
> COUNT(rng), IF( errs = 0, IF(nums > 0, "OK",
> "WAIT"), "WAIT") )

**Script Execution Log:-**
```

Execution log
1:06:30 PM Notice Execution started
1:06:29 PM Info ⏱️ START queryPnLData at Tue Jul 22 13:06:29 GMT+05:30 2025
1:06:30 PM Info 📄 Output sheet = "L/S/I Term_Filter"
1:06:30 PM Info 🧹 Cleared previous output rows
1:06:31 PM Info 🔧 Parameters → start=Sat Apr 01 00:00:00 GMT+05:30 2000 end=Sun Jul 20 00:00:00 GMT+05:30 2025 term=All ticker=ALL asset=ALL broker=ALL user=YKM
1:06:31 PM Info 📥 Fetching data from "PnL"
1:06:31 PM Info ⏳ Waiting for sheet "PnL" sentinel AA4
1:06:32 PM Info PnL AA4 = "WAIT"
1:06:34 PM Info PnL AA4 = "WAIT"
1:06:36 PM Info PnL AA4 = "WAIT"
1:06:38 PM Info PnL AA4 = "WAIT"
1:06:40 PM Info PnL AA4 = "WAIT"
1:06:43 PM Info PnL AA4 = "WAIT"
1:06:45 PM Info PnL AA4 = "WAIT"
1:06:47 PM Info PnL AA4 = "WAIT"
1:06:49 PM Info PnL AA4 = "WAIT"
1:06:52 PM Info PnL AA4 = "WAIT"
1:06:54 PM Info PnL AA4 = "WAIT"
1:06:56 PM Info PnL AA4 = "WAIT"
1:06:58 PM Info PnL AA4 = "WAIT"
1:07:00 PM Info PnL AA4 = "WAIT"
1:07:01 PM Error Error: Timed-out: data in sheet “PnL” not ready. waitForReady_ @ 10. L/S/I Term_Filter.gs:68 fetchData @ 10. L/S/I Term_Filter.gs:79 writePnl @ 10. L/S/I Term_Filter.gs:124 queryPnLData @ 10. L/S/I Term_Filter.gs:185
```