I tried in this way also still not working
I've small issue:
I would NOT Iike to show as a notification if it's new added item.
I would like to get notification only when I change from spreadsheet value "pending" to "active". This part working for me.
BUT when I'm adding new item its adding to my spreadsheet and it's showing all old "active" status as a notification. I would NOT Iike to show as a notification if it's new added item. Please help.
please note all new added items it's as a default "pending"
Here is my code:
What about introducing a new status, for example 'semd notification '? Then read only the rows which have status 'send notification ', display the notification and after that update the corresponding row in the spreadsheet and set it to 'active'?
Taifun
@Taifun thank you for your response.Approciate your efforts and time.
Actually my code it worked before I don't know maybe I made some change anyway.
Actually when I'm sending item from my app to google sheeet H columb row status "Pending" by default it's filling. at that time i'm getting notification all "Active" (which is not correct.)
Regarding when I just change from google sheet status "Pending" to "Active" I'm getting notification (which is correct).
If it's possible please help me to solve in a simple way. For me everytime building Apk for checking getting very difficult. ;(((
As far as I can see you need a new status to be able to find out, when a notification needs to be sent and after that change the status to something else
Taifun
you think it's impossible to ignore all notification if status "pending" ?
I just want ignore(don't want to see) all notifications if status "Pending" that's all.
I guess here must be used if function
What about changing your select query and reading only those rows, from the spreadsheet which have status active?
... AND H = 'Active'
Taifun
I tried in that case I'm recieving all active status notification ;((
Yes, my understanding was, that this is what you want
Taifun
No, no sorry my English.
Actually I need to receive notification if from Google sheet Changed pending status for Active. Please note Old active no need to show as a notification.
But in this case when I start app I’m receiving all active status. Which i not changed anything
I should receive only if I changed pending to active status. Let’s assume I’ve 100 rows from these 100 rows 90 already active as a old, and 10 are pending so if I change from these 10 pending , 2 active these 2 active must show as a notification .
So conclusion: I should receive notification only if I’ll change from pending to active specific changed rows
Only show notification each time if will happen new changes from pending to active otherwise NO need to show all notification active each time.
That's why I suggested to
If you do not want that, you have to keep a list in your app of rows you already sent notifications to find out, which ones are the new ones
Taifun
@Taifun thank you so much for you help.
I did some manipulation and it's working.
Here when I'm sending first I'm storing status
Then I'm doing this manipulation
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.
SCRIPT:
`function checkForNewEntries() {
let ws = SpreadsheetApp.getActiveSpreadsheet();
let ss = ws.getSheetByName("Inquires");
let lastRow = ss.getLastRow();
let columnHValues = ss.getRange("H2:H" + lastRow).getValues();
let columnCValues = ss.getRange("C2:C" + lastRow).getValues();
let columnDValues = ss.getRange("D2:D" + lastRow).getValues();
let columnEValues = ss.getRange("E2:E" + lastRow).getValues();
let columnFValues = ss.getRange("F2:F" + lastRow).getValues();
let columnIValues = ss.getRange("I2:I" + lastRow).getValues(); // Assuming Column I is for "Seen" status
let newEntries = [];
for (let i = 0; i < columnCValues.length; i++) {
if (columnHValues[i][0] === "Pending" && columnIValues[i][0] !== "Seen") {
newEntries.push(`
<div id="entry-${i}" style="background-color: #e9ecef; padding: 10px; margin-bottom: 10px; border-radius: 5px;">
<strong># ${i + 2}</strong><br>
<strong>Açıklama:</strong> ${columnDValues[i][0]}<br>
<strong>Otel:</strong> ${columnEValues[i][0]}<br>
<strong>Gönderen:</strong> ${columnFValues[i][0]}<br>
<button onclick="google.script.run.markEntryAsDone(${i + 2}); markAsDone('entry-${i}')">Done</button>
</div>
`);
// Mark as seen immediately
ss.getRange("I" + (i + 2)).setValue("Seen");
}
}
return newEntries.join("");
}
function markEntryAsDone(row) {
let ws = SpreadsheetApp.getActiveSpreadsheet();
let ss = ws.getSheetByName("Inquires");
// Update column H to "Active"
ss.getRange("H" + row).setValue("Active");
// Hide the entry in the sidebar
SpreadsheetApp.getUi().alert('# ' + row + ' is now marked as Active.');
}
function showSidebar() {
const htmlOutput = HtmlService.createHtmlOutputFromFile('Sidebar')
.setTitle('New Entries Notification');
SpreadsheetApp.getUi().showSidebar(htmlOutput);
}
function getNewEntries() {
return checkForNewEntries();
}
function onOpen() {
showSidebar();
}
`
Here is HTML code:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script>
function checkNewEntries() {
google.script.run.withSuccessHandler(updateEntries).getNewEntries();
}
function updateEntries(entries) {
if (entries) {
document.getElementById('entries').innerHTML += entries;
document.getElementById('notification-sound').play();
}
}
function markAsDone(entryId) {
const entryElement = document.getElementById(entryId);
if (entryElement) {
entryElement.style.display = 'none';
}
}
setInterval(checkNewEntries, 30000); // Check every 30 seconds
</script>
</head>
<body>
<div id="entries">Loading...</div>
<audio id="notification-sound" style="display: none;">
<source src="https://redringtones.com/wp-content/uploads/2016/09/excuse-me-boss-you-have-a-text-message.mp3" type="audio/mp3">
Your browser does not support the audio HTML tag.
</audio>
</body>
</html>
