logo
down
shadow

Exit script if Google Sheet not opened or not modified today (within 24 hours)


Exit script if Google Sheet not opened or not modified today (within 24 hours)

Content Index :

Exit script if Google Sheet not opened or not modified today (within 24 hours)
Tag : google-apps-script , By : toma
Date : November 28 2020, 12:01 PM

fixed the issue. Will look into that further You want to when the difference between the current time and the last modified time or the last opened time is smaller than 24 h, you don't want to run the script.
If my understanding is correct, the last modified time can be retrieved by getLastUpdated(). But the last opened time cannot be retrieved by it. So how about this workaround?
function saveTime() {
  PropertiesService.getScriptProperties().setProperty("time", new Date().getTime());
}

function onOpen() {
  saveTime();
}

function onEdit() {
  saveTime();
}

function process() {
  var t = PropertiesService.getScriptProperties().getProperty("time");
  if (new Date().getTime() - Number(t) < 1 * 24 * 60 * 60 * 1000) {
    return;
  }
}
if (new Date().getTime() - LastUpdatedPerDrive.getTime() > 1 * 24 * 60 * 60 * 1000) return

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Script to open "Today" in multi-sheet Google Sheet


Tag : development , By : Keniwan
Date : March 29 2020, 07:55 AM
help you fix your problem Sample Sheet- So, I am new to this part. Our work schedule is in by-weekly format, each two-week pay-period is on a separate sheet. , This script should bring you to the sheet with the current date..
function onOpen() {
var today = format(new Date());
var ss = SpreadsheetApp.getActive();
var sheets = ss.getSheets()
    .forEach(function (s) {
        var val = s.getRange("C2:P2")
            .getValues()[0];
        for (var i = 0, len = val.length; i < len; i++) {
            if (format(val[i]) === today) {
                ss.setActiveSheet(s)
                break;
            }
        }
    });
}

function format(date) {
return formattedDate = date.getMonth() + 1 + "/" + date.getDate()
}

How to fix an OnChange script that worked perfectly, then stopped working when the Google Sheet was modified


Tag : google-apps-script , By : kennystone
Date : March 29 2020, 07:55 AM
like below fixes the issue Overview: the workbook calculates an Overall Score for each Record (or row) based on the Category Scores for each Record. Category Scores are derived from computations performed on hundreds of data points for each Record. I compiled code to send an email onChange called checkComplete (below) based on several examples and tutorials I found online. checkComplete would only send an email if the status of the Record changed to "Complete." An onChange trigger is required since the workbook uses Importrange to load the raw data, and no human interaction takes place for an onEdit trigger. ,
UPDATE
 onEdit(e){
  if( e.range.getSheet().getName()=='Sheet1' &&
    e.range.getColumn()==15&&e.value=="Complete"&&e.oldValue!="Complete"){
    sendMail(e.range.getRow(),e.value);
   }  
}  
  var rank = SpreadsheetApp.getActiveSheet().getRange(row,1).getValue();
  var totalranked = SpreadsheetApp.getActiveSheet().getRange(4,1).getValue();
  var address = SpreadsheetApp.getActiveSheet().getRange(row,2).getValue();
  var score = SpreadsheetApp.getActiveSheet().getRange(row,6).getValue();
  var outlook = SpreadsheetApp.getActiveSheet().getRange(row,14).getValue();
  var ildemand = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('1-Dmnd').getRange(row+1,9).getValue();
  var aldemand = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('1-Dmnd').getRange(row+1,14).getValue();
  var mcdemand = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('1-Dmnd').getRange(row+1,19).getValue();
  var increase = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('2-Comp').getRange(row,11).getValue();
  var occupancy = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('2-Comp').getRange(row,13).getValue();
  var medHHI = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('4-Demo').getRange(row+2,17).getValue();
  var medHV = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('4-Demo').getRange(row+2,19).getValue();
  var walkscore = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('3-Muni').getRange(row,19).getValue();
  var dmnd = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('1-Dmnd').getRange(row+1,5).getValue()
  var comp = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('2-Comp').getRange(row,5).getValue();
  var muni = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('3-Muni').getRange(row,5).getValue();
  var grade = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('3-Muni').getRange(row,17).getValue();
  var demo  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('4-Demo').getRange(row+2,5).getValue();

var admin_email=XXXXX; //<- list email addresses here

function checkComplete() {
  var ss=SpreadsheetApp.getActive();
  var sh = ss.getActiveSheet();
  if(sh.getName()=="Dashboard"){
   var values = sh.getRange('O14:O').getValues().join('-');
   if(PropertiesService.getScriptProperties().getKeys().length==0){ // first time you run the script
    PropertiesService.getScriptProperties().setProperty('oldValues', values);
    return;
   }
   var oldValues = PropertiesService.getScriptProperties().getProperty('oldValues').split('-');
   var valuesArray = values.split('-');
   while (valuesArray.length>oldValues.length){
    oldValues.push('x'); // if you append some rows since last exec
   }
   Logger.log('oldValues = '+oldValues)
   Logger.log('current values = '+valuesArray)
   for(var n=0;n<valuesArray.length;n++){
    if(oldValues.indexOf(valuesArray[n])==-1){ // check for any difference
        Logger.log('value changed on row '+n+1+' value = '+valuesArray[n]+' ,  mail sent');
      sendMail(n+1,valuesArray[n]);
    }
   }  
   PropertiesService.getScriptProperties().setProperty('oldValues', values);
  }   
}


function sendMail(row,val){

   Logger.log('value changed on row '+row+' value = '+val+' ,  mail sent');

   var rank = sh.getRange(row,1).getValue();
   var totalranked = sh.getRange(4,1).getValue();
   var address = sh.getRange(row,2).getValue();
   var score = sh.getRange(row,6).getValue();
   var outlook = sh.getRange(row,14).getValue();
   var ildemand = ss.getSheetByName('1-Dmnd').getRange(row+1,9).getValue();
   var aldemand = ss.getSheetByName('1-Dmnd').getRange(row+1,14).getValue();
   var mcdemand = ss.getSheetByName('1-Dmnd').getRange(row+1,19).getValue();
   var increase = ss.getSheetByName('2-Comp').getRange(row,11).getValue();
   var occupancy = ss.getSheetByName('2-Comp').getRange(row,13).getValue();
   var medHHI = ss.getSheetByName('4-Demo').getRange(row+2,17).getValue();
   var medHV = ss.getSheetByName('4-Demo').getRange(row+2,19).getValue();
   var walkscore = ss.getSheetByName('3-Muni').getRange(row,19).getValue();
   var dmnd = ss.getSheetByName('1-Dmnd').getRange(row+1,5).getValue()
   var comp = ss.getSheetByName('2-Comp').getRange(row,5).getValue();
   var muni = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('3-Muni').getRange(row,5).getValue();
   var grade = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('3-Muni').getRange(row,17).getValue();
   var demo  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('4-Demo').getRange(row+2,5).getValue();


      // MailApp.sendEmail(admin_email,'EARLY results for '+address+' are ready!','EARLY says the Site is '+outlook+' and ranks # '+rank+' out of '+totalranked+' with an overall Score of '+score+'. The projected Net Demand is '+dmnd+' with: IL '+ildemand+' units, AL '+aldemand+' units and MC '+mcdemand+' units. The competitive environment is '+comp+' with an occupancy rate of '+occupancy*100+'% for nearby properties, and an expected supply increase of '+increase*100+'%. The location is '+walkscore+', and its public schools have a grade of '+grade+' making it '+muni+'. The demographic trends are '+demo+' with Median HHI of $'+medHHI+' and a median Home Value of $'+medHV+'. For more information, please visit https://docs.google.com/spreadsheets/d/1ydcXQilx6hxhI6HPpWTPT7Bq9a-gXPa7h8UC6KL9W8c/edit?usp=sharing.');
    // add senior growth rate(s) to the Demographic summary sentence - Sept 3 2019
}

Excel VBA exit sub if txt file modified today


Tag : excel , By : gopal
Date : March 29 2020, 07:55 AM
wish of those help First you are saying the variable file is of type Object
Dim file As Object
dim fileName as String
Dim file As Object
Dim FSO as Object

fileName = ThisWorkbook.Path & "\Logs.txt"
set FSO = CreateObject("scripting.filesystemobject")
set file = FSO.GetFile(fileName)

google sheet script to email mutiple blob one create by google sheet and another created by files in google Drive folder


Tag : google-apps-script , By : Pepe Araya
Date : March 29 2020, 07:55 AM
around this issue You want to send an email including the attachment files by including all blobs.
If my understanding is correct, how about the following modification?
var attachements = [];
var attachements = [att];

Tag : url , By : OlioEngr
Date : March 29 2020, 07:55 AM
it should still fix some issue Another app script question. I've been working/researching this for a few days and get close, but need to add an element of automation, my current scripts are not suitable for the end goal. , Change this line:
var SPREADSHEET_URL_VALUE = SPREADSHEET_URL_REFERENCE_SOURCE_RANGE.getValues(); 
var SPREADSHEET_URL_VALUE = SPREADSHEET_URL_REFERENCE_SOURCE_RANGE.getValue();
Related Posts Related QUESTIONS :
  • Invalid transfer on left side. (line 1, "Tests" file)
  • Error with deleting items from Google Form
  • Cloud build API deploy:run on Google Apps Script doesn't work
  • Does Column Index changes upon adding a column manually in a spreadsheet
  • Is it possible to do Input-based queries in Apps Script?
  • Extract the exact subjects and update the google sheets using apps script
  • Conversion of a Google Form ItemType via Apps Script
  • Display a clickable link on screen in Google Apps Script
  • How to encrypt/decrypt URL parameters in Google Apps Script?
  • Google Sheets filename in cell via formula instead of a script?
  • Google Sheets custom function bug "Unknown function" after successful run
  • Iterate through column, find match and send email based on cell value
  • How to modify details of only a particular occurrence of a recurring calendar event using google app script?
  • How do I make a Google Sheets custom formula that gives the current cell the color of the input cell?
  • Pass spreadsheet data in addRows() as an array
  • getActiveCell() always returns 1,1
  • &amp; is always being decoded (which I don't want) using HtmlOutput or HtmlTemplate
  • How do I loop through a single column of dates in Google Sheets and then send an email based on the date
  • Can i use Google app script for developing google chrome extension
  • How to set absolute reference in G-Sheets using Google Script
  • How to use Vlookup within an OnEdit function
  • How to convert Google Apps script blob to a base64 encoded string?
  • How to Place Two Different Types of Time Stamps in One Sheet?
  • Why can't lock value of User input?
  • How to copy edited cell data to new spreadsheet, into cell and sheet dependent on the edited cell?
  • How can I populate multiple choice questions in a Google Form from data within a Google Sheet
  • Create checkbox in cell by typing in another cell
  • How to make a form using Google Sheets script editor
  • How to get older versions of Google Spreadsheet data?
  • How to use scope "https://www.googleapis.com/auth/script.webapp.deploy"
  • Get a Cell value as value for function
  • Google Script ModalDialog/ ModelessDialog setWidth and setHeight feature not working
  • How to send a custom message when adding a viewer to Google Spreadsheet via Google Apps Scripts?
  • Looping in Creating PDF in Google Sheet
  • Is it possible to create a gmail label and choose its color using Google Apps Script?
  • Can I protect cells (user not allowed to edit) and run script that makes changes in these cells (e.g. hide row, unhides
  • Google Sheets custom sidebar: How to display a current cell value in sidebar without reload the sidebar?
  • oAuth disabled on google script
  • getOAuthToken returns null when called from Google Sheets
  • Use google-apps script url in project html
  • How to adjust as custom size margins and paper size in script to save google spreadsheet in PDF?
  • Google Apps Script HtmlServices template variables and global variables
  • What's the difference between a Page and a Slide in Google Slides?
  • How insert date from cell to google script?
  • Google Forms Add-On - menu item not appearing on initial load after installing plugin
  • How can I modify this function to use multiple tabs and columns for each tab?
  • Efficient way to check if an array of cells is not the desired value
  • Google sheet script starting from the 1000th line?
  • Google Script logger not showing what sheet edit was made on from event data
  • Is there a better way to make a copy of a spreadsheet with content only?
  • Increment column of cells by 1 if a condition is met
  • Need to delete an entire row based on one cell's criteria
  • Adding a few members to a Google Group in 1 URLFetchApp Call?
  • Referencing column heading text in form response spreadsheet in app script code
  • How can i set the correct answer to a question with AppScript?
  • Need help finding the maximum value in a column in Google Sheets using Google Apps Script
  • Creating array with change log data and sending it via email
  • Input name and check for existence of spreadsheet
  • Google sheets script for changing reference
  • Function onOpen not activating when i open my forms
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com