Curious to know the exact date when Google cameras captured those aerial and street view photographs of your home (or any other address) on our beautiful planet? Well, you can find the dates easily both in Google Maps and Google Earth.
Find the capture date of Satellite Images
If you wish to know the date when satellites captured those aerial images that you see in Google Maps, you will have to use Google Earth for that. For some unknown reason, Google doesn’t display these dates on the Google Maps website or the Google Earth web app.
Launch the Google Earth app on your desktop, search for any location in the sidebar and, this is important, zoom in an area as much as possible. Now hover your mouse over the map and you should see the capture date of that satellite image in the status bar as seen in the above screenshot.
Finding the capture date of Street View Images
If you happen to live in a country where Google Street View is available, you can use the Google Maps website itself to determine the date when Google Street Views cars were in your area capturing pictures of the neighbourhood.
Go to maps.google.com and search for an address. Next, drag the yellow “Pegman” to any area on the Google Map to switch from aerial to street view. The image capture date will be instantly displayed in the status bar as shown in the screenshot below.
Unlike Google Earth, the capture dates available inside Street View images only reveal the month and year of the picture but not the exact date.
To summarize, you need to use Google Earth (desktop app) to determine the capture date of Aerial Images and Google Maps for finding the date of Street View images.
John Q Public runs a travel agency and they have thousands of clients across the globe. Other than managing tickets and travel itineraries, the agency also keeps a record of passports and visas of their clients to ensure that customers have valid documents at the time of travel.
Most countries require that a foreigner’s passport must be valid for at least six months from the date of entry. The US government, therefore, recommends that you renew your passport at least nine months before it expires.
Send Automatic Emails with Google Sheets
John’s agency is looking for a reminder system that will automatically send an email notification to their customers when their passports have expired or are due for renewal in the next few months. Let’s see how they can build such a workflow in 10 minutes with the help of Mail Merge for Gmail.
The idea is simple.
We have the customer data in a Google Sheet or a Microsoft Excel spreadsheet. The “Expiry Date” column in the spreadsheet contains the date when the passport is set to expire. We setup a cron job that runs in the background and monitors the expiry date. If any date in the sheet is less than, say, 12 months from today, an automatic email reminder is sent to the customer.
Create the Reminder Email Workflow
To get started, install the Mail Merge for Gmail add-on for Google Sheets. If you have never used the merge add-on earlier, please watch the Mail Merge tutorial for a quick overview.
Next, create a new Google Sheet and choose Addons > Mail Merge with Attachments > Create Merge Template. If you have your customer data in an Excel sheet, you can easily import the records into this Google sheet using the File > Import menu.
Next, we’ll use the Array Formulas to populate the Scheduled Date column based on the date in the Expiry Date column. Go to row #2 of the scheduled date column and paste this formula:
=ArrayFormula(IF(ISBLANK(E2:E),"",E2:E-365))
The date in the Scheduled Date column will automatically be filled with a date that is 12 months before the date in the Expiry Date column. Thus if the passport expiration date is set to July 12, 2021, the follow-up reminder email would be sent exactly a year earlier on July 12, 2020.
Open the Gmail website, compose a new email message that will be the reminder template and save it in your drafts folder. The email body and subject can include column titles, enclosed inside double-curly braces and these will be replaced with actual values from the Google Sheet when the email is sent.
Auto Expiry Reminder Email
Here’s how are sample reminder email template looks like. You can also include emojis, images, and file attachments in your email drafts.
Now that our customer data is ready to be merged, go to the Addons menu in the sheet and choose Configure Mail Merge.
Here follow the step-by-step wizard to add your sender’s name and also specify addresses that you wish to CC/BCC in every merged message.
In the Create Email Template section, choose “Use a Gmail Draft” and select the draft template that you’ve created in the previous step.
Send Date-based Reminder Emails
Expand the “Send Email Campaign” section and choose “Send a Test Email” to preview your message before it gets sent to external users. If all looks good, choose “Run Mail Merge” and hit “Go”.
That’s it. Mail Merge will setup a background task that will continuously run in the background and whenever a passport is due to expire, an email reminder is automatically sent to the client based on the date in the Scheduled Date column.
You can check the “Mail Merge Logs” sheet to track progress and a copy of all emails will also be saved in your Gmail Sent Items folder.
The reminder emails are sent based on the timezone of your spreadsheet. If you would like to send emails in a different timezone, go to the File menu inside Google Sheet, choose Spreadsheet Settings and update the timezone.
You can also make use of Filters in Google Sheets to send automatic emails to rows that meet specific criteria - when the country is “India” or when a cell value contains “Follow-up” and so on.
The same date-based workflow can be utilized to automate email sending in multiple scenarios like sending personalized wishes on birthdays and anniversaries, domain renewal reminders, fee payment reminders, appointments and more.
Google Script imposes quotas around different services. If your script exceeds the specified quota, it throws an exception and terminates execution until the quota is reset.
For instance, a Google Script can read 20,000 email messages from Gmail per 24 hours before it throws an exception like Service invoked too many times.
The Save Gmail addon downloads email messages from Gmail and writes them as PDF files to your Google Drive. It uses a time-based trigger to run the script in the background or a user can manually run the app to download emails.
If a user’s Gmail account has a large number of emails and they try to run the script too frequently, it could exceed the quota and the trigger may fail. It thus help to have some sort of checks in the script that will temporarily pause the script execution if a known exception if thrown.
We are using the CacheService of Google Script to track if a script has been suspended.
The expiry time is set to 60 minutes so the script will automatically resume execution once the cache value has expired.
In the main app, we add a try catch block that parses the exception message. It the message matches one of the known errors - like Service using too much computer time for one day or Service invoked too many times - we pause the script for 60 minutes.
constapp=()=>{try{// download emails}catch({ message }){if(/Service invoked too many times/.test(message)){suspend(60);}}};consthourlyTrigger=()=>{if(!isSuspended()){app();}};
The next time our hourlyTrigger is invoked, it will run the main app only if the Google Script is not in suspended state. As we are using the Cache Service here, the suspended state is automatically reset when the cache expires.
The Indian numbering and currency system widely uses lakhs and crores for writing large numbers. The term lakh means one hundred thousand while a crore represents ten million. A billion is written as hundred crores and there exist even higher denominations like arab, kharabh or neel which is equivalent to 10 trillion.
If you would like to write financial numbers in Google Sheets using the Indian Numbering system (lakhs, crores), here’s a custom Sheets function INR() that can spell the numeric value in words for you.
To get started, go to your Google Sheet, click the Tools menu and select Script Editor. Copy-paste the function below inside the Script Editor and chose File > Save to save your changes.
Now switch to the Google Sheet, type =INR(123) in any cell and it will instantly spell the number in words using the lakhs and crores system. The function was originally written by Amit Wilson and adopted to use the V8 Runtime.
/**
* Convert number to words in Indian Rupees
*
* @param {number} input The value to convert.
* @return The number in lakhs and crores.
* @customfunction
*/functionINR(input){const rupees =Number(parseInt(input,10));const output =[];if(rupees ===0){
output.push("zero");}elseif(rupees ===1){
output.push("one");}else{const crores = Math.floor(rupees /10000000)%100;if(crores >0){
output.push(`${getHundreds(crores)} crore`);}const lakhs = Math.floor(rupees /100000)%100;if(lakhs >0){
output.push(`${getHundreds(lakhs)} lakh`);}const thousands = Math.floor(rupees /1000)%100;if(thousands >0){
output.push(`${getHundreds(thousands)} thousand`);}const hundreds = Math.floor((rupees %1000)/100);if(hundreds >0&& hundreds <10){
output.push(`${getOnes(hundreds)} hundred`);}const tens = rupees %100;if(tens >0){if(rupees >100) output.push("and");
output.push(`${getHundreds(tens)}`);}}return["Rupees",...output,"only"].join(" ").split(/\s/).filter((e)=> e).map((e)=> e.substr(0,1).toUpperCase()+ e.substr(1)).join(" ");}functiongetOnes(number){const ones =["","one","two","three","four","five","six","seven","eight","nine"];return ones[number]||"";}functiongetTeens(number){const teens =["ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"];return teens[number]||"";}functiongetTens(number){const tens =["","","twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"];return tens[number]||"";}functiongetHundreds(num){if(num >0&& num <10){returngetOnes(num);}if(num >=10&& num <20){returngetTeens(num %10);}if(num >=20&& num <100){return`${getTens(Math.floor(num /10))}${getOnes(num %10)}`;}return"";}
The Google Sheets function will only be available in the spreadsheet where you have added the above code. If you create a copy of the spreadsheet, the function would be copied as well.
With Google Drive, you can store files in the cloud and share them easily with anyone. Open any file in Google Drive, click the Share button and you’ll get a URL (link) that others can use to access your file. This is common knowledge but Google Drive has plenty of URL tricks up its sleeve that will make these simple Drive links even more powerful.
Google Drive URL Tricks
Google Drive Web Viewer
Google Drive includes a built-in web viewer so people can view your shared files - from Microsoft Office documents to videos to AutoCAD drawings - directly in their browser. You can use this web viewer to view online files without having to download the files to your computer.
https://docs.google.com/viewer?url=FILE_URL
Replace FILE_URL with the full http link of the online document and anyone can view your file in the browser itself. Here’s an example.
Reader Mode for Google Drive Files
You can view native Google documents in reader mode (sans the Google UI) by simply replace /edit in the Google Drive file URL with /preview.
So if the original share link of a file in Google Drive is:
Here’s a Google Sheet, Google Document and Google Slides presentation in preview mode that is less-cluttered without any menus and toolbars and thus loads faster.
Embed Google Documents in Web Pages
The /preview is useful when you need to embed a document, spreadsheet or presentation from Drive in your web page using the IFRAME tag as in this live example.
The native file viewer is useful but sometimes you may want to bypass the built-in Google Docs viewer and force the browser to download the file instead of opening it. Thus if a user has Photoshop on their computer, the PSD file that you have shared through Google Drive will open in Photoshop and not in their web browser.
Create Direct Download Links and Skip the Web Viewer
When you upload any file in Google Drive and share it, the shared link looks like this:
https://drive.google.com/open?id=DRIVE_FILE_ID
The FILE_ID is unique for every file in Google Drive. If you copy this FILE_ID and use it in the URL below, you’ll get a direct link to download the file from Google Drive (example).
The directly download URL trick works for native Google documents as well. This comes really handy if you want to give users an option to download your Google Document as a read-only PDF file or your Google Spreadsheet as an Excel XLS file.
The above links will now download the same Google document in Word (.docx) and PDF formats. You can also specify txt, html, odt (OpenDocument) or epub for downloading the Google Document as an e-book.
Google Slides - Download Links
Like Google Documents, the URLs of presentations in Google Drive have this format:
The direct download links for Google Slides is slightly different then Google Documents. Here replace /edit with /export/format where format can be pptx for downloading Google Slides as Microsoft Powerpoint files or PDF for exporting the presentation as a PDF slideshow.
The direct links for downloading the same presentation deck in PowerPoint (.pptx) and PDF formats are below: For instance, here’s a presentation on Google Slides that you directly download as a PDF or a PPT file.
Download Google Slides as PNG Files
With Google Slides, you can either export the entire presentation as a PDF or you can create links to individual slides that will download the slide as a high-res PNG file.
All you need to do is add ?pageid=pPAGE_NUMBER to the export url. So if I were to download the 10th slide as a PNG file, the URL would be:
Open your Google Spreadsheet in Google Drive, make the sheet Public (or share with Anyone with a link) and make a note of the shared URL. It should be something like this:
For instance, here’s the COVID-19spreadsheet and you can directly download the file as PDF or XLS file with simple manipulation of the original sheet URL.
Copy and Make any shared Google Drive File your own
Replace /edit with /copy in the URL of any native Google Drive file and anyone can click that link to quickly make a copy of that file in their own Google Drive. Try here.
Original Link:
https://docs.google.com/document/d/FILE_ID/edit
Copy Link:
https://docs.google.com/document/d/FILE_ID/copy
The /copy URL trick works for Google Docs, Sheets, Slides and Google Scripts. Add ?copyComments=true if you would like the copied document to include the comments from the original document. Set includeResolvedCommentsOnCopy=false to skip copying resolved comments and copyCollaborators=false to not share the copied document with the original collaborators.
You can use it for Google Forms as well but the form will be copied to another user’s Google Account only if the form owner has granted access to the form.
Invite users when copying documents
If you add userstoinvite=email@domain.com to the copy URL, the Google user who is copying the document will be prompted to share the document with the specific Google account immediately after copying the document.
When you create a production build for your React App, the output folder contains the main index.html file and associated JavaScript and CSS files are added in the /static/js and /static/css folders respectively.
If you are to combine all these JS and CSS files of React App in a single bundle, you can use gulp. Here’s how:
Go to the command line and install the gulp packages as dev dependencies in your package.json file.
The gulp task will add the inline attribute to the <script> and the <link> tags. The inlinesource module will read these inline attributes in the html file and replace them with the actual content of the corresponding files.
Run npm run build or npx react-scripts build to create an optimized production build for your React App and then run the command npx gulp to bundle all the JS and CSS files in the static build folder into the single main html file.
This guide explains how you can update the library version in your Google Script. It is specifically written for File Upload Forms but the steps are similar for any Apps Script Projects.
Open the Google Sheet associated with your form and go to Tools > Script Editor.
Inside the Script Editor, go to the Resources menu and choose Libraries
You’ll see a list of libraries used by your Google Script project. For the FormsApp library, open the Version dropdown and choose the highest version of the library. Click the Save button to upgrade your project to the new library.
Next, go to the Publish menu and choose Deploy as Web App.
Choose a new project version and click update to publish a new version of your Apps Script web app.
Your Google Script project is now updated to use the latest version of the included libraries.
Custom support systems like Freshdesk, Zoho Desk, or Zendesk offer an email address - like support@example.com - for quickly creating customer tickets without having to fill any complicated web forms.
Any email message sent to your support email address automatically gets converted into a ticket and a unique ticket ID gets assigned to the support request.
When a ticket is created from an email address, the email subject becomes the ticket title, the email body becomes the ticket description and the most important part, the customer’s name and email address is pulled from the FROM field of the email message header. This is important because when your helpdesk agents respond to a ticket, the replies are sent directly to the customer.
Email Ticketing with Gmail Forwards - The Problem
This system works perfect when the customer sends an email message directly to the help desk email but imagine if they send an email to you, the support agent, and it is your job to create a support ticket from the customer’s email.
Now if you forward the customer’s email message received in your Gmail mailbox to Freshdesk, the ticket would still be created but since it will have your name and email in the FROM field, the helpdesk software would assign you as the customer even though you are just creating a support ticket on behalf of the customer. If any agent responds to such a ticket, you’ll be part of all the communication and not the original customer since their email address is no part of the support ticket created from the forwarded email.
I have been exploring Freskdesk and faced a similar issue. How to easily create support tickets from existing email messages in Gmail. Couldn’t find a readymade solution so decided to build one using the Freshdesk API and Google Apps Script.
Convert Gmail Emails to Freshdesk Tickets
To get started, you’ll need the domain (where your Freshdesk site is hosted) and the API Key that is available under your Freshdesk profile page.
Next, go to your Gmail and create a label - say Freshdesk. Now you can simply drag customer emails from your Gmail Inbox to this new Freshdesk label and they’ll be automatically converted into customer tickets. Unlike email forwards, here the tickets would be created with the customer’s name and email address as the requestor.
Inside the Google Script editor, go to Edit > Current Project Triggers > Add Triggers and create a new time based trigger that runs every 15 minutes for the gmailToFreshdesk method.
Now the Google Script will connect to your Gmail every 15 minutes, finds any new email messages in the specified label and convert them to Freskdesk support tickets. It will also remove the email message from the label after the ticket is created.
No Gmail Sending Limits Apply
With Apps Script, you need not worry about Gmail sending limits since the tickets are created with API calls and your not sending (forwarding) email anywhere.
