Digital Inspiration

View Details

If you are running an online store running on WordPress, chances are you are using WooCommerce to manage your customers and orders. The holiday season in near and you may want to send your existing customers a special discount code for their next purchase. Or you may want to analyze your store’s data to see how your business is performing in various regions.

You can the built-in export feature of WooCommerce to export your customers data to a CSV file and then import the CSV file into Google Sheets. Go to your WooCommerce dashboard, navigate to the Customers section, and you’ll find an option to download the customers list as a CSV file.

If you are however looking for a more efficient way to export your WooCommerce customers to Google Sheets, you can use Google Apps Script to create a custom script that will export the customers to a Google Sheet.

Step 1: Create an API Key in WooCommerceTo get started, you’ll create an API key in WooCommerce. Go to your WooCommerce dashboard, navigate to the Settings section, and then click on the “Advanced” tab. Go to the “Rest API” section and click on the “Create API Key” button.

On the next screen, you’ll be asked to enter a name for the API key. You can use a name like “Import Customers to Google Sheets” or something similar. You can restrict the API key permissions to read only, which is all we need since we’re only going to be reading customer data and not modifying any data.

WooCommerce will generate the consumer key and consumer secret for you. You’ll need to save the secret key somewhere, as you won’t be able to access it later from the WooCommerce dashboard.

Step 2: Create a Google SheetNow that you have your WooCommerce credentials, let’s create a Google Sheet to store the customer data. Type sheets.new in your browser’s address bar to create a new spreadsheet. Go to Extensions > Apps Script to open the Google Apps Script editor associated with your spreadsheet.

Paste the following code into the Apps Script editor. Remember to replace the WooCommerce consumer key, consumer secret and WordPress domain with your own values. Do not add a slash at the end of the WordPress domain.

Step 3: Flatten the Customer DataTo flatten the customer data, we’ll add the following function to the script.

const exportCustomersToGoogleSheet = () => { const wooData = fetchWooCommerceCustomers(); const customers = wooData.map(parseCustomer); const headers = Object.keys(customers[0]); const rows = customers.map((c) => headers.map((header) => c[header] || '')); const data = [headers, ...rows]; const sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(); sheet.getRange(1, 1, data.length, data[0].length).setValues(data); const message = rows.length + ' customers exported to sheet ' + sheet.getName(); SpreadsheetApp.getUi().alert(message);}; Step 5: Run the Export FunctionInside the Apps Script editor, click on the “exportCustomersToGoogleSheet” function and then click on the “Run” button. Authorize the script and watch as your customers data from WooCommerce magically appears in your Google Sheet.

You can then use Gmail Mail Merge to send personalized emails to your customers right inside the Google Sheet.

View Details

Let’s say you have a Github repository where you push all your code changes. Each commit has a unique commit hash, and you can use this hash to restore the code to a specific commit or a particular time.

It is advisable that you take a backup of your current code before proceeding.

Find the Commit HashTo get started, open your repository on Github and find the commit you want to restore. You can do this by clicking on the “Commits” tab and finding the commit in the list. If you want to restore to a particular date, you can use the calendar dropdown to see all the commits for that day and find the one you want.

You may also use the command line to find the commit hash.

git log --oneline Once you have found the commit you want to restore, you can create a new branch at that commit. Let’s call this branch working-branch.

git checkout -b working-branch <commit-hash> This git command will create a new branch named working-branch pointing to the specified commit and switches to that branch.

Next, you can force push the new branch to the remote repository.

git push -f origin working-branch Rollback to a Specific CommitNow that you have a new branch with the code at the specific commit, you can update the main branch to this restored state.

git checkout maingit reset --hard working-branchgit push -f origin main ⚠️ Please be careful when using these git commands since it will permanently delete all code changes made after the commit you are restoring.

View Details

Document Studio can convert Google Slides into high-resolution PNG images. This can be useful if you want to create multiple variations of the same slide in bulk - create a single template in Google Slides and then use Document Studio to generate PNG images with different text or images, pulled from a Google Sheet or Google Forms.

Internally, the app uses the Google APIs to generate high-resolution thumbnail images of the slides and uploads the individual slides to the Google Drive of the current user.

In this tutorial, we’ll explore two methods to achieve the slide-to-png conversion using Google Apps Script.

Approach #1 - Use the Google Slides APIYou can use the Google Slides API to get the thumbnail images of the slides, fetch the blob of the image, and then upload the image to Google Drive.

First, you would need to enable Google Slides API in the console of your Google Cloud project associated with the Google Apps Script project. Second,the thumbnail images has a fixed width of 1600px/800px/200px and you cannot change the size of the image.

Also, you need to make two API calls here. The first one is to get the thumbnail link of the presentation. The additional API call will fetch the thumbnail image from the URL.

Approach #2 - Use the Google Drive APIThe recommended approach is to use the Google Drive API to export the slides as PNG images. The big advantage here is that the generated image is of the same resolution as the original slide. So if you have set your presentation page size as 600x800 pixels, the generated PNG image will also be of the same size.

And there’s one less API call to make since the Drive API can directly export the slide as an image.

View Details

False positives in Gmail are uncommon but can happen, meaning an important email might mistakenly end up in your spam folder. When you’re dealing with hundreds of spam messages daily, identifying these legitimate emails becomes even more challenging.

You can create filters in Gmail such that emails from specific senders or with certain keywords are never marked as spam. But these filters would obviously not work for emails from new or unknown senders.

Find incorrectly classified messages in Gmail SpamWhat if we used AI to analyze our spam emails in Gmail and predict which ones are likely false positives? With this list of misclassified emails, we could automatically move these emails to the inbox or generate a report for manual review.

Here’s a sample report generated from Gmail. It includes a list of emails with a low spam score that are likely legitimate and should be moved to the inbox. The report also includes a summary of the email content in your preferred language.

To get started, open this Google Script and make a copy of it in your Google Drive. Switch to the Apps Script editor and provide your email address, OpenAI API key, and preferred language for the email summary.

Choose the reportFalsePositives function from the dropdown and click the play button to run the script. It will search for unread spam emails in your Gmail account, analyze them using OpenAI’s API, and send you a report of emails with a low spam score.

If you would like to run this script automatically at regular intervals, go to the “Triggers” menu in the Google Apps Script editor and set up a time-driven trigger to run this script once every day as shown below. You can also choose the time of the day when you wish to receive the report.

How AI Spam Classification Works - The Technical PartIf you are curious to know how the script works, here is a brief overview:

The Gmail Script uses the Gmail API to search for unread spam emails in your Gmail account. It then sends the email content to OpenAI’s API to classify the spam score and generate a summary in your preferred language. Emails with a low spam score are likely false positives and can be moved to the inbox.

User ConfigurationYou can provide your email address where the report should be sent, your OpenAI API key, your preferred LLM model, and the language for the email summary.

// Basic configurationconst USER_EMAIL = 'email@domain.com'; // Email address to send the report toconst OPENAI_API_KEY = 'sk-proj-123'; // API key for OpenAIconst OPENAI_MODEL = 'gpt-4o'; // Model name to use with OpenAIconst USER_LANGUAGE = 'English'; // Language for the email summary 2. Find Unread Emails in Gmail Spam FolderWe use the epoch time to find spam emails that arrived in the last 24 hours and are still unread.

The tokens variable keeps track of the number of tokens used in the OpenAI API calls and is included in the email report. You can use this information to monitor your API usage.

View Details

Adding a custom menu in Google Sheets, Docs, Slides, and Forms using Google Apps Script is straightforward.

As an illustration, the following code snippet adds a custom menu to the parent Google Sheet that reveals the spreadsheet name upon clicking.

function onOpen() { const ui = SpreadsheetApp.getUi(); const menu = ui.createMenu('➯ Custom menu'); menu.addItem('Show spreadsheet name', 'showName'); menu.addToUi();}function showName() { const fileName = SpreadsheetApp.getActiveSpreadsheet().getName(); SpreadsheetApp.getUi().alert(fileName);} The above code defines two functions: onOpen which executes when the app opens, and showName which is triggered when the menu item is clicked.

This approach can be applied to create custom menus in other Google Workspace apps, including Forms, Slides, and Google Docs.

Multiple Workspace Apps, One Custom MenuThe code above is specific to Google Sheets and will not work if you use it to add custom menus in Google Forms or Slides. That’s because you need to call SpreadsheetApp.getUi() to get the sheet’s UI instance while the Google Form’s UI can be accessed through a different FormApp.getUi() method.

This can be a problem because some Google add-ons, Document Studio for example, can be launched from multiple Google Workspace apps. How do you identify the currently active Workspace application (Sheets, Docs, Slides, or Forms) and add menu items that are specific to that app?

Identify the Current Workspace AppThe getContainer function is your secret weapon for identifying the currently active Google Workspace application. It works by iterating through a list of known app classes, like DocumentApp and SpreadsheetApp.

For each class, it attempts to access the UI object. If the call is successful and doesn’t throw an exception, it indicates that the corresponding app is active and returns that app class.

const getContainer = () => { const apps = [DocumentApp, SpreadsheetApp, FormApp, SlidesApp]; const activeApp = apps.find((app) => { try { app.getUi(); return true; } catch (f) { return false; } }); return activeApp;}; Building the Universal Custom MenuNow that you know the current Workspace application, we can modify the onOpen function to create a dynamic universal menu. The menu title contains the name of the active application and includes a menu item to display the app-specific file name.

View Details

The Mail merge add-on lets you send personalized emails to multiple recipients in one go. The emails are always sent via your Gmail account or your Google Workspace email address. Google also imposes a limit on the number of emails you can send per day.

Mail Merge with SMTPMail merge is convenient because you can put your contacts in a Google Sheet and the add-on will individually send the emails for you. However, if you aren’t using Gmail or have a large mailing list, an SMTP service like SendGrid or AWS may be more a suitable option for sending out personalized emails.

Wouldn’t it be nice if you could enjoy the ease of the Mail Merge add-on while still utilizing an SMTP service to send personalized emails? That’s where the Document Studio add-on can help.

Generate SMTP CredentialsGoogle for SMTP settings for [your email provider] and you’ll find the SMTP server address, port number, and the authentication details like the username and password (or API key) for your email service.

For instance, if you plan to use Zoho Mail for mail merge, the SMTP settings would be as follows:

SMTP Server: smtp.zoho.com
Port: 465
Username: Your Zoho account email address
Password: Your zoho.com password

Prepare Mail Merge DataOpen the Google Sheet with your mail merge data and launch the Document Studio add-on. Create a new workflow and choose the Send Email task.

From the list of Email Service providers, choose SMTP Server and enter the SMTP server address, port number, and the authentication details that you found in the previous step.

Next, move to the Email Message section and configure your email template. You can use placeholders like {{First Name}} and {{Title}} in the message body and subject line to personalize the emails.

If you would like to attach files to the email, you can do that as well. You may attach the same file to all emails or use placeholders to attach different files to each email.

Click the Preview button and you should see a sample email sent to your own email address through the SMTP server. You can now click the Save and Run button to send personalized emails to all recipients in your Google Sheet.

View Details

Google Sheets offers a built-in SUBSTITUTE function that can can find and replace a specific text in a cell with another value. For instance, you can use =SUBSTITUTE("My favorite color is red", "red", "blue") to replace the text red in the string with blue. The SUBSTITUTE function is case-sensitive and will replace all occurrences of the search text.

Replace Multiple Values with SUBSTITUTENow consider a scenario where you have to replace multiple values in a string with different values. For instance, if you have a template string like My name is {{name}} and I work at {{company}} and you want to replace {{name}} with the actual name and {{company}} with the company name.

The SUBSTITUTE function is not helpful here because it can only replace one value at a time but you can use nested SUBSTITUTE functions to replace multiple values in a single cell. There would be one SUBSTITUTE function for each value that you want to replace.

Nested SUBSTITUTE Functions =SUBSTITUTE( SUBSTITUTE(A1,"{{name}}","Amit"), "{{company}}","Digital Inspiration") Multiple Substitute Function for Google SheetsThe nested approach works, but the formula can get long and complex when you have to replace multiple values in a single cell. Here’s a simpler approach that uses Google Apps Script to create a custom function that can replace multiple values in a single call.

=MULTI_SUBSTITUTE(A1, "replace_1", "value_1", "replace_2", "value_2", ... "replace_n", "value_n") The function takes the input string as the first argument and then pairs of search and replace values. Each pair has two values - the first value is the search text and the second value is the replacement text. The function will replace all occurrences of the search text in the input string with the corresponding replacement text.

Open your Google Sheet, go to Extensions > Apps Script and paste the following code in the script editor. Save the script and you can now use the MULTI_SUBSTITUTE function in your Google Sheet to replace multiple values in a single cell.

/** * Replaces multiple occurrences of search text in a string with new values. * @returns {string} The modified string with replacements made. * * @customfunction */function MULTI_SUBSTITUTE(text, ...opts) { for (let i = 0; i < opts.length; i += 2) { const searchValue = opts[i]; const replaceValue = opts[i + 1]; // Regex for case-insensitive search (flags 'gi') const regex = new RegExp(searchValue, 'gi'); // Replace all occurrences of the search value text = text.replace(regex, replaceValue || ''); } return text;} This custom function uses regular expressions to replace all occurrences of the search value in the input string. The i flag in the regular expression makes the search case-insensitive unlike the built-in SUBSTITUTE function.

You can also use the multiple substitute function to generate pre-filled links for Google Forms.

View Details

A fintech startup is preparing to host its first-ever networking event for professionals within the industry. Over the course of their journey, the company has engaged in email communication with industry experts, partners, and clients. Now, they aim to collate a directory of these email addresses to send personalized email invitations.

Extract Email Addresses from GmailThe primary challenge is to extract all these email addresses from the company’s Gmail account and download them in a compatible format, like CSV, that can be easily imported into Google Contacts or a mailing list service like MailChimp.

This is where Email Address Extractor can help. It is a Google add-on that sifts through all email messages in the company’s Gmail account, extracts the email addresses and saves them in a Google Spreadsheet. It works for both Gmail and Google Workspace accounts.

The Gmail address extractor add-on can mine email addresses from specific Gmail folders (labels), emails that have been sent or received in the last n months, or for the entire mailbox.

You can choose to extract email addresses of the sender and the recipient fields, including those in the CC field. Additionally, it can parse the email’s subject line and message body and capture email addresses from the text. This is useful for extracting addresses from, say PayPal invoices, where the buyer’s email addresses are often written in the message body.

How to Extract Email Addresses in GmailYou may follow the step-by-step guide on how to extract email addresses from Gmail messages using the Email Address Extractor add-on.

Install the Gmail Extractor add-on and grant the necessary permissions. The add-on needs access to Gmail and Google Sheets for saving the extracted email addresses.
Launch the add-on inside your Google Spreadsheet and specify the search criteria. You may use Gmail search operators and any matching emails will be processed by the extractor.
Next, select one or more message fields (to, from, cc, bcc) that should be parsed for extracting emails. The add-on can also pull names of the sender and recipients if they are available inside the email headers.

Click on the Save and Run option to extract all the email addresses from the emails that match the specified criteria. The entire process may take some time depending up on the size of your Gmail mailbox.

The extracted email addresses will be stored in the current spreadsheet. You will notice that two sheets have been added to your active Google Sheet.

The first sheet contains the list of all the unique email addresses that have been extracted from the matching emails. The second sheet contains the complete details of the emails that have been processed. These include the message date, the sender’s detail, the subject line and a link to the original email message in Gmail.

The Google sheet should remain open and the computer should be online during the extraction. If the connection is lost, or if the extraction process is interrupted for some reason, you can simply click the “Resume” button and the extractor will pick from where it left off previously, avoiding the need to start the entire process over again.

The add-on applies a label, titled Extracted, to all the emails that have been processed and extracted. If you wish to re-extract the email addresses from the same set of emails, you can do so removing the label from the emails and running the extractor again.

Internally, it is a Google Script that uses the magic of Regular Expressions to pull email addresses from Gmail. The extracted email addresses are saved in a Google spreadsheet that can later be exported to Google Contacts or Outlook.

View Details

The File Upload feature of Google Forms lets you receive files from form respondents directly in your Google Drive. You may add the File Upload question in your Google Form to receive PDF assignments from students, job applications, portfolio images from contestants, and more.

While the file upload feature in Google Forms is handy, it does have one big limitation. For instance, when a respondent uploads a file through Google Forms, the file is stored in a fixed folder within the form owner’s Google Drive. All uploaded files are are saved to the same folder, making it difficult to determine which respondent has uploaded which set of files.

Move Uploaded Files in Google FormsThis is where Document Studio can help you. The add-on can help you automatically organize uploaded files in custom folders as soon as they are received in your Google Drive through Google Forms. You can move uploaded to another folder, or rename the files based on the respondents’ answers in the Google Form. Additionally, you can organize the uploaded files into subfolders for convenient access.

Prepare Google FormFor this example, we’ve created a Google Form for collecting job applications for different positions in our company. The candidates have to provide their full name, the position they’re applying for, and then upload their resume in PDF format.

By default, all uploaded files will be added in a new parent folder that is created by Google Forms in your Google Drive. However, you can organize the resume files in subfolders and move them to a specific folder based on the position the candidate has applied for. This will help you easily find the resumes of candidates who have applied for a specific position.

The uploaded files can also be renamed based on the candidate’s name or their email address. This will help you quickly identify the resumes of specific candidates.

Move Files to Custom Folders in Google DriveInstall the Document Studio and open the add-on in your Google Form. Create a new workflow and choose the File Uploads task from the list of available tasks.

With Document Studio, you can move the uploaded files to another folder, copy the files to another folder, or rename the files based on the form responses. For our example, we’ll move and also rename the uploaded files based on the position the candidate has applied for.

Select the file upload question from the list of available questions. Next, choose the parent Google Drive folder where you wish to copy or move the uploaded files. You may also choose to save the uploaded files in a Shared Drive Folder, something that is not possible with the default Google Forms file upload feature.

For the subfolder path input field, provide the full path where you want the uploaded files to be saved. You can use placeholders like {{Country}} or {{Position}} to dynamically create subfolders based on the form responses.

Finally, provide a new name for the uploaded files. For our example, we have used the {{Name}} placeholder to rename the uploaded files based on the candidate’s name provided in the Google Form.

Save the workflow and your automation is ready. Now, whenever a candidate uploads their resume through your Google Form, the uploaded file will be moved to a custom folder in your Google Drive. The file will also be renamed based on the candidate’s name.

Move File Uploads with Google Apps ScriptIf you are comfortable with Google Apps Script, you can also write a custom script that will move the uploaded files to a specific folder in Google Drive. The script can be attached to your Google Form and will run automatically whenever a new form response is submitted.

To get started, go to your Google Drive and create a new folder (or use an existing folder). Open the folder and grab the ID of the folder from the browser’s address bar as shown in the screenshot.

Next, go to your Google Form that is accepting File Uploads and choose Script Editor from the 3-dot menu. Inside the script editor, remove all the existing code and copy-paste the following snippet. Remember to replace the Folder Id in line #1 with the Id of the folder that you’ve created in the previous step.

This will essentially run the Apps Script code whenever someone submits a new form entry and upload files to a specific folder in Google Drive.

That’s it. Go to your Google Form and submit a new test entry. You should now see all the uploaded files neatly organized in a custom folder under the parent folder. The name of the custom folder is the unique Response Id that Google Forms automatically assigns to every form submission.

Also see:

Change the Google Forms Upload Folder
Move Google Forms Uploads to Shared Drive

View Details

The Document Studio add-on helps you create personalized PDF documents from Google Sheets. You can generate invoices, certificates, agreements, offer letters, student ID cards and other documents in bulk and save them to Google Drive.

Additionally, Document Studio now offers the option to protect the generated PDF documents with a password.

This functionality is particularly valuable in scenarios where the generated documents contain sensitive information that require extra protection to stop unauthorized access. For instance, you may generate invoices, or financial reports and protect them with a password before sharing them with clients or employees.

Add Passwords to PDF DocumentsLet’s walk through the steps of adding passwords to PDF documents generated from Google Sheets using Document Studio.

Prepare Salary Data in Google Sheets

We have a Google Sheet that contains the employee’s name, and the salary amount. We’ll use Document Studio to generate individual PDF salary slips for each employee and then add a password to each PDF document before saving them to Google Drive.

Create Template in Google DocsWe have created a salary slip template in Google Docs that contains placeholders for the employee’s name and the salary amount. The data from Google Sheet will be merged into this template to generate individual PDF documents for each employee.

Password Protect PDF DocumentsLaunch Document Studio in Google Sheets and create a new workflow. If you are new here, please refer to the step-by-step guide or watch this video tutorial to get started.

Inside the workflow, choose the Google Sheet that contains the employee data and the Google Docs template that you have prepared for the salary slips. Next, select the folder in Google Drive where the generated PDF documents will be saved.

Set the export format to PDF and enable the Password Protect PDF checkbox.

Unique Password for Each PDF DocumentYou can choose to use a common password for all the PDF documents or, for added security, set a unique password for each document. For this example, we’ll define a unique password for each PDF document using the employee ID and the first four letters of the employee’s name, all in uppercase.

For instance, if the employee ID is E345 and the employee’s name is Angus, the password for the corresponding PDF document will be E345ANGU.

We’ll make use of Scriptlets to derive a unique password for each PDF document dynamically.

{{ Employee ID }}{? "{{ Employee Name }}" | slice: 0,4 | upcase ?} The scriptlet above concatenates the employee ID with the first four characters of the employee’s name, converted to uppercase.

Generate PDF DocumentsSave the workflow and run it to generate the PDF salary slips for all employees. The generated PDF documents will be saved to the specified Google Drive folder and each document will be protected with a unique password.

Also see: Remove PDF Password from Gmail Attachments

View Details

Cake Studio is a local bakery that accepts orders through Google Forms. When a customer places an order, the customer automatically receives a UPI QR Code to make the payment. This QR Code is generated dynamically based on the order amount and the customer can pay the bill using any UPI app.

In the above screenshot, the customer ordered a Butterscotch Cake through Google Form and they received a customized UPI QR Code that includes the exact bill amount. The amount is calculated automatically based on the selected items in the Google Form.

Google Forms and UPI PaymentsThis tutorial explains how you can send custom UPI QR Codes to customers automatically whenever they place an order via Google Forms. We will use Google Sheets to calculate the bill amount and generate the QR codes, and Document Studio to send the emails with the QR codes to the customers.

Let’s see how you set up this workflow in a few simple steps.

Prepare Google Form for OrdersHere is a sample Google Form that we have created for Cake Studio. As you can see, and this is important, we have mentioned the amount of each cake in the options itself.

Prepare Google SheetOpen the Google Sheet that is linked to the Google Form. The Google Sheet will contain columns for the questions in the Google Form. We’ll now add extract columns that would help us generate the custom UPI QR codes.

You may find the Google Sheet with UPI formulae here

Add Columns to Google Sheet1. Bill Amount - This column will store the price of the cake that the customer has ordered. We’ll write a formula using the REGEXREPLACE function to extract the price from the selected option.

=ARRAYFORMULA(IF(ROW(D:D)=1,"Bill Amount", IF(NOT(ISBLANK(D:D)),REGEXREPLACE(D:D,".+₹ ",""),)))

2. Total Amount - Our second column will store the total bill amount which adds GST on the price of the cake. We’ll use Arrayformula to apply the calculation down the entire column.

=ARRAYFORMULA(IF(ROW(E:E)=1,"Total Amount", IF(NOT(ISBLANK(E:E)), E:E * 1.18,))) 3. UPI QR Code - The final column will store the custom UPI QR code that includes the total bill amount. We will use the built-in UPI function to generate the QR code.

A customized UPI QR Code will be generated for each order. The QR code will include the total bill amount so that the customer can make the payment without having to enter the amount manually.

Embed UPI QR codes in EmailNow that we have the UPI QR codes in the Google Sheet, we will use Document Studio to send emails to customers with the QR codes embedded in the email body.

Launch Document Studio in your Google Sheets and create a new workflow. Add a Send Email task to the workflow. Create a message template that includes the Embed Image marker to embed the UPI QR code in the email.

{{Embed IMAGE, UPI QR Code}} We have mentioned UPI QR Code as the second parameter in the above marker since it is the title of the column that contains the generated QR codes in our Google Sheet.

Save the Workflow and make sure to enable the Run on Form Submit option so that the emails are sent automatically whenever a new order is placed through the Google Form.

Test the UPI Payment WorkflowFill this Google Form and you should see a new row added to this Google Sheet with the bill amount and the UPI QR code. You’ll also receive an email with the UPI QR code embedded in the email body.

View Details

Google Secret Manager is a cloud service where you can store sensitive data such as passwords, database credentials, encryption keys or any other confidential information that you don’t want to hardcode in your application’s source code. You can also set up an expiration time for the secret and the Google Secret Manager will automatically delete the secret after the specified time.

The following guide explains how you can use Google Apps Script to access secrets stored in the Google Secret Manager. But before we proceed, let’s first create a secret in the Google Secret Manager.

Enable Google Secret Manager1. Open the Google Cloud Console and create a new project.

2. Go to the Library section of your Google Cloud project and enable the Secret Manager API.

3. Go to the IAM & Admin > IAM section of your Google Cloud. Click on Grant Access and add the Secret Manager Secret Accessor role to the Google account from which you want to access the secrets stored in the Google Secret Manager.

Create a Secret in Google Secret ManagerNow that you have enabled the Secret Manager API and granted access to your Google account, let’s create a new secret in the Google Secret Manager.

Go to the Secret Manager and click on the Create Secret button to create a new secret.

Give your secret a name and add the secret value - this could be a plain text string, or you can upload a binary file up to 64KB in size. If you would like the secret to expire after a certain time, you can set an expiration time for the secret.

In the above example, I have created a secret named MyBankPassword with the value MySuperSecretPassword. Google Secret Manager will automatically assign a version number (1) to the secret. You cannot change the secret value once it has been saved but you can create a new version of the secret with a different value.

Access Google Secret Manager from Google Apps ScriptNow that you have created a secret in the Google Secret Manager, let’s write a Google Apps Script that will fetch the secret value from the Google Secret Manager.

Go to script.new to create a new Google Apps Script project. Go to the Project Settings and enable the Show appsscript.json manifest file in editor option. Switch to the appsscript.json tab and add the following OAuth scopes to the manifest file:

{ "oauthScopes": [ "https://www.googleapis.com/auth/script.external_request", "https://www.googleapis.com/auth/cloud-platform" ]} Next, add the following function to your Google Apps Script project. Replace the project_id, secret_id, and version_id variables with the actual values of your secret.

The project_id is the project number of your Google Cloud project and can be found in the Google Cloud Console here.

After you have added the function to your Google Apps Script project, run the main function to fetch the secret value from the Google Secret Manager and log it to the Google Apps Script Logger.

View Details

The finance team has created a revenue dashboard inside Google Sheets to track the company’s sales performance over time. The dashboard has data tables and charts showing overall revenue and regional performance trends.

Here’s a screenshot of the Google Sheets dashboard:

The finance team wants to send a snapshot of this dashboard to the company’s management every Monday morning. They would like to automate this process so that the screenshot is captured automatically and sent via email without any manual intervention.

Let’s see how we can easily set up this automation with the help of Email Google Spreadsheets add-on. You can define the area of the Google Sheets dashboard that you want to capture, using the A1 notation, and the add-on will automatically take a screenshot of that area and send it via email to the recipients.

Open your Google Sheets dashboard, go to Extensions > Email Google Sheets > Open to launch the app. Click on the Create Workflow button and move to the Email step of the workflow.

Automate Screenshots of Google SheetsGo to the Email step of the workflow and specify the email addresses of the recipients. The subject and body of the email can include markers that will be replaced with the actual values from the Google Sheets dashboard.

For instance, if you wish to include the value of a cell in the email, you can use the marker {{SheetName!A1}} where SheetName is the name of the sheet and A1 is the cell address.

How to Insert Screenshot Markers

Expand the Markers section of the Email body and click on the Image marker for the sheet whose screenshot you want to include in the email. The marker will be added to the email body.

The format of the screenshot marker follows this pattern:

{{ Image:SheetName!SheetId,A1:B10 }} The SheetName is the name of the sheet, and A1:B10 is the range of cells that you want to capture in the screenshot. The SheetId is the unique id of the sheet that is used to identify the sheet in the Google Spreadsheet. The id will not change even if you rename the sheet.

Once the message is ready, click the Preview button to send a test email to yourself. Here’s how the email will look like:

If everything looks good, click the Continue button and set the schedule for the workflow.

Install Email Google Spreadsheets

View Details

A teacher would like to create separate Google Drive folders for each student in her class. Within each student’s folder, there will be additional subfolders for various subjects. This folder structure would make it easier for students to submit their assignments to the appropriate subject subfolders.

We’ve prepared a Google Sheet with the names of students, and the subjects they are taking.For example, consider a student named Emily Johnson, who is taking Maths, Science, and English. In this case, you need to create four new folders in total, with one main folder named ‘Emily Johnson’ and three subfolders within it for each subject: Maths, Science, and English.

Create Multiple Folders in Google DriveInstall the Document Studio add-on for Google Sheets. Open the spreadsheet with the student data and click on Extensions > Document Studio > Open to launch the add-on.

Create a new workflow inside Document studio, give it a descriptive name like Student Folders. Next, select the source worksheet that contains the student’s data and click on the Continue button to move to the next step.

On the next screen, you can specify the conditions for creating the folders in Google Drive. For instance, you may only want to create folders for students who are taking a specific subject or are in a particular class. Press the Continue button to move to the next step.

Choose the Google Drive task and then select Create Folder from the dropdown menu. Next, select the parent folder in Google Drive where the student folders should be created. You can choose to create folders inside your personal Google Drive or even Shared Drives.

Naming Folders and SubfoldersStudent FoldersNow that you have selected the parent folder, you need to define the name of the child folder along with its subfolder structure.

For the Subfolder Name field, we’ll put {{ Full Name }} / {{ Subject 1 }} and this will do two things:

Create a new folder for each student in the parent folder with the student’s name. The folder is only created if it doesn’t already exist.
Inside the student folder, create a subfolder for Subject 1 that the student is taking. The value of Subject 1 is replaced with the actual subject name from the Google Sheet.

You may also put the {{Email Address}} column in the Editors field to share the student folders with their email addresses automatically when the folder is created in Google Drive.

Create Additional Subject SubfoldersNow that you have defined the task to create subfolders for the first subject, you can add more tasks to create subfolders for other subjects as well.

Instead of creating a new task, you can simply duplicate the existing task and change the Subfolder Name field to {{ Full Name }} / {{ Subject 2 }} to create subfolders for the remaining subjects.

Now that workflow is ready, choose the Save and Run option to create the folders and subfolders in Google Drive. The folders would be created and a link to the folder would be placed in the spreadsheet itself. If a folder already exists, the link to the existing folder is placed in the spreadsheet.

This is how the folder structure would look like in Google Drive:

Also see: Create Folders for Google Form responses

View Details

When you create a Google Form, it is public by default meaning anyone who has the link to the form can submit a response. There forms, whether they are quizzes, polls or surveys, have no expiration date and they can collect unlimited number of responses until the form owner chooses to close it manually.

However, there are scenarios when setting limits on Google Forms can be beneficial. For instance:

Contests and Giveaways: Limit entries to a specific number, on a first-come, first-served basis, and close the form automatically.
Event Registrations: Set a closing date and automatically close registration forms after the event date.
Quizzes and Assignments: School teacher can add restrictions and keep the form open only during specific days and hours, mimicking in-class availability.

Limit Google Form ResponsesGoogle Forms doesn’t natively support the ability to schedule forms or limit responses. However, you can easily incorporate this functionality into your forms with the help of Form Notifications add-on for Google Forms. The add-on is primarily designed to send form responses in an email message but it also includes features to schedule Google Forms and limit responses.

How to Set Limits in Google FormsInstall the Forms add-on, go to your Google Form and click the add-ons menu (it looks like a puzzle icon).

From the menu, choose Email Notifications > Open App > Options > Limit Google Form Responses and you’ll see the settings panel as shown above. This is where you can easily control when and how many people can submit your Google Form.

Close Form after a Certain Number of ResponsesYou can specify the maximum number of responses that your Google Form should accept. Once the form has received the specified number of responses, it will automatically close itself and no new responses will be accepted.

You can also specify a custom message that will be displayed when someone accesses your closed form.

Close Form after a Specific Date and TimeYou can specify the exact date and time when your Google Form should be closed for new responses. The form will automatically close itself on the specified date and time and no new responses will be accepted.

You may also specify an open date and your closed Google Form will automatically open on the scheduled date. This can be useful for event registration forms where the registrations should be opened for public only on a specific date.

Open and Close Form on a Recurring ScheduleYou can easily set up a recurring schedule and keep your Google Form open only on specific days and within specific hours. The above example shows the form being available only on weekdays between 11:00 AM and 3:45 PM.

The Form limiter is written in Google Apps Script. You can find the source code on Github should you wish to roll out own form limiter.

Also see: How to Automate Google Forms

Important Things to Know* The form will close based on whichever condition is met first, either the response limit or the closing date. * All times mentioned are in the local timezone of the user’s browser who is setting up the form schedule and limits. * Due to Google add-ons’ technical constraints, the actual opening and closing times of the form may differ from your set times by about ±30 minutes. * If you would like to manually close your Google Form for new responses, open the Form, go to the Response tab and uncheck the Accepting Responses option. You can re-open the form anytime later by checking the Not Accepting Responses button.

View Details

You are using Google Forms to collect registrations for an upcoming event and would like to send email reminders to all the registered attendees a few days before the event date to ensure maximum attendance.

This tutorial explains how you can use Google Sheets and Document Studio for sending automatic reminders to all the registered attendees. We’ll primarily us Gmail to send the reminders via email but you can also use contact the event registrants via SMS or WhatsApp.

The reminders can be scheduled for any future date and time, and the email body can be personalized for each recipient. Let’s get started.

Prepare the Google SheetAssuming that you have already set up a Google Form for event registration, the responses will be collected in a Google Sheet linked to the form. In the same sheet, add a new column labeled Event Date and this will contain the event date. You can either copy and paste the date manually for all the rows in the sheet or use a formula to copy down the date automatically.

Create Email Reminder WorkflowInstall Document Studio and launch it inside the sheet associated with your Google Form. Inside the app, create a new workflow and provide a descriptive name for your workflow. Choose the specific worksheet in your Google Spreadsheet that contains the form responses. Click on Continue to move to the conditions page.

In the Conditions section, you can define specific criteria and the workflow will only run for Google Sheet rows that meet these conditions. For instance, you may want to send a reminder email only to attendees who have paid the registration fee. By default, the reminder workflow will run for all rows in the Google Sheet.

Configure the Email Task

On the tasks screen, choose the Send Email task since we want to send email reminders to the registered attendees. The email task will use the data from the Google Sheet to personalize the email message for each recipient.

Select Gmail as the email service provider though you can also use SendGrid, Amazon SES or any SMTP server for sending emails.

In the Send Email to field of Recipients section, select the question in your Google Form that contains the email addresses of the attendees.

Next, specify the email subject line and message body for your event reminders. You can create a personalized email template by using dynamic markers enclosed in double curly braces. These markers get replaced with actual cell values in the Google Sheet, ensuring each recipient gets a tailored message.

Save the email message and proceed to the next step.

Schedule Email RemindersOn the Trigger screen, uncheck the Run on Form Submit option since we want to schedule the email reminders for a future date and not immediately after the form is submitted.

Next, check the Add a time delay option and schedule the workflow accordingly. For this example, we have set the workflow to run 3 days before the event date. It is important that the event date column in your Google Sheets is formatted as a date else the configuration for comparing the event date to the current date may not work.

Click the Save button to activate your workflow. The workflow is now running in the background and will automatically send emails near the scheduled date to all the registered attendees.

Also see:

Send email reminders with Mail Merge
How to send appointment reminders via SMS
How to send reminders for due invoices

View Details

Google Forms is a perfect tool for collecting data, from event registrations to customer feedback. By default, your Google Form responses are added in a Google Sheet that is linked to the form.

However, there could be scenarios where you may want to store a copy of the form responses in a different location, such as a Microsoft Excel worksheet, a Zoho sheet, or even multiple Google sheets.

The ProblemFor this example, we have a Google Form where respondents can fill in their name, email address, city and other basic details. When a respondents submits the form, we would like that form response to be stored in an Excel sheet as well in addition to the original Google Sheet.

Let’s see how we can easily automate this with the help of Document Studio.

The SolutionLaunch Document Studio inside Google Forms and create a new workflow. Provide the workflow name and move to the Conditions page.

Let’s add some conditions here to filter out the form responses that we don’t want to be saved in the Excel sheet. For instance, you may want to save only the responses where the respondent’s city is either New York or Boston. Any form response that doesn’t meet this condition will be ignored.

Next, move to the Tasks page and add a new task. Choose Copy Row from the list of available tasks. Here we’ll choose the spreadsheet service the Google Form responses should be stored. You can choose from Google Sheets, Microsoft Excel, or Zoho Sheets.

Connect Microsoft Excel to Google FormsSelect Excel Sheets from the list of available spreadsheet applications, and click on the Link Microsoft Excel button. You’ll be asked to sign in with your Microsoft account and grant permissions to Document Studio to access your Excel files.

Once your Microsoft account has been linked, open the Excel workbook in your browser and copy-paste the spreadsheet URL as shown in the screenshot below. If you would like to copy the form response to a Zoho Sheet, you can choose Zoho Sheets from the list and link your Zoho account.

You also need to specify the name of the worksheet where the form responses should be copied. If the sheet doesn’t exist, Document Studio will create a new sheet in Excel with the specified name.

The next section is Field Mapping where you can specify which form fields should be copied to the Excel sheet. You also need to specify the corresponding column names in the Excel sheet where the form responses should be stored.

For instance, if the field in your Google Form is called “Zip Code”, you can map it to the “Postal Code” column in your Excel sheet. Similarly, if the field in your Google Form is “Email”, you can map it to the “Email Address” column in your Excel sheet.

| Google Form Field (Field Name) | Excel Column Name (Field Value) | | --- | --- | | Zip Code | {{ Postal Code }} | | Email | {{ Email Address }} |

Save your task and click on Continue to proceed to the triggers screen. Check the Run on form submit option so that every time a new form response is submitted, the workflow will be triggered, and a new row will be added to the specified Microsoft Excel or Zoho Sheet.

Related Tutorials1. Copy Google Form response to Zoho Sheet 2. Duplicate Google Form response in another Google Sheet

View Details

Are you developing Google Sheets functions or Google Workspace add-ons that tap into the power of Google Gemini AI or OpenAI? This tutorial explains how you can use Google Apps Script to verify that the API keys provided by the user are valid and working.

The scripts make an HTTP request to the AI service and check if the response contains a list of available models or engines. There’s no cost associated with this verification process as the API keys are only used to fetch the list of available models and not to perform any actual AI tasks.

Verify Google Gemini API KeyThe snippet makes a GET request to the Google Gemini API to fetch the list of available models. If the API key is valid, the response will contain a list of models. If the API key is invalid, the response will contain an error message.

This snippet works with Gemini API v1. If you are using Gemini 1.5, you need to update the API_VERSION variable in the script.

Verify OpenAI API KeyThe Apps Script snippet makes a GET request to the OpenAI API to fetch the list of available engines. Unlike Gemini API where the key is passed as a query parameter in the URL, OpenAI requires the API key to be passed in the Authorization header.

If the API key is valid, the response will contain a list of engines. If the API key is invalid, the response will contain an error message.

View Details

Imagine this scenario - your computer is connected to a Wi-Fi network but you do not remember the password that you previously used to connect to this particular WiFi network. Maybe you forgot the password over time, or perhaps the network administrator setup the Wi-Fi connection for you without providing the actual password to you.

Now, you need to connect a second device, such as your mobile phone, to the same Wi-Fi network, but you’re unsure how to retrieve the password.

Recover the Forgotten Wi-Fi PasswordIn this situation, you may either request the password from the Wi-Fi owner or, a simpler alternative, open the command prompt on your computer to retrieve the saved Wi-Fi password in one easy step. This technique works on both Mac and Windows computers.

Find the WiFi Password on WindowsOpen the command prompt in administrator mode. Type cmd in the Run box, right-click the command prompt icon and choose Run as Administrator). Now enter the following command and hit enter to see the WiFi password.

netsh wlan show profile name=labnol key=clear Remember to replace labnol with the name of your Wireless SSID (this is the name of the Wi-Fi network that you connect your computer to). The password will show up under the Security Setting section (see screenshot).

If you would only like to see the password and not the other information, use the findstr command:

netsh wlan show profile name=labnol key=clear | findstr Key

If you do not see the password, probably you’ve not opened the command prompt window as administrator

Show the WiFi Password on Mac OSIf you’re using a Mac and have previously connected to a Wi-Fi network but can’t remember the password, you can easily retrieve the password from Keychain. macOS uses Keychain to store various login credentials, including Wi-Fi network passwords.

Open Spotlight (Cmd+Space) and type terminal to open the Terminal window on your Mac. Now type the following command and hit enter (replace labnol with your WiFi name).

security find-generic-password -wa labnol You’ll be prompted to enter your admin username and password to access the OS Keychain and the Wi-FI network password would be displayed on the screen in plain text.

Reveal the WiFi Password on LinuxThe process of retrieving the Wi-Fi password on Linux is similar to the method used on macOS. Open the Terminal window and type the following command to view the Wi-Fi password.

sudo cat /etc/NetworkManager/system-connections/labnol | grep psk= Remember to substitute labnol with the wireless name (SSID) of your current Wi-Fi network. The value of the field psk is your WiFi password.

If you don’t know the exact name of the Wi-Fi network, you can use the following command to list all the stored network configurations and their corresponding passwords:

sudo grep psk= /etc/NetworkManager/system-connections/* Start WLAN AutoConfig (Wlansvc Service)If you are using this technique to retrieve the WiFi password on a Windows computer but getting an error that says - “The Wireless AutoConfig Service (wlansvc) is not running” - here’s a simple fix:

Click the Windows Start button and type services.msc in the Run box to access Windows Services. Here go to the WLAN Autoconfig service and make sure that the status is Running. Else right-click the WLAN AutoConfig service, select Properties and go to Dependencies. Check all the dependencies to make sure they are all running.

View Details

You have a Google Form and you would like to send an auto-confirmation emails to the person as soon as they submit the form. The autoresponder email message can contain a custom note (like an acknowledgement saying that you have received their form entry) and also a copy of the form answers that that they have submitted.

These auto-responders are similar to canned responses in Gmail but for Google Forms. You may use the technique for sending welcome messages, acknowledge support requests, and more. Here’s a sample confirmation email that was sent through Google Forms:

A sample auto confirmation email sent through Google Forms

Send a Confirmation Email to the Form SubmitterThe other day I got an email from N.Vamsi asking me how to send these confirmation emails using Google Forms?

Would you mind telling me how you have set up auto email updater for inputs taken from Google forms. I have seen your video tutorial on setting up Google forms and getting input values to an email address but auto email responder is something new! Do you have any tutorials for that as well?

This is easy and you can can add the auto-reply feature to your Google Forms in less than a minute. Here are the steps involved:

Create a new Google Form with one or more fields. You can also use an existing form but do make sure you have a field where you would be asking for the email address of the form respondent. This should be a mandatory field.
Install the Google Forms add-on, then go to the add-ons menu inside forms, choose Email Notification for Forms and select Create New Rule.
Enter your name, choose your Gmail alias that you wish to use for sending confirmation emails and check the “Notify Form Submitter” option. Select the form field that you are using to get the email address of the respondent.
On the next screen, customize the email subject and message as described in the Google Form Email tutorial.

Create the rule and you’re done. When anyone submits the Google Form, they’ll get an automatic confirmation email in HTML format and copy of the email data will also be cc’ed to you so you are in the loop.

View Details

Do you have image files in your Google Drive with generic names like IMG_123456.jpg or Screenshot.png that offer no context about what the image is about? Wouldn’t it be nice if you had an assistant that could look at these images and automatically suggest descriptive filenames for the images?

Rename Files in Google Drive with AIWell, you can use Google’s Gemini AI and Google Apps Script to automatically rename your files in Google Drive in bulk with a descriptive name based on the image content.

The following example uses Google’s Gemini AI but the steps can be easily adapted to OpenAI’s GPT-4 Vision or other AI models.

To get started, open script.new to create a new Google Script and copy-paste the following code snippets in the editor. You may also want to enable the Advanced Drive API from the Google Script editor under the Resources menu.

Get the list of files in a folderThe first step is to get the list of files in a folder. We will use the Drive.Files.list method to get the list of files in a folder. The search query contains the mimeType parameter to filter the results and only return Drive files that are image formats supported by the Google Gemini AI.

const getFileAsBase64 = (thumbnailLink) => { const blob = UrlFetchApp.fetch(thumbnailLink).getBlob(); const base64 = Utilities.base64Encode(blob.getBytes()); return base64;}; 3. Get the suggested filename from Google Gemini AIWe’ll use the Google Gemini API to analyze the visual content of an image and suggest a descriptive filename for the image. Our text prompt looks something like this:

Analyze the image content and propose a concise, descriptive filename in 5-15 words without providing any explanation or additional text. Use spaces for file names instead of underscores.

You’d need an API key that you can generate from Google AI Studio.

const renameFilesInGoogleDrive = () => { const folderId = 'Put your folder ID here'; const files = getFilesInFolder(folderId); files.forEach((file) => { const { id, thumbnailLink, mimeType } = file; const base64 = getFileAsBase64(thumbnailLink); const name = getSuggestedFilename(base64, mimeType); Drive.Files.update({ name }, id); });}; Google Scripts have a 6-minute execution time limit but you can setup a time-drive trigger so that the script runs automatically at a specific time interval (say every 10 minutes). You may also extend the script to move the files to a different folder after renaming them so that they are not processed again.

The full source code is available on GitHub

View Details

Google Forms are a perfect tool for creating online forms and surveys. The forms are responsive and mobile friendly, and they look beautiful as the color schemes and typography are based on Material design philosophy. Whether you need a basic Contact Me form to a more complex Event Registration form, there are readymade form templates to get you up and running in minutes.

Google Forms offer several advantages. The form responses are automatically saved in a Google spreadsheet that can be easily exported to other formats like PDF or CSV. Unlike other online form builders that follow the freemium model, Google Forms are absolutely free and can accept unlimited responses. You can also schedule Google Forms to stop accepting responses after a given date.

There’s one limitation though with Google Forms.

Google Forms can send email notifications as soon as people submit your form but the form response submitted by the respondent is not included in the email message. You’ll have to open the Google Form, or the associated Google Spreadsheet that is collecting the form responses, to view the submitted data. Not a very convenient option.

If you would like to receive the submitted data in an email message, the Email Notification for Google Forms add-on can help. The add-on, written using Google Scripts, gets triggered whenever a user submits your Google Form and emails the form data to one or more email addresses specified by the form owner in rich HTML or PDF formats.

How to Receive Google Forms data in EmailHere’s how you can add email notifications to any Google Form in 5 easy steps:

Install the Google Forms add-on, then click the add-ons icon inside the Forms Editor (it is the shape of a puzzle icon), choose the Email Notification for Forms menu and then click the Create New Rule menu.
The configuration window will open inside the form editor. Enter your full name (or the sender’s name) and also specify the list of one or more email addresses (comma separated) who should receive automatic email notifications when a form is submitted.
If you would like to send an auto-confirmation email to the form’s respondent after they submit the form, check the Notify Submitter option. You’ll also need to select the question in your Google Form that asks the respondent for their email address.
Go to the next screen and enter the subject line and message body of the email notification. You can customize the emails and include any of the {{form fields}} in the subject or body as explained in the next section.
Click the Create Rule button to activate the form notification. Now open your Google Form, submit a test entry and then go to your Gmail Sent Items folder to see the email notification that has gone out to the recipients.

Google Forms EmailHow to Create Customized Emails AutorrespondersYou can easily include dynamic fields from the Google form into the email subject line and message body. For instance, if you have a question titled “Name?” in the Google Form, you can include the template variable {{Name?}} in the subject or body and they’ll be automatically replaced with the actual data entered by the user.

Other than the form fields, the add-on also supports dynamic form fields like:

| Variable | Replaced with | | --- | --- | | {{Form Name}} | Name (title) of your Google Form | | {{Form Url}} | Direct link to edit a user’s response | | {{Response Number}} | The current number of Form entry | | {{All Answers}} | Full response, formatted as a table |

Format your Notification Emails with HTMLThe form notification emails are created in HTML and you can therefore use any HTML tags to format the emails. For instance, if you an enclose the text inside <b>tag</b>, it will turn bold in the email while <em>tag</em> will italicize the text. Use the span tags to change the font color as show in the following example:

<span style="color:#f00;">Thank you!</span> If you would like to include your brand’s logo in the email message, simply upload the image and copy-paste the HTML code provided into the email body.

Conditional Email Notifications using If-Then LogicThe forms add-on can also send email notifications to different people based on the answers that are filled in the form. For instance:

In a sales form, you can send a notification to a particular sales manager based on the product category selected by the user.
In a school form, you can send notifications to the concerned teacher based on the grade selected by the parent / student.

You can use simple rules, like region equals North America, or build more complex rules using regular expressions. Conditional notifications are however available in the premium version only.

Send Conditional Email with Google Forms

Google Forms do not allow file uploads but you can use Google Scripts to allow anyone to upload files via a form to your Google Drive.

For more answers, please see the Google Forms Help Center.

View Details

The availability of third-party add-ons for Google Docs, Sheets and Google Slides have certainly made the Google productivity suite more capable and useful. If you haven’t tried them yet, open any Google document or spreadsheet in your Google Drive and look for the extensions menu near Help. Google Workspace users may have to ask their admin to enable support for add-ons for the organization.

For starters, Google add-ons are like extensions for Chrome. Extensions add new features to the Chrome browser and add-ons extend the functionality of Google Office applications.

Anyone can write a Google add-on with some basic programming knowledge for writing HTML and CSS for styling the add-on. The server side code is written in Google Apps Script which is similar to JavaScript but runs on the Google Cloud.

Google Apps Script vs Google Add-onsGoogle Add-ons are written in the Google Apps Script language but while regular Google Scripts can work on any document in your Google Drive, add-ons only work against the document or sheet that’s currently open in your browser.

The other big difference is that you can view the source code of regular Google Scripts while in the case of add-ons, the code is hidden from the end user. This helps developers protect their code but a downside is that the user has no clue about what’s happening behind the scenes.

We have seen issues with Chrome extensions and add-ons for Google Docs can be a target as well. For instance, an add-on can possibly email a copy of the current document or sheet to another email address? Or maybe it can share a folder in Google Drive with someone else. The good part is that add-ons listed in Google Workspace have been tested and reviewed by Google and, they go through a security review process if it requires access to sensitive scopes (like sending Gmail or accessing Google Drive).

The Best Add-ons For Google Docs, Sheets and Google SlidesThe Google Workspace marketplace lists hundreds of Google add-ons and here are some of favorite ones that you should have in your Google Docs and Sheets. The are compatible with both GSuite and consumer Google accounts.

Mail Merge for Gmail - Send personalized emails with emails to multiple email recipients with Mail Merge for Gmail and Google Workspace.
Document Studio - Generate certificates, invoices, and other documents automatically from source data in Google Sheets or Google Form submissions.
Download Gmail Emails - Download your Gmail messages and attachments in Google Drive for archiving.
Email Scheduler for Gmail - Schedule emails inside Gmail for sending later at a specific date and time. Send repetitive emails that go on a recurring schedule.
Google Drive Permissions Auditor - Know who has access to your files in Google Drive.
Gmail Address Extractor - The add-on extracts the email addresses from the header and body of email messages for preparing a mailing list.
Creator Studio for Google Slides - Convert Google Slides presentations to animated GIF and MP4 movies.
Notifications for Google Forms - Get Google Forms responses in an email message when people submit your forms. Send customized email notifications to respondents.
Email Google Spreadsheet as PDF - Convert and email Google Spreadsheets as PDF, Excel or CSV to multiple people. Email sheets manually or on a recurring schedule.
Bulk Gmail Forward - Easily forward one or more email threads from Gmail to any other address.

Related tutorial: How to Create a Google Docs Add-on

View Details

John is a public relations professional and he is often required you to send press releases and event invites to journalists, bloggers and influencers via email.

Reaching out to individual journalists can be slow so how do you send the same email to multiple people in one go? Some people use the BCC option in Gmail - compose a single email, put email addresses of all recipients in the BCC field and hit send.

That’s obviously the easiest option for sending bulk emails through Gmail but such generic email pitches are unlikely to get noticed.

Send Email Pitches with GmailIn this tutorial, I’ll show you how you can use send personalised email pitches to your media contacts through Gmail and Google Sheets. You’ll be able to schedule your press releases in advance and also track which influencers have seen your emails.

The big advantage is that, unlike other mass email programs, messages sent via Mail merge are delivered just like regular emails directly in the Inbox.

Let’s get started:

How to use Mail Merge with GmailGo to the Google Workspace Marketplace and install the Gmail Mail Merge addon. You’ll need to grant certain permissions so that the add-on can send emails from your Gmail accounts. It also needs permission to attach files from your Google Drive.

Now that the add-on is installed, type sheets.new in your browser to create a new Google Sheet. Inside the sheet, go to the Addons menu, choose “Mail Merge with Attachments” and then select the “Create Merge Template” menu.

Your sheet now has all the essential columns that are required for running mail merge but can add more columns. We’ll add Location and News Outlet columns as shown in the above screenshot.

The next task is to get the media list into this Google Sheet. You can either import groups from Google Contacts, from your Mailchimp campaigns or, if you are an Excel user, export as CSV and directly import the CSV file into Google Sheets.

Create an Email Template for MergeOpen your Gmail, create a new email message (see screenshot) and save the template in your drafts folder. The email can have {{markers}} enclosed in double curly braces and these are replaced with actual values from the Google Sheet in your outgoing emails.

When we enclose some text inside double curly braces, it becomes a marker and these are replaced with values in the sheet. You can also add emojis in the subject and body.

Next, we can add some attachments to our email template. You can either upload files from your computer or you can bring directly from your Google Drive.

Configure and Run Mail MergeNow that our email template in Gmail is ready, go back to your Google Sheet and choose Configure mail merge from the Mail Merge menu under add-ons.menu.

Follow the step by step wizard to configure merge but there are a few important things you should know.

You can send emails on behalf of any email address that is associated as an alias in your Gmail account. So an intern can send emails on behalf of the manager while logged into her own Google account.
You can add a CC or BCC email address and all your merged emails will be copied to them as well. Remember that Gmail counts every CC or BCC recipient as a separate email and thus it counts towards your daily email quota.
Mail Merge includes email tracking so you can know who opened your email or clicked the links. For email newsletters, you can even include an unsubscribe option in your email messages.

After the configuration is done, go to the Send Email Section, select the Send a test email option and hit the Go button.

Mail Merge will take the merge data from the first row in the Google Sheet and send you a test email. You can find the test email in your Gmail Sent folder.

If you are satisfied with the test email, go back to the Google Sheet, select the Run Mail Merge option and hit Go to perform a live merge. That’s it.

The emails will be dispatched immediately and you can check the Mail Merge Status column in the sheet to track the sending progress.

You can add more rows in the Google Sheet to send the same email to another batch of people and when you hit send, Mail Merge will automatically ignore the rows that have already been sent the email.

Mail Merge - Tips and Tricks1. You can schedule emails - just add a date and time in the Scheduled Date column and run merge again to schedule the emails. 2. If you have a lot of rows in the sheet, you can skip sending emails to specific rows by hiding those rows in the Google Sheet. Alternatively, you may use filters in Google Sheets to only show rows that match certain criteria. When you run merge again, emails will be sent to visible rows only. 3. If you wish to cancel scheduled emails, you can either empty the scheduled date column or you can go to the Mail Merge menu, choose Help and click the Cancel Scheduled Mail option. 4. With Mail merge you can also send different attachments to different people. See how-to guide. 5. You can also create drafts with Mail Merge and this is a handy option if you wish to review the emails manually before sending them to real people.

Get Gmail Mail Merge

View Details

The Google Forms Email Notifications add-on lets you send Google Form responses in an email message as soon as a respondent submits the form. The email template can be customized to include the form answers or the form edit link.

You can also set up conditional email notifications for Google Forms where the form responses are sent to different email addresses based on the form answers filled-in by the respondent.

In the above example, we have a Google Form that asks the respondent to select the sessions they are interested in attending for an AI workshop. It is a mulitple choice checkbox question as the respondent can select one or more sessions. The form responses are then sent to different email addresses based on the sessions selected by the respondent.

Set Up Conditional Email Notifications for Google FormsInstall the Google Forms add-on and setup a new rule. Enable the Conditional Notifications option and add a new condition as shown in the screenshot.

The first rule says that if the respondent has selected either “Generative AI” or “Prompt Engineering” for the Sessions question, the email should be sent to peter@example.com. We thus choose “Any of” as the condition and add the two session options in the condition separated by a comma.

Similarly, if the respondent has selected “The future of AI” as the session, the email should be sent to kiran@example. For all other sessions, the email should be sent to angus@example.com so we’ll put that email address in the “No Match Found” section.

You can also customize the email message that is sent to the respondents. The email message can include individual form answers, images, QR Codes and even files from Google Drive.

View Details

Prefilled Google Forms, where some of the fields in the form are pre-populated with answers you already have, make the process of filling out your forms easier and faster.

Your contacts are more likely to fill out the form as it takes less time for them to complete the remaining fields.
The form respondents are less likely to type incorrect data in fields, like the employee ID, that are pre-populated.
The forms feels more personal when people see their name and other personalized information pre-filled in the form.

Create Pre-filled Google Forms with Google SheetsThis step-by-step video tutorial explains how you can create pre-filled Google Forms with dynamic information from a Google Sheet. You can then use Mail Merge or Document Studio to automatically send the prefilled forms to your contacts in bulk with Gmail.

In our example, the organization maintains their employee database in a Google Spreadsheet and they want to give employees an option to self-update their details in the spreadsheet with the help of Google Forms.

If you look at employee records in the Google Sheet carefully, you’ll find that only some details of the employees are missing in the sheet. This is a perfect use case for using prefilled Google Forms as it be wasting employee productivity if we send them a blank Google Form and require them to fill out every single field.

For instance, in row #2, we know the location and gender of Angus but his date of birth is unavailable in our records. For row #4, the employee ID and email is known but Kiran’s other details are missing.

Create the Google FormTo build this workflow, we’ll create a Google Form with fields corresponding to the columns in the source Google Sheet. Here’s how the final form would look like:

Generate the Prefilled Form LinkInside the Google Form editor, click the 3-dot menu choose the Get pre-filled link option. Here, fill in every field with dummy data that is easy to recognize and replace later. Once the fields have been filled, click the Get Link button to generate the prefilled link and copy it to your clipboard.

The link to the prefilled Google Form would look something like this.

https://docs.google.com/forms/d/e/xxxx/viewform ?entry.1808207196=EMPLOYEEID&entry.1663131167=EMPLOYEENAME &entry.1819275928=2020-06-03&entry.2071782719=Female &entry.175059757=Hyderabad It’s long and complex but if you take a closer look, this is simply a collection of name and value pairs appended to the Google Form URL. Google Forms will assign a unique id to each field in the form and these are appended to the Form URL with your pre-populated value.

For instance, the Name field in your Google Form is internally represented as entry.1663131167 in the form URL. If we replace the parameter value EMPLOYEENAME in the URL with another value, that would be pre-populated in the Google Form.

And this is exactly what we’ll do to create personalized prefilled links for all the rows in our Google Sheet.

Add Form Formulas in Google SheetInside your Google Spreadsheet, create a new sheet and rename it Form Link. Paste the prefilled Google Form link in the first cell (A1) of this blank sheet.

Next return to the Google Sheet that has the employee database and create a new column, say Google Form Link.

Now we need to replace the dummy values in our prefilled link with the actual values from the rows in the sheet and this can be easily done with SUBSTITUTE function of Google Sheets.

For instance, we need replace EMPLOYEENAME in the prefilled link with real names that are in column B of the spreadsheet. Our formula would be something like this:

=SUBSTITUTE('Form Link'!$A$1, "EMPLOYEENAME", B2) We’ll feed the result of this formula into another SUBSTITUTE function to replace another field, say EMPLOYEEID.

=SUBSTITUTE( SUBSTITUTE('Form Link'!$A$1, "EMPLOYEENAME", B2), "EMPLOYEEID", A2) This has to be repeated for every prefilled field in the Google Form.

If your prefilled data contains space, you need to wrap the results into another SUBSTITUTE function that will replace all occurrences of spaces with the plus symbol.

Our final prefilled link would be:

=SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE('Form Link'!$A$1, "EMPLOYEEID", A2), "EMPLOYEENAME", B2), "2020-05-31",E2), "Female", C2), "Hyderabad", D2), " ", "+") You can test the workflow using this prefilled Google Form that will write your form submission in a new row of this Google Sheet.

Copy-down the Google Forms FormulaYou may use ArrayFormula to copy down formulas or, if you have only a few rows, select the first cell and drag the crosshair to the last row in the formula column as shown below:

Handling Dates in Google FormsIf you plan to pre-fill dates in the Google Form, you need rewrite your dates in the Google Sheets in a format that Google Forms can recognize.

This is easy to implement. Just select the column in your Google sheet that contains the dates, then go to the Format menu, choose Number > More Formats > More date and time format and choose the YY-MM-DD format.

Also see: Create PDF from Google Form Responses

How to Email Prefilled Google Form LinksYou can use Mail Merge with Gmail to send the prefilled forms to all the email addresses in one go from the Google Sheet itself.

When composing the email template for merge, select any text in the email body and convert it into a hyperlink. You can put the title of the column - {{Google Form Link}} as the hyperlink and this would be replaced with your Google Form link.

Please watch the Mail Merge tutorial to learn more.

View Details

Credit card companies and banks often send their financial statements in the form of password-protected PDF files. These PDF attachments may be encrypted with passwords derived from the last four digits of your Social Security number, your birthdate, or any unique combination.

If you are to open these password-protected PDF files, you’ll have to enter the password every time. You can permanently remove passwords from PDF files using Google Chrome but that’s a manual and time-consuming process especially if you have a large number of password-protected PDF attachments in your Gmail inbox.

Important Note - This tutorial will only help you decrypt password-protected PDF files for which you know the password. You cannot use this method to unlock PDF files where you don’t know the password.

Imagine the convenience of having an automated method to download all your password-protected PDF attachments from Gmail directly to Google Drive as unencrypted PDF documents. This would completely eliminate the need to enter passwords to view your PDF files. The other advantage is that your PDF files will become searchable in Google Drive.

Save Password Protected PDF Files to Google DriveWe’ll use the Gmail to Google Drive add-on for Google Sheets to automatically download password-protected PDF attachments from Gmail to Google Drive.

Build the Gmail Search QueryAfter the addon is installed, go your sheet.new in the browser and choose Extensions > Save Emails and Attachments > Open App. Create a new workflow and provide the Gmail search query that will help you find all the password-protected PDF files in your Gmail mailbox.

The search query can be of the form filename:pdf has:attachment from:bank.com where you can replace bank with the name of your bank or credit card company.

Choose the Google Drive FolderOn the next screen, select the folder in your Google Drive where you wish to save the decrypted PDF files. You can also choose to save the PDF files in a sub-folder of your Google Drive or even Shared Drives.

In our example, we have set the sub-folder for downloading emails as {{Sender Domain}} / {{Year}} / {{Month}} / {{Day}} so the PDF files would be saved in a folder structure like bank.com/2024/01/15.

Unencrypt PDF AttachmentsOn the next screen, enable the option that says Save Attachments and choose PDF for the Allow file extensions list. Thus, only PDF files will be saved to Google Drive and all other email attachments will be ignored.

Next, enable the option that says Save PDF Attachments without password and provide the password that you use to open the PDF files. This is the password that you would normally enter when opening the PDF files in Adobe Acrobat or Google Chrome.

That’s it. Click the Save button to create the workflow and the add-on will now run in the background and save all your password-protected PDF attachments from Gmail to Google Drive as decrypted PDF files that can be opened without entering the password.

View Details

Base64-encoded images can be embedded directly inside HTML emails without having to host the image on a remote server. This offers several advantages:

Avoids tracking - External images are often used by email marketers to track email opens.
Reduced spam potential - Some email servers may reject emails that contain external images.
No broken images - If the image is hosted on a remote server, it may not load if the server is down.

Gmail, however, does not support base64 images in HTML emails. If you try to send an email with base64 images to a Gmail or Google Workspace account, the image will not be displayed in the email body but will be displayed as an attachment instead.

The workaround is to convert the base64 image to a blob and then embed the blob in the email. We use a similar technique to embed base64 encoded images in emails sent from Mail Merge and Document Studio.

The following Google Apps Script function will convert all base64 images in an HTML email to blobs and then send the email using the Gmail service.

// The original HtmlMessage may contain base64 images in the <img> tags.// <img src="data:image/png;base64,R0lGODlhQABAAMQAAJSWl..." />const sendEmailWithGmail = ({ to, subject, htmlMessage }) => { let htmlBody = htmlMessage; const inlineImages = {}; // Find all base64 image tags in the html message. const base64ImageTags = htmlBody.match(/<img src="data:image\/(png|jpeg|gif);base64,([^"]+)"[^>]*>/gm) || []; base64ImageTags.forEach((base64ImageTag) => { // Extract the base64-encoded image data from the tag. const [, format, base64Data] = base64ImageTag.match(/data:image\/(png|jpeg|gif);base64,([^"]+)/); // Convert the base64 data to binary. const imageByte = Utilities.base64Decode(base64Data); // Create a blob containing the image data. const imageName = Utilities.getUuid(); const imageBlob = Utilities.newBlob(imageByte, `image/${format}`, imageName); // Replace the base64 image tag with cid: image tag. const newImageTag = base64ImageTag.replace(/src="[^"]+"/, `src="cid:${imageName}"`); htmlBody = htmlBody.replace(base64ImageTag, newImageTag); inlineImages[imageName] = imageBlob; }); MailApp.sendEmail({ to: to, subject: subject, htmlBody: htmlBody, inlineImages: inlineImages, });}; Also see: Send Emails with Gmail API

View Details

I have a Stock tracker spreadsheet built inside Google Sheets that keeps track of my fictional stock portfolio. The stock prices in the Google Sheet are updated automatically using the GOOGLEFINANCE function.

I would like to set up a daily trigger that runs every day at, say 4pm, and sends me an email with the screenshot of the spreadsheet. This way I can keep track the performance of my stocks without having to open the spreadsheet every day. Let’s see how this process can be easily automated with the help of Email Google Sheets add-on.

Inside your Google Sheet, go to Extensions > Email Google Sheets > Open to launch the app. Click on the Create Workflow button to create a new automation workflow that will send you an email with the screenshot of the spreadsheet.

Go to the Email step of the workflow and put the email address of the recipients in the To, Cc and Bcc fields. You can then add a custom subject and message in the email to include values from the spreadsheet.

For example, in our workflow, the subject line says The portfolio value on {{Date}} is {{Watchlist!B5}} which will be replaced by the current date and the value of the cell B5 in the Watchlist sheet.

The message body of the email includes {{ Watchlist!B7:I16 }} which will be replaced by the cell range B7:I16 in the Watchlist sheet. All the formatting of the cells will be preserved in the email. You can click the Markers button to see the list of all the available markers that you can use in the email.

Once the message is ready, click the Preview button to send a test email to yourself.

Here’s what the email looks like in my Gmail inbox. All the formatting of the cells is preserved in the email. If you prefix the marker with Image:, the marker will be replaced by a high-resolution screenshot image of the cell range.

If everything looks good, click the Continue button to move to the next step. Here you can choose the frequency of the workflow. You can set it to run daily, weekly, monthly or even on a custom schedule.

That’s it. Your workflow is now set up and will run automatically around the time you have specified.

Also see:

Install Email Google Spreadsheets
How to Email Charts in Google Sheets
How to Email Timelines in Google Sheets

View Details

The Document Studio add-on lets you create pixel-perfect documents from your data in Google Sheets and Google Forms. For instance, someone can fill out your Google Form, upload a photo and the add-on will automatically generate a PDF document with the uploaded photo and form answers (tutorial).

Embed Images in DocumentsOne of the most unique features of Document Studio is that it can embed images in the generated documents. For instance, you could have a Google Form where the respondent uploads an image and these can be embedded inline in the generated PDF document.

All you have to do is add the following marker in your Google Document template and the add-on will replace it with the uploaded image.

{{ Embed IMAGE, File Upload Question }} You can also resize the image uploaded in Google Forms by specifying the width and height values.

{{ Embed IMAGE, File Upload Question, width=300 }} Embed Multiple Images in DocumentsThe above example works great if you have a single image upload question in your Google Form. What if the file upload question in your Google Form allows multiple images to be uploaded and you wish to embed all the images in the generated document?

Document Studio can only embed a single image in a single marker, but there’s a workaround to embed multiple images uploaded in Google Forms in the generated document.

Here we have a question in Google Forms that allows the user to upload multiple images. The question is named Photos and it allows the user to upload up to 5 images in their Google Drive.

When a respondent uploads multiple files and submits the form, a new row is added in Google Sheets and the file URLs are stored in the column as comma-separated values.

Go to the Google Sheet that is storing the form responses and add 5 new columns next to the column that is storing the file URLs. Give each column a name like Photo 1, Photo 2 and so on.

=ARRAYFORMULA(IF(C2:C<>"", TRIM(SPLIT(C2:C, ",")), )) Next, put the above SPLIT formula with ARRAYFORMULA in the first photo column to split the comma-separated values in the file URL column into multiple columns.

The next step is to add the {{ Embed IMAGE }} marker in your Google Document template but this time, instead of using the file upload question, we’ll use the new columns that we have created in the Google Sheet.

{{ Embed IMAGE, Photo 1 }}{{ Embed IMAGE, Photo 2 }}{{ Embed IMAGE, Photo 3 }}{{ Embed IMAGE, Photo 4 }}{{ Embed IMAGE, Photo 5 }} Related Tutorials:

Generate personalized images from Google Forms
Create business cards from Google Sheets
Using Formulas with Google Forms Sheets

View Details

The Mail merge add-on for Gmail lets you send personalized emails to your contacts in bulk. You can pull file attachments from Google Drive and include them in the outgoing emails. The emails can be sent immediately or scheduled for sending at a later date and time.

If you are an Outlook or Microsoft 365 user, you can still use Google Sheets to send personalized emails to multiple people at once with the help of Mail Merge. All you have to do is add your Outlook account to Gmail as an alias and the Mail Merge add-on will be able to send emails through your Outlook account.

Generate an App Password for OutlookThe first step is to generate an app password for your Outlook / Microsoft 365 account.

Go to account.microsoft.com/security, sign-in with your Outlook account and choose Advanced security options.
Scroll down to the App passwords section and click Create a new app password.

Add your Outlook Account to GmailGo to your Gmail account and choose Settings from the gear icon. Switch to the Accounts tab and choose Add another email address under the Send mail as section.

You can now add your Outlook account as an alias in Gmail. Enter your name and Outlook email address and click Next Step. Make sure you choose Treat as an alias.

On the next screen, enter the following details:

SMTP Server: smtp-mail.outlook.comPort: 587Username: Your Outlook email addressPassword: The app password you generated earlier

Click Add Account and Gmail will send a verification code to your Outlook email from noreply@google.com. Enter the code in Gmail and your Outlook account will be added as an alias in Gmail.

Sending Email with Outlook in Mail mergeNow that you have added the Outlook email address as an alias in your Google account, go to sheets.new to open a new Google Spreadsheet and configure Mail merge. You will now be able to send emails from your Outlook account through the Mail Merge add-on.

View Details

Imagine you're working with a lengthy Google Document, or a Google Slides presentation, and you need to extract all the embedded images from…

View Details

This video tutorial explains how you can automatically send Slack messages from Google Forms with the help of Document Studio . video…

View Details

Cron is a scheduling tool that helps you run tasks at recurring intervals. You use a cron expression to specify the exact timing for your scheduled task. For example, if you want a schedule to run every week day at 8:30 pm, the cron expression would look like this:

30 20 * * 1-5

Cron Expressions

Here are some more practical examples to help you understand the cron expression.

Cron Expression	Description
0 0 * * *	every day at midnight
0 /2 * *	every 2 hours
0 10 * * FRI,SAT	every Friday and Saturday at 10 am
30 9 /15 *	at 9:30 am on every 15th day
0 0 1 /3	first day of every quarter

Time Triggers in Google Apps Script

Google Apps Script supports time-driven triggers to help you run tasks in the background automatically. For instance, you can setup a time trigger in Apps Script to email spreadsheets every weekday. Or a trigger to download emails from Gmail to your Google Drive.

Time-based triggers in Apps Script have certain limitations, particularly when it comes to setting up recurring schedules. For example, if you want to create a simple cron job that runs every weekend at around 3 PM, you’d need to set up two separate triggers like this:

function createTimeTrigger() { ScriptApp.newTrigger('functionName') .timeBased() .everyWeeks(1) .onWeekDay(ScriptApp.Weekday.SUNDAY) .atHour(15) .create(); ScriptApp.newTrigger('functionName') .timeBased() .everyWeeks(1) .onWeekDay(ScriptApp.Weekday.SATURDAY) .atHour(15) .create();}

Managing more complex triggers, like one that runs at 10 PM on the 15th of every alternate month, becomes even more challenging. In contrast, writing a cron expression for this is quite straightforward: 0 22 15 */2 *. Similarly, creating a time trigger that runs at 10:30 am on the last day of every month would involve much more code that writing a cron expression: 30 10 L * *

Google Script meets Cron Expressions

The cron syntax is powerful and supports complicated recurring schedules but, unfortunately, it is not available inside Google Apps Script. But we now have a easy workaround.

We can write our time trigger schedules in cron expressions directly inside Apps Script.

Load the Cron Parser Library

We will use the popular croner library in Apps Script to parse cron expressions and calculate the upcoming schedules.

const loadCronLibrary = () => { const key = 'croner@7'; const url = 'https://cdn.jsdelivr.net/npm/croner@7/dist/croner.umd.min.js'; const cache = CacheService.getScriptCache(); // Check if the library content is already cached const cachedContent = cache.get(key); if (cachedContent) return cachedContent; // Fetch the library content from the specified URL const libraryContent = UrlFetchApp.fetch(url, { muteHttpExceptions: false, }).getContentText(); // Check if the fetched content contains the word "Cron" if (/Cron/.test(libraryContent)) { // Cache the libary for 6 hours cache.put(key, libraryContent, 60 * 60 * 6); return libraryContent; } throw new Error('The cron library is unavailable');};

Add Trigger with Cron Expression

Next, we’ll create a function that loads the Cron library and checks if a scheduled task is set to execute within the next 5 minutes. It uses the script’s timezone to compare the dates.

const scheduledFunction = () => { // Run the trigger at 3:45 for the 1st 10 days of every month const cronExpression = '45 3 1-10 * *'; eval(loadCronLibrary()); const job = Cron(cronExpression); const timeDifference = (job.nextRun() - new Date()) / (1000 * 60); if (Math.abs(timeDifference) <= 5) { Logger.log('Hello, I am running via the time trigger!'); }};const addTrigger = () => { ScriptApp.newTrigger('scheduledFunction').timeBased().everyMinutes(5).create();};

The addTrigger function would create the time trigger that would invoke the scheduledFunction every 5 minutes. The cron schedule is checked every 5 minutes, and if it is scheduled to run, the Hello message would be logged to the console.

View Details

Whether you are looking to write emails in Gmail, create tables with custom data in Google Sheets or design a presentation in Google Slides, Duet AI for Google Workspace can do the work for you in few easy steps.

Duet AI - Google Workspace

How to Enable Duet AI

Duet AI is now available for Google Workspace but you need to take the following steps to start using the AI capabilities of Duet AI in your Gmail and other Google apps.

1. Purchase the Duet AI add-on

Open admin.google.com and sign in to your Google Workspace account as an administrator. Inside the dashboard, navigate to Billing > Get more services > Google Workspace add-ons.

Here, look for the Duet AI for Google Workspace Enterprise card and cick the Start Free Trial link to subscribe to the Duet AI service. You can use the Duet AI add-on without payment for a period of 14 days.

2. Assign Duet AI licenses

Once you’ve successfully activated Duet AI, it’s time to share its benefits with your team. Go to Directory > Users and select one or more users and click Assign Licenses. Select Duet AI for Google Workspace from the list of available subscription and click Assign.

Please note that Duet AI is not compatible with Google Workspace Business Starter edition. Additionally, it’s important to ensure that your Workspace users have English set as their preferred language in their Google account settings to access Duet AI.

You may visit the Workspace help center to learn more about Duet AI.

View Details

video: https://www.youtube.com/watch?v=jRdgh7uZbQI Discord is a popular instant messaging and group-chatting platform, used by millions of…

View Details

Are you looking for a way to receive notifications in real-time when an important spreadsheet in your Google Drive get modified or is…

View Details

Yesterday marked Friendship Day, and to celebrate, I sent a personalized image to each of my friends via WhatsApp . The images were created…

View Details

Emojis can be a fun and effective way to add visual interest to your Google Sheets formulas. There are so many different ways to add emojis…

View Details

Bob Canning writes: I have a Google Spreadsheet with postal addresses in column A. Each week, a real estate agent copies a section of those…

View Details

The Email Spreadsheets add-on for Google Sheets can save office workers a ton of time by automating the reporting of spreadsheet data and…

View Details

This tutorial explains how you can build a BMI calculator app with Google Sheets and Google Forms. When a user submits the form, their BMI…

View Details

The Save Gmail to Google Drive add-on lets you automatically download email messages and file attachments from Gmail to your Google Drive…

View Details

"Tech and AI" - that's the theme of an upcoming event we are organizing in New York. We have created a Google Form to collect registrations…

View Details

This Google Spreadsheet on Udemy courses has about 50 sheets, one for each programming language, and the sheets are sorted in random order so it is difficult to find a specific sheet.

It will take a while to sort the worksheets manually but we can easily automate the process with Google Apps Script and easily navigate through large spreadsheets.

Automate Sheet Sorting with Google Apps Script

The following code snippet will automatically sort the worksheets in a Google Sheet alphanumerically. The script can arrange the sheets in either ascending or descending order based on the sheet names.

To get started, go to Extensions > Apps Script to open the script editor. Then, copy and paste the following code:

const sortGoogleSheets = (ascending = true) => { const options = { sensitivity: 'base', ignorePunctuation: true, numeric: true, }; const compareFn = (sheet1, sheet2) => { return ascending ? sheet1.getName().localeCompare(sheet2.getName(), undefined, options) : sheet2.getName().localeCompare(sheet1.getName(), undefined, options); }; // Get the active spreadsheet. const ss = SpreadsheetApp.getActiveSpreadsheet(); ss.getSheets() .sort(compareFn) .reverse() .forEach((sheet) => { ss.setActiveSheet(sheet); ss.moveActiveSheet(1); }); // Flush the changes to the spreadsheet. SpreadsheetApp.flush();};

The compareFn function compares two sheets and returns a value that indicates whether the first sheet should come before or after the second sheet. The function returns the following values:

-1 if the first sheet should come before the second sheet.
1 if the first sheet should come after the second sheet.

Advanced Sort Options

const options = { sensitivity: 'base', ignorePunctuation: true, numeric: true,};

The options object specifies the options for the locale comparison. Here are some important things to know:

The numeric property specifies whether numbers should be treated as numbers instead of strings. If this property is set to false, “Sheet1” and “Sheet10” will come before “Sheet2”.
The ignorePunctuation property specifies whether spaces, brackets and other punctuation should be ignored during the comparison. If this property is set to false, “Sheet 1” and “Sheet1” will be treated as different sheets.
The sensitivity property specifies if the comparison should be case-sensitive or case-insensitive. Set this property to “accent” to treat base letters and accented characters differently (Sheet a and Sheet à will be treated as different sheets).

Sort Google Sheets by Date

If your sheet names contain dates, like “March 2023” or “01/03/23”, you’ll need to convert the dates to numbers before comparing them.

const compareFn = (sheet1, sheet2) => { return ascending ? new Date(sheet1.getName()).getTime() - new Date(sheet2.getName()).getTime() : new Date(sheet2.getName()).getTime() - new Date(sheet1.getName()).getTime();};

References

View Details

Whether you’re new to using a Mac or an experienced Mac user looking to take your productivity to the next level, you’ll find something new and useful in our collection of the must-have Mac Apps of 2023. Most of these apps are free and cater to general Mac users, not just the geek crowd.

Best Mac Apps and Utilities

The Best  Mac Apps & Utilities

This collection of essential Mac Apps includes mostly lesser-known apps so the popular ones — like Evernote, 1Password, Dropbox, Skype, OneNote, or Google Drive — aren’t listed here. Also, all the apps here are compatible with Big Sur and Catalina, the current versions of macOS.

Wherever possible, I have included the Mac App Store links because the store not only makes it easy for you to install apps on your Mac but, in the case of paid apps, you also have an option for requesting refunds.

Let’s get started.

Raycast - A powerful Spotlight replacement for your Mac that lets you quickly search and launch apps, search the web, and more. You can also create custom workflows with JavaScript.
Notion - Think of Notion as a note-taking app, a wiki, a to-do manager, a calendar, a spreadsheet and a project management tool, all rolled into one.
Outlook - Microsoft Outlook is the best email client for Mac and you no longer need a Microsoft 365 subscription or Office license to use Outlook. Spark is also a good alternative to Apple Mail.
Magnet - A perfect windows management app for Mac that lets you move and resize windows with configurable keyboard shortcuts. You can move windows between multiple displays too. Another alternative is Rectangle.
Setapp - A collection of premium Mac Apps and Utilities in a single package. Includes favorites like MindNode, Ulysses for writers, CleanshotX for screen capture and Capto for screen recording.
Shottr - An innovate screenshot app for Mac that lets you capture and annotate screenshots with ease. You can perform OCR and also capture full web pages. I also use CleanShot X and Xnapper.
ImageOptim - Always run your images through ImageOptim before uploading them on to your website. The app will reduce the size of your image files without affecting the visual quality.
Warp - A modern replacement for the Mac Terminal. It is fast, beautiful, and includes AI search to convert natural language into executable shell commands. Also see - Essential Tools for Programmers
Site Sucker - Download entire websites includes images, PDF files and mirror them on your local disk for offline browsing. Like wget but with a visual interface.
App Cleaner - The best uninstaller for your Mac that will automatically remove all the extra files that are left on the disk when you delete an app.
Maccy - A clipboard manager that stores all that you copy to the clipboard and lets you paste the copied snippets into other apps with a simple shortcut. [CopyClip] is a good alternative.
Clean Me - Recover space on your Mac by deleting all the system logs, cache and other temp files that your Mac can easily do away with.
Dozer - An excellent alternative to the popular Bartender app. You can quickly re-order or even hide the app icons appearing in the Mac menu bar.
NetNewsWire - A clean and fast RSS Reader for your MacOS. Checkout Reeder if you are looking for a more advanced RSS reader. We have an RSS Feed too!
RSS Bot - Access your RSS from your Mac’s menu bar and get notifications when new items are available. You can also apply filters to only show articles that match certain keywords.

Numi.app - The Smart Mac Calculator

Flotato - It turns any web page into a native Mac app that you can quickly open outside the web browser. Also see, Fluid.
Latest - It scans the Applications folder of your Mac and checks if all your installed apps are up to date. You can also update your outdated apps. MacUpdater is an even more powerful but paid alternative.
Onyx - Perform system maintenance tasks to improve the performance of your Mac, verify disks and more.
TinkerTool - It provides access to several configuration settings that are otherwise hidden on the Mac. For instance, you can specify the default folder where Screenshots should be saved on the Mac.
KeepingYouAwake - It keeps your Mac stay awake and also prevents your screen from going to sleep. If you need more features, use Amphetamine.
Shifty - Easily toggle between dark and light mode on your Mac. You can also decide which of your Apps or websites should stay light, while your system runs in Dark Mode. Also see, NightOwl.
IINA - A modern alternative to the VLC Media Player that includes support for gestures and the touch bar in newer Macs.
HyperSwitch - An improved window switching app for Mac that upgrades your default Command + Tab experience when cycling between open app windows.
TextBar - You can specify system commands and the app will add the text output of those commands to the menu bar. For instance, ipconfig getifaddr en0 will print your current IP address. You can also have these as desktop widgets with Übersicht.
Tyke - A minimalistic notepad app that sits in the menu bar and lets you save quick notes.

Control the Night mode on MacOS

Karabiner - Remap existing keys on the keyboard to perform a different command. For instance, the CAPS-lock key can be configured to work as an Escape key. Useful when using any non-Apple keyboard with Mac.
Dropzone - It makes it easy to copy or move files to your favorite folders, open applications and you can also upload files to the Internet right from your menu bar.
Clocker - Show multiple clocks in your menu bar from different timezones.
Duet Display - Use your iPad, iPhone or even an Android phone as an extra display for your Mac and PC.
Transmit - The perfect FTP client for Mac OS X that just works. You can create droplets to instantly upload files to your favorite destinations from anywhere.
AirDroid - It connects your Android phone to the Mac. You can access messages, manage photos, transfer files and more, wirelessly.
Unarchiver - It’s like WinZip compression utility for Mac that can handle all the popular archive formats including RAR, TAR, GZIP, ISO, and more.
Handbrake - Convert video files from one format to another. FFmpeg is powerful too but works only from the command line. For audio files, the recommended converter is fre:ac.
Disk Inventory - If your MacBook is running low on space, use the Disk Inventory app to quickly discover large files and folders that are clogging the storage.
Helium - An Always on Top like app but for your Mac. The browser window will float on top of other windows and you can also change the translucency level.

Better Volume Control for Mac

XMenu - It provides explorer-style access to your favorite folders and Mac apps from the menu bar. You can launch apps, browse files and folders right from the menu bar.
Flux - It automatically dims the brightness of your screen based on the time of the day - warm at night, bright during the day - so your eyes feel less strain. Also see the 20 20 20 rule.
Text Expander - The app accelerates your touch typing by replacing pre-defined abbreviations with corresponding phrases. For instance, say ;sig to add your rich signature in the Gmail window.
CheatSheet - Use this app to memorize keyboard shortcuts for any Mac app. Just hold the Command Key a bit longer to get a list of all shortcuts available in that app.
Soundflower - If you are to record the Mac audio, like the sound coming out of the speakers, you would need SoundFlower to route that sound to the recording app instead of the speakers.
JumpShare - Quickly upload files, record screencasts, capture screenshots and share them instantly, all from the convenience of your menu bar.
GIF Brewery - It can convert video files and screencasts into animated GIFs and offers tons of options to fine-tune your GIF images. Also see, Giphy Capture.
Hocus Focus - It helps keep your Mac desktop clean by automatically hiding windows that are inactive or haven’t been used for a while. You can even choose to hide windows as soon as they lose focus.
Bandwidth+ - Monitor your Internet bandwidth usage in realtime. Especially handy when you are connected to a metered Wi-Fi hotspot.
Background Music - An audio utility that provides per-application volume control for your Mac. It automatically pauses your music player when a second audio source is playing and unpauses the player when the second source has stopped.

Simple File Transfer for Mac

Download Shuttle - A fast download manager for Mac that will split the files into multiple chunks and downloads them in parallel. Can pause and resume downloads too.
WeTransfer- Send big files to anyone by simply drag and drop. You get a download link that automatically becomes inactive after 7 days.
LICEcap - A light-weight screencast app for capturing any area of your Mac desktop as a small GIF file. Also see, Kap.
Hazel - A folder monitoring app that lets you specify rules per watched folder and any files added to these folders are automatically organized. Supports AppleScript and Automator actions too.
Zoho OneAuth - The only 2-factor authentication (2FA) app you need to secure your Gmail, Facebook and all other online accounts. OneAuth is available on iOS, Android, Mac and Windows devices and you can even import accounts from Google Authenticator. Another good alternative is Authy.
Self Control - To help you stop procrastinating, this Mac app that can temporarily block access to time-wasting websites, emails and everything else that you find distracting.
Better Touch Tool - The app lets you modify the gestures of your Magic Mouse and the Magic Trackpad. You can configure Touch Bar settings and actions too.
OBS - If you ever plan to set up a live stream on Twitch or YouTube, OBS is the only streaming software you’d need.
Zoom - My favorite app for video conferencing on Mac. You can do screen sharing, the meetings are automatically recorded and you can remotely control the attendee’s computer for tech support.
To Do - A perfect todo and task management app for your Mac from Microsoft. Also see, Trello.
Hand Mirror - It lives in the menu bar of your Mac and quickly gives you a view from your webcam. Handy to know how you look before you join that Skype or Zoom video call.
Camo - Use your iPhone or Android phone as a webcam for your Mac. The app works with Zoom, Skype, Meet and other video conferencing apps. Also see Irium.
Kap - Record quick screencasts as GIFs and MP4 videos and upload them to GIPHY, Dropbox, Vercel (Now) or Amazon S3 directly from the app.
Diagrams.net - The best tool for creating diagrams and flowcharts. It’s like Microsoft Visio but completely free.
HiddenMe - If your Mac desktop is cluttered with folders and files, you can hide all the icons with a single click or with a keyboard shortcut.
Meeter - Keep track of your upcoming online meetings from Zoom, Google Meet, Microsoft Teams and other virtual conference services and join the meeting directly from your Mac’s menubar.
KeyPad - Use the connected physical keyboard of your Mac to type on your iPhone, iPad and Android phone.

Also see: The 101 Most Useful Websites

View Details

Whether you are looking to learn a programming language, enhance your Microsoft Excel skills, or acquire knowledge in Machine Learning, Udemy probably has a video course for you. Udemy courses are usually affordable, there are no subscription fee and you can learn at your own pace.

Free Udemy Courses on Programming

While most video tutorials on Udemy require payment, the website also offers some of their highly-rated courses for free. I’ve prepared a Google Sheet that lists all the free programming courses currently available on Udemy. The spreadsheet is updated automatically every few hours. You can also access the web version for easy browsing.

✨ You may use the search function of the browser (Ctrl + F) to find courses for a specific programming language or topic. The courses are sorted by popularity.

There’s no secret sauce. Udemy has an developer API that provides access to all the course data available on the website, including user ratings, number of students who have taken the course, duration, preview video lectures, and more.

Use the Udemy API with Google Sheets

The Udemy API is free to use but requires authentication. You can generate the credentials for your Udemy account and then use the /courses endpoint to fetch the list of free courses.

const parseCourseData\_ = (courses) => courses .filter( ({ is\_paid, primary\_category }) => is\_paid === false && ['Development', 'IT & Software'].includes(primary\_category.title) // We are primarily interested in programming courses on Udemy ) .map((e) => [ `=IMAGE("${e.image\_240x135}")`, `=HYPERLINK("https://www.udemy.com${e.url}";"${e.title}")`, e.visible\_instructors.map(({ display\_name }) => display\_name).join(', '), e.num\_subscribers, Math.round(e.avg\_rating * 100) / 100, e.num\_reviews, e.content\_info\_short, e.num\_lectures, new Date(e.last\_update\_date), ]);const listUdemyCoursesGoneFree = () => { // Put your Udemy credentials here const CLIENT\_ID = ''; const CLIENT\_SECRET = ''; const params = { page: 1, page\_size: 100, is\_paid: false, 'fields[course]': '@all', }; const query = Object.entries(params) .map(([key, value]) => `${key}=${encodeURIComponent(value)}`) .join('&'); const apiUrl = `https://www.udemy.com/api-2.0/courses/?${query}`; const bearer = Utilities.base64Encode(`${CLIENT\_ID}:${CLIENT\_SECRET}`); const options = { muteHttpExceptions: true, headers: { Authorization: `Basic ${bearer}`, }, }; const courses = []; do { const response = UrlFetchApp.fetch(apiUrl, options); const { results = [], next } = JSON.parse(response); courses.push(...parseCourseData\_(results)); url = next; } while (url && courses.length < 500); const ss = SpreadsheetApp.getActiveSpreadsheet(); const [sheet] = ss.getSheets(); sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn()).clearContent(); sheet.getRange(2, 1, courses.length, courses[0].length).setValues(courses);};

We use the UrlFetch service of Google Scripts to fetch the data from the Udemy API and the data is then parsed and inserted into the Google Sheet. The course thumbnail image is rendered using the IMAGE formula and the course title is linked to the Udemy website using the HYPERLINK formula.

View Details

Mail Exchange (MX) records, in simple English, tell the internet where to deliver your emails. These records enable email servers to know which mail servers are responsible for accepting incoming messages for a given domain.

When you are using Gmail with your Google Workspace domain, Google provides you with a set of MX records in the format ASPMX.L.GOOGLE.COM and you need to add five such records to your domain. These MX records point incoming emails to Google’s mail servers, which then deliver the emails to your Gmail inbox.

Priority	Host / Mail Server
1	ASPMX.L.GOOGLE.COM
5	ALT1.ASPMX.L.GOOGLE.COM
5	ALT2.ASPMX.L.GOOGLE.COM
10	ALT3.ASPMX.L.GOOGLE.COM
10	ALT4.ASPMX.L.GOOGLE.COM

These MX records specify the priority and destination of email messages. The priority numbers indicate the order in which the email servers should be tried.

When a sender sends an email to your domain, their email server will look up your domain’s MX records to determine where to deliver the email. The server will try the first mail server listed (priority 1), and if that server is unavailable or unreachable, it will try the next one listed (priority 5), until the email message is successfully delivered.

New MX Records for Google Workspace

Google has simplified the MX records for new Google Workspace accounts that are created after April 2023. You now only need to add one MX record to your domain. The priority value is always 1 and the destination is SMTP.GOOGLE.COM.

Priority	Host / Mail Server
1	SMTP.GOOGLE.COM

You may find the updated MX records here. Google Workspace evangelist Michael Brenzel suggest that the new MX records should be used for only new Google Workspace accounts, and that there’s no need to change the DNS records for existing Workspace domains.

MX Records References:

View Details

Conditional content allows you to customize your Google Docs template and generate different versions of the same document based on the user’s answers. In this tutorial, I’ll show you how to use conditional content in Google Docs using Document Studio, a Google add-on that automates document creation.

If you are new here, please follow this step-by-step guide on how to generate documents from data in Google Sheets and Google Forms responses.

Conditionally Display Content

Let’s say you are a recruiter who wants to use a Google Docs template to send out job offer letters to candidates. You want to include specific information in the offer letter based on the candidate’s job title and location.

The conditional statements that we would like to include in the document template are:

Your office is located in San Francisco. Your offer letter should include a paragraph offering relocation benefits only to candidates who are located outside SF.
If the candidate is offered an Associate position, they are eligible for basic benefits.
If the candidate is hired for a senior position, like Manager or Director, they are entitled to additional benefits like 401(k) retirement plan.

Define the conditional sections

Create a new Google Docs document and create a job offer letter template. Include sections for the candidate’s name, job title, location, salary, and benefits package.

Use the <<if>> and <<endif>> expressions to define the conditional sections in your template. For example, you might use the following expressions to show or hide the relocation package section based on the candidate’s location:

<<If: ({{Location}} != 'San Francisco')>>We are pleased to offer you a relocation package to assist with your move from {{Location}} to our main office.<<EndIf>>

Similarly, you can wrap the benefits paragraph with the <<if>> and <<endif>> expressions to show or hide the benefits package section based on the candidate’s job title:

<<If: OR (({{Job Title}} == 'Manager'), ({{Job Title}} == 'Director'))>>As {{Job Title}}, you will be eligible for our comprehensive benefits package, which includes health, dental, and vision insurance, a 401(k) retirement plan, and more.<<EndIf>>

You may also use the ~ contains operator in place of == equals operator for partial matches. For instance, {{Job Title}} ~ 'Manager' will match Sales Manager, Senior Manager, Manager and so on.

Here’s how the final job offer letter template looks like.

https://www.youtube.com/watch?v=UbMlmckpZDg

In addition to document templates, you can also add conditional text in email templates with the help of scriptlets.

Things to know:

You may not nest IF statements inside each other.
You can use the OR, AND or NOR operator to combine multiple conditions.
If you have a table in your document that should be displayed conditionally, you should put the wrapping <<if>> and <<endif>> tags outside the table.
It is currently not possible to hide or show specific rows or columns of a table based on the user’s answers.

View Details

This tutorial explains how to make phone numbers clickable within Google Sheets, Slides and Google Docs. When someone clicks on the phone number link in your spreadsheet or this document, it will open the dialer on their mobile phone and initiate dialing of the specified phone number.

How to Insert Clickable Phone Numbers in Web Pages

Let’s start with the basics.

If you click an email link on a webpage, it opens your default mail program. Similarly, you can make phone numbers on your website “callable” meaning when someone clicks on the phone number link, it will automatically launch the dialer on their mobile phone with the phone number filled in.

You can use the tel protocol to convert a plain text phone number on a web page into a clickable telephone link.

For instance, if you click this link on a mobile phone, it will open the phone dialer prefilled with the specified number. There’s no need to copy-paste numbers.

How to Type Phone Numbers in Google Sheets

It is a bit tricky to type phone numbers inside Google Spreadsheets. Here’s why:

Phone numbers typically consist of digits preceded by the plus (+) symbol. However, a common issue is that when you include the plus sign in a cell, the spreadsheet assumes you are entering a math formula and attempts to calculate the value.

If you encounter this problem, there are two easy ways to resolve it.

Workaround A You can surround the phone number with double quotes (”) and precede it with an equal sign (=).

Workaround B You can add a single quote (’) before the phone number. This tells Google Sheets to treat the cell’s contents as text, preserving the formatting of the phone number.

How to Make Phone Numbers Clickable in Google Sheets

Coming to the main problem, how do you make phone numbers inside a Google Sheet clickable?

The obvious choice would be to use the HYPERLINK formula with the tel protocol but it is not supported inside Google Sheets. So a formula like =HYPERLINK("tel:12345", "Call Me") would not work but there’s a simple workaround to this issue.

Append the phone number with the call.ctrlq.org domain name and it will automatically convert the phone number into a clickable link. For example, if you want to create a clickable phone link for the number +1 650-253-0000, you can use the following formula.

You can create a regular hyperlink in the cell pointing to a website which in turn redirects to the actual telephone link. To see this in action, add https://call.ctrlq.org/ before any phone number in the Google Sheet and it will turn into a callable phone link.

=HYPERLINK("https://call.ctrlq.org/+16502530000"; "Call Google Support")

In the above example, the phone numbers are listed in column B while the names are in column A. You can add the following formula in column C to have clickable phone links.

=HYPERLINK("https://call.ctrlq.org/"&B2; A2)

You may open this Phone Number Google Sheet on your Android or iPhone and click on any of the phone links to see it in action. You can even publish the sheet as a web page and the numbers will be clickable on the web too.

Clickable Phone Numbers in Google Docs and Slides

You can also create clickable phone numbers in Google Docs and Google Slides. The process is similar to Google Sheets but we’ll use the Insert Link option instead of the HYPERLINK formula.

Write the phone number inside the document and select it. Then click on the Insert menu and select Link from the dropdown. Or you can use the keyboard shortcut Ctrl+K to open the link dialog.

Enter the phone number preceded by the call.ctrlq.org domain name and click on the OK button. The phone number will be converted into a clickable link.

Also see: Add Images in Google Spreadsheets

The Technical Details

The call.ctrlq.org service is a simple Node.js app running on Google Cloud Run that merely redirects to the tel protocol. Here’s the entire app code should you want to run it on your own server.

const express = require('express');const app = express();app.get('/:number', (req, res) => { const { number } = req.params; const phone = number.replace(/[^0-9]/g, ''); res.redirect(`tel:${phone}`);});app.listen(process.env.PORT, () => { console.log(`App is running`);});

View Details

NPM, short for Node Package Manager, is a widely-used tool for managing JavaScript packages in a project. It allows developers to install and update packages, as well as manage dependencies and scripts. NPM comes bundled with Node.js, so if you have Node installed on your machine, you automatically have access to NPM as well.

npm Commands You Should Know

This is not a tutorial for learning npm, the official docs are a good place to get started, but a collection of tips and tricks that will help you do more with the npm utility. Whether you’re a seasoned developer or just starting out, these tips can help you be more efficient and productive in your work with npm.

Instantly run packages without installing

The NPM registry is a treasure trove for finding packages that do useful stuff and they aren’t just for programmers.

For instance, the speed-test package shows the speed of your internet connection. The emoj package helps you search for emojis from the terminal. And the wifi-passwords package can help you find the password of your current WiFi network.

You can run these utility packages directly from the command line using the npx command.

npx speed-testnpx emoj unicornnpx public-ip-clinpx wifi-password-cli

Get package details

Use the npm view command to get details of any npm package, including the repository URL, the dependencies and the date when the package was last updated.

npm view eslint

Install npm packages faster

You’ve probably used npm install to install packages, and dependencies, in the local node\_modules folder of a project. Replace this command with npm-ci and you’ll be able to install packages significantly faster.

npm ci

If a node\_modules folder is already present, it will be automatically removed before npm ci begins to install packages.

Recover space

If you have been working with npm packages for some time, the various node\_modules folders on the disks could be consuming several gigabytes of space. The very useful npkill finds all node\_modules folders on your system and lets you delete them interactively.

npx npkill

Quickly download a Git repository

Most developers use the git clone command to download a Git repository. However, this also downloads the entire git history making the process slower. The degit package can download the latest commit to the master branch locally and you need not specify the full Github URL.

npx degit username/reponpx degit labnol/apps-script-starter

List installed packages

Generate a list of all npm packages that are installed on the system with global scope. Remove the -g flag to list only packages installed in the current project directory.

npm ls --depth=0npm ls -g

Find unused dependencies

The depcheck command will list all the npm packages that are not used in the project based on the dependencies in package.json.

npx depcheck

Use the command npm uninstall <package-name> to uninstall any unused package.

Find unused source files

The unimported package will find all the unused files and dependencies in your JavaScript / TypeScript projects.

npx unimported

Find outdated dependencies

Get a list of all outdated packages in your current project. This command checks every single module listed in the package.json file and compares it with the latest version available in the NPM registry.

Add the -g flag to get all outdated packages that are installed globally on the system.

npm outdatednpm outdated -g

Update the package versions

The npm outdated command will list all packages in your current project that are outdated and a newer version is available. Add the -g flag to list outdated packages that are installed in the global scope.

The ncu command will update the package.json file with the latest version of the packages listed in the dependencies and devDependencies sections.

Or use the npm-check -u command to update packages to their latest version in interactive mode.

npm outdatednpm outdated -gnpm-checknpm-check -uncu -u

Remove extra packages

Use the prune command to remove all packages that are installed locally but not listed in the package.json file. If the —dry-run flag is used then no changes will be made.

npm prune

Alternatively, you can remove the node\_modules folder and run npm ci again.

Find vulnerable packages

Run the audit command to check for vulnerabilities in the packages listed in the dependencies and devDependencies sections. Add the fix flag to automatically apply the fixes, if any.

npm auditnpm audit fix

Useful NPM Package Websites

bundlephobia.com - Upload your package.json file and get an idea of how much it would cost (size-wise) to install the dependencies.
diff.intrinsic.com - Compare any two versions of a npm package and know which files have changed in the update.
npmtrends.com - Compare the relative popularity of packages across the npm registry based on the number of downloads.

View Details

A teacher may want to create folders in Google Drive for each of their students and share those folders with the students. This can be a tedious task if you have a large number of students but there’s a way to automate the process - you may either use an add-on or write an Apps Script to generate the folder structure.

Prepare the Students’ Data in Google Sheets

We’ve prepared a Google Sheet with the names of students, their corresponding classes and email addresses. The first row of the sheet displays the column titles, while the student data starts from row two onwards.

The folder structure in Google Drive would be as follows - the parent folder would have sub-folders for each class and each class folder would have sub-folders for each student. The student folders would be shared with the student’s email addresses where students can upload their work.

Bulk Create Folders in Google Drive

Install the Document Studio add-on for Google Sheets. Open the spreadsheet with the student data and click on Extensions > Document Studio > Open to launch the add-on.

Create a new workflow inside Document studio, give it a descriptive name like Create Student Folders and click on the Continue button to add a task.

For the Subfolder Name field, select the column in the spreadsheet that contains the student names and their class names. Enclose the column titles within double curly braces and they are replaced with the actual values from the spreadsheet.

You can put the {{Email Address}} column in the Editors field to share the student folders with their email addresses automatically when the folder is created in Google Drive.

Now that workflow is ready, choose the Save and Run option to create the folders in Google Drive. The folders would be created and a link to the folder would be placed in the spreadsheet itself. If a folder already exists, the link to the existing folder is placed in the spreadsheet.

Create Multiple Folders in Google Drive in Apps Script

If you prefer to write code, you can use the following Apps Script to create folders in Google Drive for students and share those folders with their email addresses based on data from a Google Sheet.

Go to Google Sheets, and choose Extensions > Apps Script to open the script editor. Create a new script and add the following code:

A. Create a folder in Google Drive only if it doesn’t already exist.

function createFolderIfNotExists(folderName, parentFolder) { const folders = parentFolder.getFoldersByName(folderName); return folders.hasNext() ? folders.next() : parentFolder.createFolder(folderName);}

B. Get the student data from the spreadsheet and return an array of objects with the student data.

function getStudentData(sheet) { const [header, ...rows] = sheet.getDataRange().getDisplayValues(); return rows.map((row, rowIndex) => { const student = {}; row.forEach((cell, i) => { student[header[i]] = cell; }); return { ...student, rowIndex: rowIndex + 2 }; });}

C. Create the folders in Google Drive and share them with the students.

function createStudentFoldersInGoogleDrive() { const sheet = SpreadsheetApp.getActiveSheet(); const studentData = getStudentData(sheet); const rootFolder = DriveApp.getRootFolder(); const parentFolder = createFolderIfNotExists('Classroom', rootFolder); for (let i = 0; i < studentData.length; i++) { const student = studentData[i]; const classFolder = createFolderIfNotExists(student['Class'], parentFolder); const studentFolder = createFolderIfNotExists(student['Student Name'], classFolder); studentFolder.addEditor(student['Email Address']); const folderUrl = studentFolder.getUrl(); sheet.getRange(student['rowIndex'], 5).setValue(folderUrl); } SpreadsheetApp.flush();}

You may want to change the column titles and indices in the code to match the ones in your data spreadsheet. Also, you may want to use the Advanced Drive API service to create folders in Shared Drive.

Also see: Create Folders in Google Drive for Google Form responses

View Details

The Email Address Extractor add-on for Gmail helps you extract email addresses of your customers from your Gmail messages and writes them to a Google Sheet. It internally uses the Gmail API to fetch the messages and the Google Sheets API to write the email addresses to a Google Sheet.

There are two ways to pull email addresses from Gmail messages. The simpler, and more popular, method is that you pull a list of messages from which you wish to extract the email and loop over them to extract the email addresses.

// Pull details of emails from PayPal, Stripe or Shopifyfunction getEmailAddress() { const threads = GmailApp.search('from:paypal OR from:stripe OR from:shopify newer\_than:2d', 0, 10); threads.forEach((thread) => { const messages = thread.getMessages(); messages.forEach((message) => { Logger.log('Subject: ' + message.getSubject()); Logger.log('To: ' + message.getTo()); Logger.log('From: ' + message.getFrom()); }); });}

Gmail Batch Request

A more efficient way to pull email addresses from multiple email messages is to make a single batch request to the Gmail API with the help of Apps Script’s UrlFetch service.

1. Get a list of messages in Gmail

We use the Advanced Gmail service of Apps Script to get a list of unread messages from a user’s inbox in Gmail. You may use any of Gmail’s advanced search operators to filter the messages.

The searchGmailMessages() function uses the Gmail API to search for unread messages in the inbox and returns an array of message IDs.

const searchGmailMessages = () => { const { messages = [] } = Gmail.Users.Messages.list('me', { q: 'in:inbox is:unread', maxResults: 25, fields: 'messages(id)', }); return messages.map(({ id } = {}) => id);};

2. Prepare the batch request

Now that we have the list of Gmail message Ids, we need to prepare the batch request to the Gmail API.

The function getUrlParts() generates a URL query string with parameters for requesting specific fields and metadata for Gmail messages. We use the fields parameter to request minimal data for each message and the metadataHeaders parameter to request specific metadata headers for each message.

const getUrlParts = () => { const metadata = ['Subject', 'From', 'To'].map((key) => `metadataHeaders=${key}`).join('&'); const data = { fields: 'payload/headers', format: `metadata`, }; const fields = Object.entries(data) .map(([key, value]) => `${encodeURIComponent(key)}=${encodeURIComponent(value)}`) .join('&'); return `${fields}&${metadata}`;};

The createMessageRequest() function constructs a request object for fetching a specific message from the Gmail API with an OAuth token.

const GMAIL\_API\_ENDPOINT = `https://www.googleapis.com/gmail/v1/users/me/messages`;const createMessageRequest = (messageId) => { const urlparts = getUrlParts(); return { url: `${GMAIL\_API\_ENDPOINT}/${messageId}?${urlparts}`, headers: { Authorization: `Bearer ${ScriptApp.getOAuthToken()}` }, muteHttpExceptions: true, };};

3. Make the batch request

We use the fetchAll method of the UrlFetch service to make multiple requests to the Gmail API in parallel. This method takes an array of request objects, we created them in the previous step, and fetches the email message headers for each message ID using the Gmail API.

const makeBatchRequest = (messageIds) => { const messageRequests = messageIds.map(createMessageRequest); const responses = UrlFetchApp.fetchAll(messageRequests); responses.forEach((response) => { const messageData = JSON.parse(response); const { error, payload: { headers = [] } = {} } = messageData; if (error) { console.log('Error', error); } else { headers.forEach(({ name, value }) => { Logger.log(name + ': ' + value); }); } });};

Also see: Send Email with Gmail API and Node.js

View Details

It is easy to embed a YouTube video but you’ll be surprised to know how much extra weight a single YouTube video embed can add to your web pages. The browser has to download ~900 kB of data (see screenshot) for rendering the YouTube video player alone. And these files are downloaded even before the visitor has clicked the play button.

The embedded YouTube video not only increases the byte size of your web pages but the browser has to make multiple HTTP requests to render the video player. This increases the overall loading time of your page thus affecting the page speed and the core vitals score of your website.

The other drawback of the default YouTube embed code is that it renders a video player of fixed dimensions and isn’t responsive. If people view your website on a mobile phone, or a tablet, the video player may not resize properly on the small screen.

Embed YouTube Videos without Increasing Page Size

Google+, which is now retired, made use of a very clever technique for embedding YouTube videos. When the page was initially loaded, Google+ would only embed the thumbnail image of the YouTube video and the actual video player was loaded only when the user clicked the red player icon.

The thumbnail image of YouTube videos is about 15 kB in size so we can easily reduce the initial size of web pages by almost 900 kB.

Lite YouTube Embed Demo

Open this demo page to view the Google+ technique in action. There are two similar videos on that page. The first video is embedded using the default IFRAME code that is supplied by YouTube while the second video uses the lite mode that loads the YouTube video on demand.

When a user clicks the play button of the second video, the thumbnail image is replaced with the standard YouTube video player with autoplay set to 1 so the video would play almost instantly. The big advantage is that the extra YouTube JavaScript gets loaded only when someone decides to watch the embedded video and not otherwise.

Light and Responsive YouTube Embeds

The standard embed code for YouTube uses the IFRAME tag where the width and height of the video player are fixed thus making the player non-responsive.

The new on-demand embed code for YouTube is responsive that adjusts the player dimensions automatically based on the screen size of the visitor.

Embed YouTube Videos Responsively - Tutorial

Step 1: Copy-paste the following HTML snippet anywhere in your web page where you would like the YouTube video to appear. Remember to replace VIDEO\_ID with the actual ID of your YouTube video.

<div class="youtube-player" data-id="VIDEO\_ID"></div>

We will not assign height and width since the video player will automatically occupy the width of the parent while the height is auto-calculated. You can also paste multiple DIV blocks with different video IDs if you need to embed multiple YouTube videos on the same page.

Step 2: Copy-paste the JavaScript anywhere in your web template. The script finds all embedded videos on a web page and then replaces the DIV elements with the video thumbnails and a play button (see demo).

<script> /* * Light YouTube Embeds by @labnol * Credit: https://www.labnol.org/ */ function labnolIframe(div) { var iframe = document.createElement('iframe'); iframe.setAttribute('src', 'https://www.youtube.com/embed/' + div.dataset.id + '?autoplay=1'); iframe.setAttribute('frameborder', '0'); iframe.setAttribute('allowfullscreen', '1'); iframe.setAttribute('allow', 'accelerometer; autoplay; encrypted-media; gyroscope; picture-in-picture'); div.parentNode.replaceChild(iframe, div); } function initYouTubeVideos() { var playerElements = document.querySelectorAll('.youtube-player'); for (var n = 0; n < playerElements.length; n++) { var videoId = playerElements[n].dataset.id; var div = document.createElement('div'); div.setAttribute('data-id', videoId); var thumbNode = document.createElement('img'); thumbNode.src = '//i.ytimg.com/vi/ID/hqdefault.jpg'.replace('ID', videoId); div.appendChild(thumbNode); var playButton = document.createElement('div'); playButton.setAttribute('class', 'play'); div.appendChild(playButton); div.onclick = function () { labnolIframe(this); }; playerElements[n].appendChild(div); } } document.addEventListener('DOMContentLoaded', initYouTubeVideos);</script>

Step 3: Copy-paste the CSS before the closing head tag of your web template.

<style> .youtube-player { position: relative; padding-bottom: 56.25%; height: 0; overflow: hidden; max-width: 100%; background: #000; margin: 5px; } .youtube-player iframe { position: absolute; top: 0; left: 0; width: 100%; height: 100%; z-index: 100; background: transparent; } .youtube-player img { object-fit: cover; display: block; left: 0; bottom: 0; margin: auto; max-width: 100%; width: 100%; position: absolute; right: 0; top: 0; border: none; height: auto; cursor: pointer; -webkit-transition: 0.4s all; -moz-transition: 0.4s all; transition: 0.4s all; } .youtube-player img:hover { -webkit-filter: brightness(75%); } .youtube-player .play { height: 48px; width: 68px; left: 50%; top: 50%; margin-left: -34px; margin-top: -24px; position: absolute; background: url('https://i.ibb.co/j3jcJKv/yt.png') no-repeat; cursor: pointer; }</style>

You can view the light YouTube embed technique in action on this Codepen page.

Please do note that Chrome and Safari browsers on iPhone and Android only allow playback of HTML5 video when initiated by user interaction. They block embedded media from automatic playback to prevent unsolicited downloads over cellular networks.

Create Google Cloud Storage Bucket

To get started, go to console.cloud.google.com/projectcreate and create a new Google Cloud Project. Once the project has been added, go to console.cloud.google.com/storage/create-bucket and create a new bucket. Give your bucket a unique name and select the region where you want to store your data. If the files you are uploading are private and you don’t want to make them public anytime later, you may enable the “Enforce public access prevention on this bucket” option.

Create Storage Service Account

Next, go to IAM & Admin > Service Accounts console.cloud.google.com/iam-admin/serviceaccounts/create and create a new service account. Give your service account a name and select the “Storage Admin” role. You may also want to add the “Service Account Token Creator” role to the service account, as this is required to create signed URLs for the files you upload to Google Cloud Storage.

From the list of service accounts, click the one you created in the previous step. Go to the “Keys” tab and click on “Add Key” > “Create New Key,” and select the JSON option. This will download a JSON file containing the service account credentials. You will need these credentials to upload files to Google Cloud Storage.

Write Google Apps Script Code

Go to script.new to create a new Google Apps Script project. Click on Libraries and add the OAuth2 library 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF to your project. Next, add a new file service.js, and use the values of private\_key and client\_email from your service account JSON file to create a new OAuth2 service.

// service.js// Replace these with your own valuesconst service\_account = { private\_key: '-----BEGIN PRIVATE KEY-----\n51CjpLsH8A\n-----END PRIVATE KEY-----\n', client\_email: 'upload@storage-labnol.iam.gserviceaccount.com',};const getStorageService = () => OAuth2.createService('FirestoreStorage') .setPrivateKey(service\_account.private\_key) .setIssuer(service\_account.client\_email) .setPropertyStore(PropertiesService.getUserProperties()) .setCache(CacheService.getUserCache()) .setTokenUrl('https://oauth2.googleapis.com/token') .setScope('https://www.googleapis.com/auth/devstorage.read\_write');

Upload Files to Google Cloud Storage

Next, we’ll write the upload function in Apps Script. The function takes the file ID of the file you want to upload to Google Cloud Storage, the name of the bucket, and the path where you want to store the file.

The function uses the getStorageService function from the previous step to create a new OAuth2 service. The getAccessToken method of the OAuth2 service is used to get the access token required to upload files to Google Cloud Storage.

// Replace these with your own valuesconst DRIVE\_FILE\_ID = 'abc123';const STORAGE\_BUCKET = 'labnol.appspot.com';const FILE\_PATH = 'parentFolder/subFolder';const uploadFileToCloudStorage = () => { const file = DriveApp.getFileById(DRIVE\_FILE\_ID); const blob = file.getBlob(); const bytes = blob.getBytes(); const API = `https://www.googleapis.com/upload/storage/v1/b`; const location = encodeURIComponent(`${FILE\_PATH}/${file.getName()}`); const url = `${API}/${STORAGE\_BUCKET}/o?uploadType=media&name=${location}`; const service = getStorageService(); const accessToken = service.getAccessToken(); const response = UrlFetchApp.fetch(url, { method: 'POST', contentLength: bytes.length, contentType: blob.getContentType(), payload: bytes, headers: { Authorization: `Bearer ${accessToken}`, }, }); const result = JSON.parse(response.getContentText()); Logger.log(JSON.stringify(result, null, 2));};

Also see: File Upload Forms for Google Drive

View Details

An order form, created in Google Forms, requires customers to provide their full name, the item quantity and whether home delivery is…

View Details

You have been using Google Docs to write documents and essays but did you know that the same editor can also be used to write and run…

View Details

The Document Studio add-on helps you automatically send text messages when a new Google Form is submitted or when new rows are added to…

View Details

A school provides email accounts for students that are enrolled in high school. The school has published a Google Form and any student can…

View Details

Whether it is a wedding party or a business conference, those tent-shaped place cards are ideal for helping your guests find their seats at…

View Details

We have created a simple quiz in Google Forms that has 3 questions and each correct answer gives you 10 points. The maximum score that can…

View Details

When you submit a Google Form, it stores a copy of the form response as a new row in the Google Sheet. The only problem here is that Google…

View Details

Set reminders with @RemindMe_OfThis An open-source Twitter bot that lets you easily set reminders for public tweets. Mention @RemindMe…

View Details

This step-by-step tutorial describes how you can connect to the Gmail SMTP server for sending emails from a Node.js web application that…

View Details

In a previous tutorial, you learned how to send WhatsApp messages from Google Sheets using the official WhatsApp API. The first 1,00…

View Details

This tutorial describes how you can use the new WhatsApp API with Google Apps Script to send WhatsApp messages from Google Sheets. The same…

View Details

This Apps Script sample shows how you can programmatically schedule video meetings inside Google Meet with one or more participants using…

View Details

Looking for a place to host images so you can embed them on to your website? The most popular image hosting services are imgur.com and…

View Details

Let's say you have built an add-on for Google Sheets that adds a new menu item to the sheets UI. You would now like to add an option in the…

View Details

You can put the link of any MP3 audio file in Google Sheets but when you click the file link, the audio would not play. You can however add…

View Details

This tutorial describes how you can use Google Sheets to build your own podcast manager. You can specify a list of your favorite podcast…

View Details

Let's create a simple website scraper that download the content of a web page and extract the content of the page. For this example, we will…

View Details

Let's write a simple web application that will allow users to upload files to Google Cloud Storage without authentication. The client site…

View Details

The BHIM UPI payment system has transformed the way we pay for goods and services in India. You scan a QR Code with your mobile phone…

View Details

Let's build a simple web application that uses Google OAuth 2.0 to access Google APIs. The user can sign-in with their Google account and…

View Details

An external accounting system generates paper receipts for its customers which are then scanned as PDF files and uploaded to a folder in…

View Details

Conditional formatting in Google Sheets makes it easy for you to highlight specific cells that meet a specific criteria. For instance, you…

View Details

This tutorial explores the different options for inserting images in Google Sheets. We'll also discuss the advantages and limitations of…

View Details

This step by step guide describes how you can build a web form for uploading files to Google Drive using Node.js, Express and Multer. The…

View Details

This tutorial describes how to extract pages from a PDF document from the command line. There are online tools available for splitting PDFs…

View Details

In a previous tutorial, we used a service account to connect to the Google Drive API from a Node.js application. We cannot use a service…

View Details

This step by step guide will guide you on how to upload files to Google Drive with a service account using Node.js . For this example, we…

View Details

This tutorial will show you how to import PayPal transactions into Google Sheets with the help of Google Apps Script. You can choose to…

View Details

Here we have an employee list spreadsheet with a column named Employee Name and a column named Employee ID . As soon as you enter a new…

View Details

You can use Google Apps Script to find all the inactive user accounts in your Google Workspace domain. The script will find all the users…

View Details

The HYPERLINK formula of Google Sheets lets you insert hyperlinks into your spreadsheets. The function takes two arguments: The full URL…

View Details

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…

View Details

When you delete any file or folder in your Google Drive, it is moved to the trash folder. The deleted file stays in trash for 30 days and…

View Details

This quick tutorial will walk you through the steps to create your own custom stickers from photos in Canva . The basic idea is that you…

View Details

You can use the filetype: operator in Google to search for Office files of specific types. For instance, a query like invoice template…

Digital Inspiration: Recent Episodes

None

Cron Expressions

Time Triggers in Google Apps Script

Google Script meets Cron Expressions

Load the Cron Parser Library

Add Trigger with Cron Expression

How to Enable Duet AI

1. Purchase the Duet AI add-on

2. Assign Duet AI licenses

Automate Sheet Sorting with Google Apps Script

Advanced Sort Options

Sort Google Sheets by Date

References

The Best  Mac Apps & Utilities

Free Udemy Courses on Programming

Use the Udemy API with Google Sheets

Related reading:

New MX Records for Google Workspace

MX Records References:

Conditionally Display Content

Define the conditional sections

How to Insert Clickable Phone Numbers in Web Pages

How to Type Phone Numbers in Google Sheets

How to Make Phone Numbers Clickable in Google Sheets

Clickable Phone Numbers in Google Docs and Slides

The Technical Details

npm Commands You Should Know

Instantly run packages without installing

Get package details

Install npm packages faster

Recover space

Quickly download a Git repository

List installed packages

Find unused dependencies

Find unused source files

Find outdated dependencies

Update the package versions

Remove extra packages

Find vulnerable packages

Useful NPM Package Websites

Prepare the Students’ Data in Google Sheets

Bulk Create Folders in Google Drive

Create Multiple Folders in Google Drive in Apps Script

Gmail Batch Request

1. Get a list of messages in Gmail

2. Prepare the batch request

3. Make the batch request

Embed YouTube Videos without Increasing Page Size

Lite YouTube Embed Demo

Light and Responsive YouTube Embeds

Embed YouTube Videos Responsively - Tutorial

More YouTube Embed Tutorials

Create Google Cloud Storage Bucket

Create Storage Service Account

Write Google Apps Script Code

Upload Files to Google Cloud Storage