How to Update Import Range data from another Source when Google Sheet is not open using Google app script

Question

I have a installable time driven trigger with 30 minutes interval in Google Sheet. The Trigger will send alert if there is any pending task. The trigger works fine when the Google Sheet is open. But the problem is when the Google Sheet is not open the Trigger is fetching the Old Data and that is causing False Alert. The pending task data is calculation using IMPORTRANGE function from another sheet. The another sheet is linked with Google Form where the sheets get updates by user's output.

So if anyone can help the App Script Code which will refresh all the data of the sheet event the sheet is not opened and then the trigger will do the job.

Note: The Source file and the Destination files are set for Update automatically on change and every minute. I have posted this to Google App Script Bug Report Tracker and they have suggested to ask help here. You can check that using the Link: https://issuetracker.google.com/issues/168644365

My Code:

  function AutoTelegramReminder() {
 
  var SpreadsheetId = 'My_SpreadSheetId';
  var SheetName = 'Pending Details';
  var ss = SpreadsheetApp.openById(SpreadsheetId)
  var sheet = ss.getSheetByName(SheetName);
 
  //Select the column we will check for the first blank cell
  var columnToCheck = sheet.getRange("D:D").getValues();
 
  // Get the last row based on the data range of a single column.
  var lastRow = getLastRowSpecial(columnToCheck);
 
  if (lastRow>1){
 
  var SlipNo = sheet.getRange(2, 4, lastRow-1).getValues();
  //getRange(row, column, numRows)
 

 //Send alert to Telegram
  var botSecret = "My_Bot_Secret"; //KCSL BOT ID
  var chatId = "My_Chat_Id"; //M.B.D Group ID
  var Url = "https://api.telegram.org/bot";
  var Link = "My_Google_Sheet_Link_For_Users";
  var whatsapLink = "My_Whatsapp_Link";

 
  var body = "MY_TEXT" + SlipNo + "MY_TEXT " + whatsapLink + "MY_TEXT";
 
  var response = UrlFetchApp.fetch( Url + botSecret + "/sendMessage?text=" + encodeURIComponent(body) + "&chat_id=" + chatId + "&parse_mode=HTML&disable_web_page_preview=TRUE");

                }

}


//Function To Get Last Row

function getLastRowSpecial(range){
  var rowNum = 0;
  var blank = false;
  for(var row = 0; row < range.length; row++){

    if(range[row][0] === "" && !blank){
      rowNum = row;
      blank = true;
    }else if(range[row][0] !== ""){
      blank = false;
    };
  };
  return rowNum;
};

Try using SpreadsheetApp.flush at the start of you script. If this does not work you can try to add the Utilities.sleep(1000) right after the flush, this will pause the script for 1 second to the updates can be made. — RemcoE33
@RemcoE33 Thanks for the reply I will check that and will let you know if it works. — Amit

Harsh Harsh · Accepted Answer · 2020-09-26T17:12:21

If you want to get the data refreshed then in place of =importrange try using the script to call for sheets by using SpreadsheetApp.openById('Sheet Id') and then fetch the range using getRange().getValues().

How to Update Import Range data from another Source when Google Sheet is not open using Google app script

1 Answers