Send Personalized Emails with SMTP and Mail Merge in Google Sheets

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 SMTP

Mail 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 Credentials

Google 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

Send Email with SMTP

Prepare Mail Merge Data

Open 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.

SMTP for Mail Merge

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.

An Improved SUBSTITUTE Function for Replacing Multiple Values in Google Sheets

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 SUBSTITUTE

Now 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 Function

Nested SUBSTITUTE Functions

=SUBSTITUTE(
  SUBSTITUTE(A1,"{{name}}","Amit"),
   "{{company}}","Digital Inspiration")

Multiple Substitute Function for Google Sheets

The 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.

Multiple Substitute in a Single Cell

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.

How to Email Screenshots of Google Sheets on a Schedule

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:

Google Sheets Screenshot

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.

Screenshot of Google Sheets

Automate Screenshots of Google Sheets

Go 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

Screenshot Spreadsheet 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:

Gmail - Google Spreadsheet Image

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

Install Email Google Spreadsheets

Forminator Crosses 500K+ Installs and Reaches Top-Five Free Form Plugin Status 📈

With over 500,000 downloads, Forminator has quickly evolved into a powerful plugin, beloved for its user-friendly design, extensive features, and robust integrations.

Forminator Downloads
Forminator’s journey from zero to form hero … 500K+ downloads and counting!

How do you grow a plugin from zero to hero and make it shine in a market saturated with plugins? Read on to find out…

Since its inception, Forminator has been consistently evolving, introducing new features and integrations that have significantly enhanced its functionality and user base.

In this article, we take a look at how Forminator became the go-to custom form solution more web developers are choosing to add interactive contact forms, payment forms, polls, quizzes, and more to WordPress sites.

Forminator
Forminator started out as a nerdy superhero before becoming a fully white labeled plugin!

We’ll cover the following sections:

Forminator’s Journey

Forminator began its journey on 17th May 2017, marking the commencement of a project that would significantly impact WordPress form building.

The first Forminator version was released on 20 February 2018 and launched to the world on September 12, 2018.

Here’s a look at the plugin’s journey and key moments:

Releases

2019 Enhancements

Continued Growth in 2020

Expanding the Ecosystem

Recent Developments

Milestones

  • October 2018: Surpassed 4,000 active installations.
  • January 2019: Reached 10k active installations.
  • June 2019: Achieved 20k active installations.
  • July 2020: Crossed 60k active installations.
  • August 2021: Exceeded 200k active installations.
  • February 2023: Surpassed 400k installations.
  • January 2024: Reached a landmark of 500k installations.

The above timeline not only highlights the continuous improvements and enhancements made to Forminator but also showcases the growing trust and reliance of the WordPress community on this versatile plugin.

What Users Like Most About Forminator

“Forminator Pro has been life-changing.”

Jamie J. WPMU DEV Member

So, what makes Forminator so popular?

According to our users, here’s what many like best about Forminator:

1. Comprehensive Free Features

Forminator offers a vast array of functionalities without the need for a Pro upgrade. Users can create custom forms, polls, quizzes, and payment forms at no cost that support integrations with PayPal and Stripe for financial transactions, making the plugin an all-encompassing solution for gathering information, engaging users, and facilitating payments.

2. User-Friendly Design

Forminator boasts a drag-and-drop visual builder that simplifies the process of form creation and customization. The plugin’s accessibility features and intuitive interface cater to both beginners and advanced users, ensuring that adding forms to a WordPress site is simple, easy, and very straightforward.

3. Responsive Support Team

WordPress users rave about our award-winning customer support, which is highly responsive and helpful, and extends not only to all of our products and services, including Forminator, but also anything WordPress-related.

Also, as a Stripe Verified Partner, Forminator benefits from a direct line to additional resources and support. This partnership highlights our plugin’s commitment to provide a reliable service and ensure that users can maximize the functionality of their Stripe integration.

4. Wide-Ranging Integration

Forminator offers seamless compatibility with a plethora of third-party services, including email marketing tools, CRM systems, and project management platforms.

Integration with over a dozen services like HubSpot, Mailchimp, Google Sheets, Slack, Trello, and newer additions like Mailjet (plus over 1000 apps when using Forminator’s Webhooks integration) ensures seamless workflows and enhanced productivity.

Additionally, Forminator’s full-featured API enables connections with a wide range of services and automation tools, enhancing its utility and flexibility.

5. Deep Customization

Beyond basic form creation, Forminator allows for deep customization through a variety of drag-and-drop blocks and field types. Users can tailor forms to their specific needs, whether for GDPR compliance, social media quizzes, or customer feedback.

6. Advanced Functionalities

For users requiring more sophisticated capabilities, Forminator offers advanced features such as e-signature collection, subscription and recurring payments on Stripe, custom login/registration forms, multi-step forms, file uploads, and integrations with many applications and platforms.

These functionalities, available with Forminator Pro, extend the plugin’s utility to virtually any scenario.

Forminator’s Challenges

Forminator’s popularity continues to grow despite having faced challenges and hurdles since launching, such as:

Standing Out in a Crowded Market

The WordPress plugin ecosystem is highly competitive. With almost 60,000 free WordPress plugins listed in the WordPress.org plugin directory and thousands more premium and custom plugins available to extend and enhance the functionality of WordPress sites, it’s tough for any plugin to stand out.

Forminator has continually addressed this challenge by developing unique features and making these freely available where most would charge for them, consistently delivering exceptional, award-winning support to users, and always being willing to actively engage with its user community.

User Feedback and Feature Requests

They say that you can’t please everybody, but we try our darndest to do this.

Handling the diverse needs of our users has required the implementation of multiple feedback systems to prioritize plugin improvements.

We encourage user feedback and feature requests not only for Forminator, but for all areas of our platform, and we do this by actively engaging our user community through our support channels, member’s forums, holding Discussions of The Week (DOTW), running user surveys, and more.

User Interface Usability

Trying to achieve a balance of providing advanced features while remaining easy to use has led to extensive and continuous UX/UI testing and refinements of Forminator’s interface and feature usability by our development team.

For example, to view just how much work has gone into improving the Free version of Forminator alone, check out the plugin’s extensive and fully documented development log on WordPress.org.

As part of our focus and commitment to continually improve Forminator’s user experience and user interface for WordPress users of all skill levels, we provide in-depth and updated plugin documentation, practical tutorials and guides, and an AI-powered knowledgebase.

The Secret To Forminator’s Success

While we’re really pleased with Forminator’s growth to date, achieving success amidst a saturated WordPress plugin market has been no small feat.

Developing Forminator from an idea into one of the “Top 5” form plugins for WordPress is an ongoing effort requiring not only the daily cooperation between all members of our team and feedback from our valued users, but also a comprehensive approach that involves:

  • Performing diligent market analysis to understand the current landscape and identify gaps, trends, and opportunities,
  • Conducting in-depth research of our existing user base to understand their needs, preferences, and pain points,
  • Implementing robust and meticulous tracking of user behavior to understand user interactions with the plugin and which features are being most utilized or neglected.
  • Gathering insights from our analysis, support, and product development teams to prioritize and develop new features and innovative functionalities that will exceed our users’ expectations and deliver greater value than our competitors,
  • Continuously iterating and enhancing the plugin, leveraging user feedback and data-driven decisions.

And finally, and very importantly …

Bundling all of the above into one very robust, professional, and well-supported plugin and then giving it to the WordPress community for FREE!

Forminator plugin banner on WordPress,org
Download Forminator for Free at WordPress.org

Forminator: Looking To The Future

As Forminator’s popularity and usage increases, our plugin development team remains hard at work and committed to achieve the following aims:

Introduce New Free Features

Innovation and user feedback drive the addition of new functionalities. To keep track of new developments and view Forminator’s upcoming features, visit our Roadmap.

Pursue Community-Driven Development

As our entire platform is geared toward helping WordPress web developers and digital agencies grow their business, we value our users’ insights and their experiences, and this significantly influences our development roadmap.

Enhance Integrations

As new technologies and applications emerge, we will continue to expand Forminator’s integrations with key services and platforms to offer more versatility to our users.

Focus on Performance and Reliability

Our plugin development team regularly reviews and optimizes all aspects of Forminator to ensure that as new features and functionalities get added, the plugin will continue to remain fast, reliable, and efficient, whether users choose to download and use the free or Pro version.

Have You Discovered the Power of Forminator Yet?

Forminator’s journey from its initial release to surpassing 500,000 downloads is a testament to WPMU DEV’s commitment to innovation, user satisfaction, and adaptability.

With a focus on user-friendly design and versatility, comprehensive features, and robust integrations, Forminator’s broad feature set and ease of use make it a standout choice for all WordPress users looking to enhance their site’s interactivity, collect payments, and more, all while maintaining a user-friendly and accessible interface.

The future looks promising for Forminator, with plans for new features catering to a wide range of needs, enhanced integrations, and a dedication to performance and reliability.

If you are a current Forminator user, we thank you for helping us reach the 500k download milestone. We truly value your feedback and input into helping us to continually improve the plugin’s features and ease of use.

If you haven’t experienced the power and simplicity of Forminator yet, we invite you to make it a staple in your web development or digital agency’s toolkit. Download Forminator for free or become a WPMU DEV member today and experience Forminator Pro risk-free for 30 days.

We look forward to celebrating 1 million downloads of Forminator with you in the very near future.

Compare MacBook Prices Worldwide with Google Sheets

Looking to buy the new Macbook Pro with M3 chips? Wondering if it would be cheaper to purchase a Macbook in your local Apple store, or ask a friend who is travelling from Singapore or Japan to bring one for you?

Here’s a Google Sheet that can help you compare prices of MacBooks in different countries. It takes the current prices of MacBooks from Apple online stores in different countries and converts them to a common currency (US Dollars). The exchange rates are fetched directly from Google Finance so the prices will update automatically when the exchange rates change.

Macbook Prices worldwide

How the Macbook Price Comparison Sheet Works

I have written a Node.js script that fetches the current prices of MacBooks from the Apple website and writes them to Google Sheets. Here’s the code that scrapes the Apple website and parses the HTML to extract the prices.

Get Macbook Prices from Apple Website

Apple uses JSON-LD to embed structured pricing data in their web pages that can be easily parsed using cheerio. If the prices were not embedded in the wepage, an headless browser like Puppeteer would have been required to scrape the data.

const fs = require('fs');
const cheerio = require('cheerio');
const regions = ['us', 'in', 'sg', 'uk', 'ae', 'jp'];

const scrapeAppleStore = async (region) => {
  const url = `https://www.apple.com/${region}/shop/buy-mac/macbook-pro`;
  const response = await fetch(url);
  const html = await response.text();
  const $ = cheerio.load(html);
  const country = $('a.as-globalfooter-mini-locale-link').text().trim();
  const data = [];
  $('script[type="application/ld+json"]').each((i, elem) => {
    const json = JSON.parse($(elem).text());
    if (json['@type'] === 'Product') {
      json.offers.forEach((offer) => {
        const { priceCurrency, price, sku } = offer;
        data.push([country, sku.substring(0, 5), price, priceCurrency]);
      });
    }
  });
  return data;
};

(async () => {
  const promises = regions.map(scrapeAppleStore);
  const values = await Promise.all(promises);
  const prices = values.filter((value) => value.length > 0);
  fs.writeFileSync('prices.json', JSON.stringify(prices, null, 4));
})();

Get Currency Exchange Rates from Google Finance

The next step is to convert the prices of MacBooks in different currencies to a common currency (US Dollars). The exchange rates are fetched from Google Finance using the GOOGLEFINANCE function of Google Sheets.

=BYROW(A1:A27, LAMBDA(e, IF(e="USD",1, GOOGLEFINANCE("CURRENCY:USD"&e))))

The function accepts the currency code of the source and target currencies and returns the exchange rate. For instance, the formula =GOOGLEFINANCE("CURRENCY:USDINR") will fetch the current exchange rate of US Dollars to Indian Rupees.

Google Finance - Currency Exchange Rates

Build the Macbook Price Comparison Sheet

Now that we have prices in a common current, we can build the price comparison table using the INDEX MATCH function of Google Sheets. The lookup criteria includes two columns - the SKU of the Macbook model and the country. The relevant formula is:

=INDEX(Data!$A$1:$E$648,MATCH($A3&B$1,Data!$A:$A&Data!$C:$C,0),5)

Also see: Monitor iPhone Stock with Google Sheets

Monitor Your Stock Portfolio with Google Sheets and Receive Daily Email Reports

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.

Monitor Stocks in Google Sheets

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.

Email 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.

Embed Email in Google Sheets

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.

Email Schedule Builder

Also see:

How to Create Personalized Images in Bulk with Google Sheets

Yesterday marked Friendship Day, and to celebrate, I sent a personalized image to each of my friends via WhatsApp. The images were created in bulk, but each graphic had the person’s name, making the greetings unique and heartfelt.

To achieve this, I did employ some automation. First, I gathered the names of my friends in a Google Sheet. Then, I designed a graphic template in Canva and imported the design in Google Slides. The template had a placeholder - {{Friend's Name}} - that would be replaced with actual values from the Google Sheet.

The Source Template

Here’s the source data and the image template. The placeholder {{Friend's Name}} in a graphic was replaced with actual values from the Google Sheet.

Customer data and image template

The Generated Graphic

And here is the completed deck in Google Slides, where each slide is generated from individual rows of the Google Sheet. You may notice that the names are distinct on every slide for personalization.

The best part is that the customization options aren’t just limited to text - you can also add unique QR Codes, profile pictures and more for more personalization.

Personalized Graphic Designs

How to Auto-Generate Images in Bulk

For those interested in creating personalized images, it’s a simple process.

Start by creating a template with a single slide in Google Slides, adjusting the deck size to match your desired output image size. Then, populate a Google Sheet with the personalization data. Each row of the sheet will generate a unique image.

Install the Document Studio add-on and follow the getting started guide to create a new workflow. For this particular task, I selected the Append (Combine) task, as it allowed me to generate a combined deck containing all the personalized images.

Append Google Slides

  1. Next, select the Google Slides template from your Google Drive that contains your base design. Document Studio will automatically create a new deck to house your auto-generated designs but you can also pick an existing deck from the target presentation field.

Google Slides Template

  1. Save your workflow and choose the Run option to generate your personalized images. Document Studio will automatically generate images for each row of the spreadsheet, but you also have the option to select rows for which the images should be generated.

Create Images from Spreadsheet

Click the Run workflow button, and your personalized images will be ready in no time.

The possibilities are endless. You may use Document Studio to create quiz certificates, business cards, employee IDs, wedding place cards and other designs that necessitate only minimal text changes.

Give Document Studio a try and surprise your friends and family with personalized images for any occasion!

Emojis in Google Sheets

Emojis in Google Sheets

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 in Google Sheets but my favorite option is the built-in CHAR function.

You can copy the hex code of any emoji from unicode.org and then use the HEX2DEC function to convert the hexadecimal value into its decimal equivalent. The CHAR function will take this decimal number as input and returns the corresponding emoji symbol.

// Add the 😀 emoji to the active cell
=CHAR(HEX2DEC("1F600"))

// Get the hex value of 😀 emoji
=DEC2HEX(UNICODE("😀"))

Well the purpose of this guide is not to explain how to add emojis in Google Sheets but the problems that emojis may cause in your production workflows related to Google Sheets.

The problem with Emojis in Google Sheets

If you are to convert any Google Sheet to a PDF file programmatically, Apps Script can help. However, if your Google Sheet contains any emoji symbols, the PDF conversion engine will fail with a 500 error. This issue arises due to a known bug (see issue tracker) at Google’s end and there has not been any resolution so far.

Google Sheets PDF 500 error

Replace Emojis in Google Sheets

Google Add-ons like Email Google Sheets and Document Studio internally use Google Drive’s own conversion engine to convert spreadsheets into PDF files. the input sheet contains any emoji symbol, the PDF conversion would always fail owning to the bug.

The only workaround to this problem is to check your spreadsheet file for any emoji symbols and remove them before performating the PDF conversion.


/* 
*  Replace Emoji Symbols in Google Spreadsheet 
*  Written by Amit Agarwal www.labnol.org
*/

const replaceEmojisInGoogleSheet = () => {
  SpreadsheetApp.getActiveSpreadsheet()
    .getSheets()
    .filter((sheet) => sheet.getType() === SpreadsheetApp.SheetType.GRID)
    .filter((sheet) => sheet.isSheetHidden() === false)
    .forEach((sheet) => {
      sheet
        .getDataRange()
        .getValues()
        .forEach((row, rowIndex) => {
          row.forEach((cell, colIndex) => {
            if (typeof cell === "string" && /\p{Emoji_Presentation}/u.test(cell)) {
              sheet.getRange(rowIndex + 1, colIndex + 1)
                   .setValue(cell.replace(/\p{Emoji_Presentation}/gu, " ").trim());
            }
          });
        });
    });

  SpreadsheetApp.flush();
};

The Google Script will now scan your entire sheet, detect any cells containing emojis, and replace those emojis with spaces. After running the script, you can safely convert your sheet to a PDF file without encountering the 500 error caused by emoji symbols.

The \p{Emoji_Presentation} pattern in the regular expression matches emoji characters. The g flag is for a global search (to replace all occurrences) and the u flag is for Unicode mode (to properly handle emoji characters).

Google Sheet Emojis

How to Link Postal Addresses to Google Maps in Google Sheets

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 addresses to a “upcoming tour” tab on our website. The tab is shared with other real estate agents so they can see the addresses in the order they will be viewed on the tour. I would like to make all of the addresses clickable so that people can easily navigate to the next location on the tour. Is this possible?

Google Maps Links in Google Sheets

Make Addresses Clickable in Google Sheets

We can use custom functions in Google Sheets with the built-in HYPERLINK function to make any location clickable in the spreadsheet. And unlike other Google Maps functions, this approach doesn’t make any Maps API calls so there’s no restriction on the number of links that you can generate in a sheet.

Assuming that your postal addresses are in column A from row 2 to row 11, go to column B and paste the custom function. The first parameter refers to the cell, or range of cells, that contain the location that needs to be hyperlinked. You can set the second ‘satellite’ parameter to TRUE if you would like to link the map to the aerial view instead of the regular map view of Google Maps.

=GOOGLEMAPSLINK(A2:A11, FALSE)

The Google Maps Link function is obviously not part of Google Sheets but we can easily integrate it with the help of Google Apps Script.

Generate Maps URL with Apps Script

Open your Google Sheets spreadsheet. Click on “Extensions” in the top menu, then select “Apps Script.” In the Apps Script editor that opens, replace any existing code with the following function:

/**
 * Generate a Google Maps Link for any address
 *
 * @param {string} address - The postal address
 * @param {boolean} satellite - Show aerial view (TRUE or FALSE)
 * @returns {string} The Google Maps URL
 * @customFunction
 */

function GOOGLEMAPSLINK(address, satellite) {
  function createLink(query) {
    const baseUrl = "https://maps.google.com/?q=" + encodeURIComponent(query);
    const mapsUrl = baseUrl + (satellite ? "&t=k" : "");
    return mapsUrl;
  }

  return Array.isArray(address)
    ? address.map(createLink)
    : createLink(address);
}

The GOOGLEMAPSLINK function can generate map links for addresses in a single cell as well as a range of cells.

We can also add another column to the sheet that will create a clickable link with the address text. Paste the following ArrayFormula function in cell C1. See demo sheet.

=ArrayFormula(HYPERLINK(B2:B11,A2:A11))

Clickable Address in Google Sheets

The hyperlinked postal addresses can also be copied and pasted directly into Word, or any rich text editor, including HTML Mail for Gmail.

Create a BMI Calculator using Google Forms and Google Sheets

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 score is calculated in Google Sheets and a personalized report is generated in Google Docs. The user then receives an email with their BMI score and the PDF report.

👋 Complete this quick Google Form to calculate your BMI and receive a personalized report in your inbox.

BMI Calculator with Google Forms

Google Forms BMI Calculator

We have a Google Form that asks the user to enter their height and weight. The response is stored in a Google Sheet and the BMI score is calculated automatically using the BMI formula.

The PDF report is personalized and includes the user’s BMI score, BMI category, and suggestions on how to improve their BMI score. The user’s photo is also embedded in the report as shown in the screenshot above.

BMI Calculations in Google Sheets

We perform the following calculations in Google Sheets to calculate the user’s BMI score.

Age of the respondent

The form requires the user to enter their date of birth. We use the DATEDIF function to calculate the age of the user in years.

=MAP(C:C, LAMBDA(c, IF(ROW(c)=1, "Age", 
   IF(ISDATE(c), ROUND(DATEDIF(c, TODAY(), "Y"), 0),))))

BMI Calculations in Google Sheets

BMI Score (kg/m²)

The user enters their height and weight in the form. We use the MAP function to calculate the BMI score for each form response in the sheet.

=MAP(E:E,F:F, LAMBDA(ht, wt, IF(ROW(wt)=1, "BMI", 
    IF(AND(ISNUMBER(wt),ISNUMBER(ht)), 
      ROUND(wt/(ht/100)^2,2),))))

BMI Category

The BMI category is calculated using the array function.

=ARRAYFORMULA(IF(ROW(J:J)=1, "BMI Category", IF(ISNUMBER(J:J), 
  IF(J:J < 18.5, "Underweight", 
  IF(J:J < 25, "Normal weight", 
  IF(J:J < 30, "Overweight", "Obese"))),)))

Prepare BMI Report

We’ve created a template in Google Docs that will be used to generate personalized BMI reports for each user with the help of Document Studio.

BMI Report in Google Docs

The report uses conditional content to display suggestions based upon the BMI score of the user. For instance, if the BMI score is less than 18.5, the user is underweight and the report suggests that they should eat more calories.

The image is inserted in the report using a special Embed Image tag to add thethe photo uploaded by the user in the Google Form in the document.

Create BMI Workflow

Launch Document Studio inside the form responses sheet and go to Extensions > Document Studio > Open to create a new BMI workflow.

We’ll only process the form responses that have a valid email address, the age of the user is numeric, and the calculated BMI score is at least 10.

Workflow Conditions

On the next screen, add a Create File task and select the Google Docs template that we’ve created in the previous step. You may also want to change the name of the generated PDF file to include the name of the form respondent.

Click the Add Another Task button to add the Send Email task. This will send the generated PDF report to the user via email.

For the recipient’s email address, select the Email column from the Google Sheet. You can also customize the email subject and the body of the email.

Email BMI Report

The Attach Merged Files option should be enabled so that the generated PDF report is attached to the email. That’s it. Click the Save Workflow button and your BMI calculator is ready for use.

You can view the tutorial section for more ideas on Google Forms automation with Document Studio.

How to Make Phone Numbers Callable in Google Sheets and Docs

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.

html-telephone-links.png

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.

phone-number-formatting.png

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")

google-sheet-phone-links.png

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.

Phone numbers in Google Docs

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`);
});

How to Send Personalized Text Messages from Google Sheets

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 Google Sheets. You can thus build workflows that send text reminders when the invoices are due. Or you can get notified instantly when people fill out your Google Forms.

The SMS Workflow

The text messages in Document Studio are sent through Twilio but the app can be integrated with any SMS service as long as the service offers an API for sending text messages programmatically. You may use TextMagic, SimplyTexting, Vonage, ClickSend, RingCentral, or any SMS service of your choice.

SMS Google Sheets

For this example, we have a Google Sheet that contains the customer’s name, phone number, invoice number and the amount that is due. Column A of the Google Sheet, titled Send Reminder contains checkboxes and the SMS should be sent only for rows where this checkbox is selected.

Format the Phone Numbers

The phone numbers in Column D should conform to the E.164 international format and can have a maximum of 15 digits.

[+][country code][area code][local phone number]

We have added another column in the sheet, titled Phone Number and this uses a Google Sheets function to remove all non-numeric characters from the customer’s phone number. Paste this function in the D2 cell.

=BYROW(C2:C11,LAMBDA(Phone, REGEXREPLACE(Phone,"\D","")))

If you prefer using ArrayFormulas instead of the new BYROW function, the modified formula would be:

=ArrayFormula(REGEXREPLACE(C2:C11,"\D",""))

1. Build SMS Workflow

Now that our source data in Google Sheets is prepared, let’s build a workflow to send SMS messages. Launch Document Studio and create a new workflow.

SMS Workflow

2. Set the Send Criteria

On the next screen, choose the Process specific rows option and specify the criteria when invoice reminders should be sent over SMS. We’ve specified two conditions in our example:

  • The Checkbox should be selected or TRUE
  • The phone number field should not be blank

Send SMS Criteria

3. Configure SMS Service

Next, proceed to the Tasks screen and choose Webhooks from the list of services. We’ll essentially make an HTTP POST request to the SMS service provider’s API to send the text message.

And this request will be different for each SMS service depending on their API endpoints.

Webhook SMS service

3a. Send SMS with TextBelt

Go to textbelt.com and create an API key. You may send the first SMS message for free using textbelt as your API key.

Inside the Webhook service, set POST as the request method and the request URL as https://textbelt.com/text.

Switch to the Request Body tab and set the content type as application/json. The message field contains the text Dear {{customer name}} - your invoice # {{ invoice number }} for {{invoice amount}} is due. while the phone field contains the values from the {{ phone number }} column of the Google Sheet.

SMS TextBelt

3b. Send SMS with TextMagic

Create an account on TextMagic.com, go to the API settings page and click the Add new API key button to generate a new secret key.

Set the request URL as https://rest.textmagic.com/api/v2/messages and add two header fields - X-TM-Username and X-TM-Key to include the username and API key respectively. The request body should have the parameter phones for the recipient’s phone number and text for the SMS body.

You may optionally include the sendingDateTime parameter in the request body to schedule text messages and send them at a later date. Check the API docs for the full list of parameters.

TextMagic SMS

3c. Send SMS with ClickSend

If you prefer the ClickSend service for sending text messages, put https://rest.clicksend.com/v3/sms/send in the URL field with the request method set as POST. Choose Basic OAuth under the Authorization tab and provide your username and API key in the user and password fields respectively. You may get the credentials from your ClickSend dashboard.

For the request body, turn on the Use Raw Input option and the content type set to application/json. Put the following JSON in the body field. The Sender ID can either be a business name or mobile number and can be used by the recipient to identify who sent the message.

{
  "messages": [
    {
      "from": "your_sender_id_goes_here",
      "to": "{{ phone number }}",
      "body": "Dear {{ customer name }} - your invoice #{{ invoice number }} for {{ invoice amount }} is due.",
      "source": "Document Studio"
    }
  ]
}

ClickSend SMS

4. Activate the SMS Workflow

Now that you have configured the workflow to send SMS with your preferred texting app, go to the Save screen inside Document Studio and choose Save and Run to send the text messages to your customers.

You may also enable the Time Delay option to delay the sending of text messages until a condition is met. For instance, you may choose to send text messages only when the invoice due date is past 5 days.

Run SMS workflow

Also see: 🐘 Automate Workflows with Google Sheets

How To Price Your Web Development Services: The Definitive Guide

How do you price your WordPress development services? How do you avoid pricing yourself out of business? We surveyed our working web developer members to get the scoop and help you overcome the common dilemma of pricing.

Should you charge by the hour or per project? How do you come up with a quote? Maybe you offered a client an estimate and didn’t hear back?

Whether you’re a freelancer or starting your own web development business, if clients have ever found you overpriced, you’ve probably heard the following when asking around for advice:

“There’s no such thing as a market rate. Only you can decide what you’re worth.”

Or, maybe you don’t know how to price your services and decide that “bartering” is a great way to start your business…

“When I started this (WordPress) business, I exchanged a website for a 6′ fence around my property. The value of the fence was $3,000, so that’s what I got in exchange for their website.” – Phil, WPMU DEV Member

Yes, it’s all true, but it doesn’t generally apply to absolute beginner or intermediate-level freelancers or web developers running their own business.

Of course, if you have a shiny portfolio, or if your calendar is booked out for months in advance (because you’re that good) then sure, you don’t have to think about the going rates or what other people are charging.

The bottom line is, no one wants to work for peanuts. There’s a lot to consider when constructing your pricing model.

Fortunately, we have access to a 50k+ member community of web developers and we have gathered the information presented in this article through surveys and discussions.

Rather than talking about specific pricing and what to charge (which we cover in other articles – see links at the end of this article), in this article, we’ll go deep into how to set up pricing for your services so you can apply these ideas and come up with a pricing model that works for your business.

And so in this post:

  • We’ll show you a surefire way to price your projects so you’re never underpaid.
  • If you aren’t sure what your hourly rate should be, we’ll look at feedback we got from our web developer members, as well as looking at crowdsourced data and the going rates on popular freelance marketplaces.
  • We’ll also see some tools to help you validate your project estimate and ensure that it’s not way off the mark.
  • And in the end, we’ll see how you can meet your annual monetary targets using some cool freelance rate calculators.

Note: This guide is not for you if you’ve reached a point in your career where you can charge what you want. This guide is for you if you’re a freelance web developer or starting your own web development business.

Continue reading, or jump ahead using these links:

Fixed vs Adjustable Pricing

We recently approached our web developer audience and asked the question: How do you currently charge your web development clients?

It’s not cut and dry. Responses revealed some interesting results and patterns.

Fixed vs. adjustable? Is it best to have a set price, so you’ll know what you’ll make? Or, do you adjust accordingly – making the end payment vary?

Here are some insights that will help clear things up…

First, a positive factor of a fixed price is it’s straightforward for you and the client to understand.

There won’t be any guesses on how much the project will cost at the end of the day. Whereas an adjustable price – or hourly rate – may have sticker shock.

Or, the opposite of that might be severely undercharging.

“If you break down exactly what you do and how much time you spend on it, you will probably realize that you really do spend a lot of time on long-term clients, for which you must be compensated. If not, you’re doomed to that joy of the initial sale and payment, a nice dinner, and rent money, followed by the remorse of having demanding clients and overdue bills.” WPMU DEV Member – Tony G.

A scenario of a fixed amount would be if a developer charged a minimum of $740 for a blog-based website and $1000 for an eCommerce.

However, the client goes beyond the fixed amount, so the developer charges for add-ons (e.g., Google Search Console, maintenance, security, etc.).

Or several members mentioned it depends on the complexity of the project. It could range from $1,000 to $10,000. This is where you get into quoting (which we’ll get into next).

Whether you go with a fixed or adjustable price, make sure it makes financial sense, and you’re comfortable with your decision.

After all, there’s nothing worse than doing a development project when you feel like you’re being severely underpaid.

This takes us to another pricing structure, which is…

Hourly Rates

Hourly Rates ensure you’re getting paid for any time you put into your work. This includes everything from correspondence to actual work on a WordPress site.

A benefit is that you’ll capitalize on your time if a project takes longer than expected. That being said, a disadvantage of an hourly rate is if a task takes less time, you won’t earn as much as you originally anticipated.

Here are a few thoughts on this…

“For most projects, I do per-project, value-based pricing. I only use hourly for small things or ongoing maintenance work. Usually, it just ends up being what I feel makes sense – thinking about things like what’s the value, how much I need to be paid in order to care enough about it, how much are they able to afford, how much am I willing to simplify if they can’t afford much, will it contribute anything to my portfolio, do they even need what I offer, would it lead to more work, would I like working with them and will it be an enjoyable project, are there enough quality assets (photography, good copy, a usable logo, etc) available or am I going to have to lecture them about why we need better assets, what tools will I be able to use to build it and how much custom coding will I need to do, etc., etc. Eventually, I just pull a number out of thin air that I feel makes sense. Obviously, I know it’s not super scientific, but if we both feel the price is manageable and fair, then it doesn’t matter how much it is.” WPMU DEV Member – Greg

“Setting a price up front is great but be careful with this line of thinking: if your client asks you to justify your costs and you tell them your hourly wage is $20 because that’s what your up-front billing works out to, that’s all you’ll ever be able to charge them.” WPMU DEV Member – Phil

You can also include several services at once as a bundle. Packages are a great way to charge a higher rate and not chase after additional add-ons down the road.

Some great examples of bundled packages can be found in our additional services article.

There’s a way to incorporate fixed, variable, and hourly rates into the website of your service. The main point is it’s clear to your client what they’re looking for when it comes to costs.

Beyond the Hourly Vs. the Flat Fee Debate: Bottom-Up Estimating

Next let’s look at a helpful project estimating technique that applies seamlessly to all kinds of WordPress projects – “bottom-up estimating.”

What is it exactly?

Dick Billows from 4 pm explains:

“When the estimates of the amount of work, duration and cost are set at the task level, you can aggregate them upward into estimates of higher-level deliverables and the project as a whole.”

Basically, in bottom-up estimating, you list out all the tasks you expect to do as part of the project delivery and estimate individually for each of these tasks.

Next, you roll up these numbers to get the final project quote.

For example, for a WordPress site development project, the typical stages include:

  • Planning
  • Implementation
  • Testing
  • Review
  • Client training
  • Content upload
  • Soft launch (and launch)
  • Post-launch support and maintenance

If we had to apply the bottom-up estimating technique to this, we’d further break down these stages into the actual tasks for each.

At the task level, here’s what this project could look like:

Planning

  • Plan IA
  • Sketch out a sitemap
  • Determine the technology stack
  • Understand the functionality to custom code
  • Understand the functionality to prove via plugins (with or without the customization)

Implementation

  • Build the website
  • Install and fine-tune plugins

Testing

  • Check overall functionality
  • Check for broken links
  • Check sitemap
  • Check for access
  • Check performance metrics

Review

Client Training

  • Show the client the way around the site
  • Explain updates and ways of uploading content

And so on.

Once you’ve broken down a project like this into individual tasks, the estimating begins.

And because this estimation technique takes into account every task of the project, it ensures that you’ve paid for all the work you do. Simple.

To apply the bottom-up estimating technique to calculate your project quotes, follow this simple three-step process:

Step #1: List each task you’ll have to perform as part of the project

Don’t skip even the smallest of all tasks. You’ll be surprised to realize how much work you actually put in.

Step #2: Determine how long each of these tasks will take

Don’t club any of the tasks together; add a time tag to each.

As you can tell, determining the right amount of time for the different tasks is critical to making this technique succeeds, which means that this technique will only work if you know how long you take to do the different steps.

But what if you don’t know how much time you take for the different project tasks?

Well, if this is the case, all you can do is guess the time requirements for all the tasks. And create an estimate based on the guesses.

When you make such “guesstimates,” it’s possible to be over-ambitious. You may think that you’ll choose the technology stack in five hours, but you might end up taking a full day.

So don’t go with your first estimate. Consider these three things:

  1. The best-case estimate (a)
  2. The most likely estimate (m)
  3. The worst-case estimate (b)

And your final estimate (E) becomes: (a + m + b) / 3.

(This is a type of three-point estimation.)

Remember: tasks will always take longer than you think!

Also, this whole guesstimation process will work for you for now, but if you want to give estimates that never fail, you need to know how much work you can get done in a period of time.

To find this out, use a time tracking tool. Toggl is a great option to consider.

It has apps for all major platforms, so you can track time even when you’re working locally. You can also set Toggl to launch when you start your laptop.

This way, you won’t forget to log your work hours. Also, with unlimited clients and projects, Toggl’s free plan will cover you fully.

Step #3 : Add up all the time estimates and multiply with your hourly rate

The result is your project estimate. Add to this estimate the time that goes into communicating and collaborating with your client – don’t discount this time because it can add up fast if it’s a big project that will involve a lot of discussions.

Some freelancers also recommend padding such an estimate out with a few extra hours, just in case.

So if you can only make nearly accurate time calculations and set the right hourly rate, the bottom-up pricing technique will never leave you underpaid.

Clueless About Your Fair Hourly Rate?

If you have no idea of what a fair rate will be with the skills and experience you have, try using Bonsai’s web developer hourly rate calculator.

Bonsai allows you to compare freelance rates, taking into consideration your locations and years of experience.
Bonsai allows you to compare freelance rates, taking into consideration your locations and years of experience.

The Bonsai rate calculator uses insights from more than 30,000 contracts to offer suggestive hourly rates for developers based on their roles, skills, experience, and location.

Bonsai states:

“Many factors go into pricing, and this [the rate calculator] should be one of several you use. It can be helpful as a directional indicator: are you above, below, or within the average? The data can also be used to justify your rates to clients.”

Keep in mind the calculator is just a tool – you will be the best person to determine what a fair rate is for your services.

Does Bottom-up Estimating Look like an Hourly Pricing Model to You?

Maybe you’ll argue that the pricing technique we saw above is, in fact, an hourly pricing module. And, of course, you’re not wrong or alone in thinking so.

A few WordPress developers don’t like this method of estimating. They recommend offering a flat rate for a project based on factors like:

  • The ROI the client will get from hiring your services – For instance, how hiring you will get the client an additional $XX each month).
  • The market or niche of the client – For example, “adjusting” the cost based on the client by considering if the client is a solopreneur or a C-level executive in a top company. Essentially, the same service will be quoted at two prices.
  • Availability – This means charging higher if you’re booked out, occasionally discounting if in need of work).

All this stuff is great, but as I mentioned earlier in this article it hardly applies to beginner or intermediate freelancers — especially those who haven’t yet developed the knack of pricing.

How to Confirm That You Aren’t Underquoting

The following tools will help somewhat validate your project quote. They aren’t 100% accurate, but if you’re grossly undercharging these tools should indicate that.

#1: Project Quote Calculator from WebPageFX

This handy calculator will help you come up with estimates for website projects, based on inputs you can adjust.
This handy calculator will help you come up with estimates for website projects, based on inputs you can adjust.

The Project Quote Calculator suggests rates based on a site’s specifications like the number of pages, features like responsiveness, functionality and more.

If you offer additional services like design, development, copywriting, and SEO packages, this tool will give you a reasonable idea of what to charge for your different packages.

#2: Crew’s Budgeting Tool

Crew has built a fantastic app for its clients to use when enquiring about new projects.
Crew has built a fantastic app for its clients to use when enquiring about new projects.

Calculating Your Pricing – What The Experts Say

When calculating prices to charge clients, there’s a lot to contemplate. Everything from your time, complexities, tools, and more comes into play.

“If the project doesn’t call for any special skills, I’ll bid it at $50 per hour. But if it will require 3d or video, or some kind of custom javascript, then I’ll charge between $75 and $100 per hour. I also consider who the client is, what they can afford, and how annoying they might be to deal with.” WPMU DEV Member – Kahnfusion

Any client with no clue about the timeline or complexities might be anxious about hiring a developer hourly. After all, as far as they know, a project could take thousands of hours and be way beyond their budget.

“Here’s how I sell my projects: I am quoting your website project at $6,000. I quote my projects in lump sum costs because I’m meticulous and regularly spend over 100 hours when building a website, making sure all details are accounted for. At my hourly rate of $100, it just wouldn’t be very fair to you to charge by the hour, which is why I’m quoting a lump sum cost for the website. Any additional extras or customization you’d like to do afterward will be charged hourly. This way, I just told them I am potentially going to spend 100 hours on their project, and at an hourly rate of $100 that’s a $10,000 value they’re getting for a lump sum of $6,000. Hopefully, they see the value in this, and it also prepares them to pay $100/hour for extras going forward.” WPMU DEV Member – Phil

A good rule of thumb is to get a good estimation of your client’s needs. Get as many details as possible before starting the project. Narrow down your quote and include a general time estimate on your website.

Here’s an excellent example from Upwork:

Project estimates for developers.
As you can see, a lot can be included to ensure you give your client a good quote.

Our members mentioned a handful of things to consider when quoting.

  • Be conscious of the costs on your end: internet, phone bill, hosting, domain, WPMU DEV, subscriptions, outsourcing, and person-hours.
  • Quote a bit lower ($5-8K instead of $18.5K) so your agency can make more money on going from upgrades, maintenance, etc.
  • Compare the cost of a basic install (WordPress, plugins, etc) to a new business site with a completely custom template and integrated features.
  • Set the price for the project up front by calculating the hours it will take.
  • Base a lot of the quotes on the size of the company. Larger companies get charged more.
  • Consider the number of pages or number of products for eCommerce websites

Once you have a nice idea about the scope of a project, it’s just a matter of adding up the numbers and figuring out how much to quote.

One of our members, Ed, from GETSET2G, put together an excellent spreadsheet for calculating costs on Google Sheets. You can change currencies, add information, and more.

Google sheets example of pricing
A snippet of a Google Sheets spreadsheet with calculations for project costs.

Quickly and easily develop a spreadsheet – or similar method using Google Docs, Microsoft Excel, or another spreadsheet source. It’s a very simplistic way to accurately figure out a quote.

But, let’s say you want to jazz things up a bit. Luckily, there’s…

Quoting Software

Beyond spreadsheets, there is software that can help with quotes. Some companies can help organize and implement quotes for you and your clients.

Of course, we have our Clients and Billing tool. It makes it easy to set up and bill a client, then have them pay online. We’ve talked about it in previous articles, such as How To Get the Most Out of Client Billing.

That being said, these companies are a bit different where they focus on quotes and billing. For example, they help focus on the actual presentation of the quote and the “wow” factor.

Here’s a quick rundown of three quoting services. (And please note, these companies are in no way affiliated with WPMU DEV. They have solid reputations, established, and we feel they make for good options.)

Scoro

Scoro header.
Scoro is worth checking out for your quoting template needs.

Scoro is unique in several ways. They have predesigned templates for quotes, where you can quickly turn that quote into a purchase order, contract, or invoice.

A scoro quote.
There are a lot of details you can include in your quote.

Other aspects include tracking results in real-time, partial payment options, and organizing by teams.

There is no live demo on their website, but you can quickly request a demo with your email address.

Qwilr

Qwilr banner.
Qwilr is here to impress.

Qwilr is all about impressing your potential client with their proposal templates. They have options for embedded images, videos, websites, and more to show your potential client(s).

A quote from qwilr.
Here’s a snippet of a quote that can be used.

Plus, they have interactive quotes. This allows you to show your client what costs would be when additional services or projects are added.

They have an example template that’s available to view. It shows all the details and professionalism you can include in your proposal to make you stand out.

Nusii

nusii header.
Lastly, Nusii has an impressive layout that can “wow” your potential clients.

Nusii is another option that can make you stand out from the competition. It has templates that can be modified in a few clicks, proposal notifications, and a save & insert option for your content, so you can eliminate rewriting.

nusei pricing options.
If a client selects these options, you’ll be notified immediately.

It also features integrations with companies like Zapier, Slack, HubSpot, Stripe, and more.

Check out their example template, and you’ll see how eye-popping your proposal can be with their help.

Some Pricing Questions That You Might Still Have…

1. “How much does the average WordPress developer charge per hour for their services?”

Or: “What’s the going hourly rate for WordPress developers?”

Most WordPress developers don’t display their hourly rates on their portfolios. However, here’s some information on the going rate for the best talents on the different freelance marketplaces. All hourly rates quoted below are in USD:

3. “How much should I charge to develop a WordPress site?”

Take the bottom-up approach, which we outlined above. If you aren’t sure about the time, guess. If you’re clueless about a feasible hourly rate, use Bonsai’s hourly rate calculator to get an idea.

4. Additional pricing information

In other articles, we cover pricing for services like:

Choosing Pricing Based on Your Income Goals

Now, you didn’t become a freelancer or start a web development business to live on a project-to-project or month-to-month basis.

You did so to lead a life of freedom! Which needs financial security. And you can easily get this if you charge your services in a way that supports your income goals.

Check out this freelance rate calculator. It will help you determine what your rates should be to meet your desired annual income target.

Just input your desired annual salary and details of what you’re currently charging. The freelance rate calculator will then give you an analysis of how you can meet your target.

Raising Your Rates

Unlike a typical 9-5, which ensures a regular pay check, what you bring in is determined by the rates you set.

And speaking of a regular job, would you want to work somewhere where your pay never goes up? The same goes for working as a developer.

You should raise your rates as your value increases with your clients and time goes by.

After all, your clients know that replacing you wouldn’t be great (and a pain), and you need to be paid what you’re worth.

Your clients “get” you, and you get them. It’s natural to raise rates, and you might find it surprising that it usually won’t scare clients (and they’ll happily pay you the new amount).

We’ll review how to do this – with insights from WPMU DEV members and more information from other sources.

How to Raise Your Rates

So, how is it done?

There’s a fine art to raising your rates. You can’t just send an invoice with an increased rate and expect your clients to pay without hesitation.

You can do a few things to ensure a smooth transition to a higher rate.

Allow for Plenty of Time

To start with, allowing time for the client to adjust to a higher rate is the most important thing you can do. Clients wouldn’t respond too positively to surprise rate increases.

Ensure that your rate increase is in writing. You can add the upcoming rate increase to a recent invoice (highlighted, if possible) and a separate email.

A subject line of “Notification of Rate Increase” or “Rate Changes” should suffice.

There’s no precise timeline, but a good 2-4 month notice is a good amount of time to let your clients know. This gives them months to adjust their budget and prepare. If you can let them know even sooner (e.g. 6-8 months out), that’s never a problem. The sooner the better so that a rate increase isn’t sprung on them.

Also, when you get a new client, let them know that you may (or will) increase your rates periodically. This way, it’s not surprising when a rate increase notification heads its way in the future.

Plus, do not increase new client rates too soon. A good practice is to keep them at the same rate for a year rather than increasing the rate – even if the rate is upped for existing clients.

A good working relationship with your clients is important before upping the price. Being as transparent as possible with your rate increases trust in your business – and you as an individual.

Limit Increase Amount

A big question is: how much should I raise my rates?

It’s a good question to ask yourself because there’s a lot on the line. You don’t want to raise them too high because then your clients might leave (e.g. more than 50%).

So, is there a magic number?

Limiting rate increases between 5% and 10% is a good rule of thumb. That’s not a huge percentage; however, it can add up quickly and won’t put an enormous financial burden on your clients.

How to Answer Your Clients on Why You’re Raising Rates

This is the part that can bring some anxiety and nervousness. It’s not easy to send an email mentioning a price increase. And the reality is that you might lose clients after raising your rates.

However, clients who trust you and know your worth will be okay with a price increase when you explain why it’s happening.

A common factor that pops up that justifies a rate increase is hidden costs. Things that you, as the developer, have to pay for DOES often change over time.

For example, you may need to pay for hosting or outsourcing your design work. Maybe it’s something as simple as you need a new computer system to handle the workload because your old system is outdated. Or, hosting costs went up, and you have to pay for covering the increase.

At the end of the day, you may still have an unhappy client. At that point, you can reflect on the work itself.

Are they happy with what you’ve done up to this point? If so, is it worth it for them to continue working with you? Ask them these questions.

As we mentioned, most good clients will accept and stay by your side if you follow what we’ve discussed (e.g. not upping the rate dramatically on short notice).

You may lose some clients or have a few clients hesitant to pay, but chances are, they will be the clients that aren’t the most valuable to you – and they’ll be few and far between.

How to Tell Clients About Increasing Rates

A simple email can do the trick. Or a phone call. Whatever is easiest for you and your clients. You know best how you communicate well with specific clients.

If you go the email route, here’s an example of what you can include:

Hello (Client Name):

When we started working with you, I mentioned raising my rates annually. I do this to cover increasing costs and to ensure I can fully provide you with the best service possible.

On (Date) I will be increasing my rates by 5%. This will be reflected in upcoming invoices.

I can’t thank you enough for your continued business. I enjoy the work I do with you and look forward to providing you with the best service in the future.

If you have any questions or concerns, please feel free to contact me at any time.

Of course, this can be edited accordingly. However, in a nutshell, a brief explanation of why you’re increasing your rates, how much it will be when it occurs, and “thank you” is best to include.

How Other Developers Increase Their Rates

Here are some quotes from other developers (members) on how they handle increasing their rates:

“If any of my cost of Goods sold increases, I need to constantly adjust my pricing. And when I learn a new skill or gain more expertise, I raise my minimum project starting cost to new customers but marginally will increase it to the existing customer by winning the fact that I am upgrading their technology and their user experience and make them feel I am constantly updating them.” Rajiv – WPMU DEV Member

“Times demand changes in prices, and as we get better, we tend to charge better.” Fabio – WPMU DEV Member

“Market rises and dips? No. My own constantly improving skills & experience level? 1000000000% This is the primary and only reason why I increase my rates. I only do this to earn extra funds, so I invest in doing quality work every time and leverage that to increase my rates going forward.” Phil – WPMU DEV Member

“We have not adjusted prices due to changing skills. We were an established and experienced firm when we acquired the website group. We have increased prices over the years in response to the current market trends in our area and the fair salaries and benefits we provide our professional employees. Our current hourly rate for doing modification work on an established site is $102 / hour.” Jim W – WPMU DEV Member

“As my business has grown and I’ve become more sought out, I have been increasing the dollar value of my proposals. I haven’t had any pushback. I have also increased monthly hosting fees for a few of my early clients by $10/month, with no reaction whatsoever.” Brad – WPMU DEV Member

“The rate has always increased commensurate with value, and always lagging behind the market. We always try to keep existing clients at the same rate and just start new clients with the new rates. They appreciate this and it’s a part of the “endearing” long-term commitment that we have with one-another. When we have raised the rate for an existing client it was done with significant advance notice that gives them a chance to shop for different resources for the next project. They recognize that we give them that opportunity, again, because we are partnered with them to help keep their costs down while still using quality solutions. Since we continue to provide the quality solutions, they don’t feel a need to look elsewhere. It works for everyone.” Tony G – WPMU DEV Member

“I slowly raised my rates until I started getting pushbacks as I got started. I wanted to get to the point where I could charge what I need and deserve, while also being within my target clients’ budgets and can provide the value they need. I’ve also raised my rates as I built for my specific niche. What I have done is offer deferred payments to loyal clients who hit a rough month or with extraordinary circumstances. I’ve also offered split-payment plans for projects with higher investments. This helps the client get what they need and it helps me with recurring income over several months, which I prefer over a lump sum followed by little or no income.” Keith – WPMU DEV Member

The Price Is Right: Crunching Your Numbers

Knowing how to price feels nice!

Hopefully, this article has given you ideas on how to set your WordPress development business pricing so you don’t undersell your services and charge clients what you’re worth.

We’ve looked at quite a few numbers in this post, from hourly rates to project quotes to theme/plugin prices. Remember that they’re all subjective because pricing differs from project to project and from business to business.

For additional resources that will help you advance your web development business success, be sure to read our articles on eight ways to get new clients and our guide on the secrets to getting freelance work.

And if you’re not a WPMU DEV member yet, try our risk-free plan to implement these pricing ideas into your business with our complete all-in-one WordPress platform.

How to Auto Format Google Form Responses in Google Sheets

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 Forms will not add any formatting or styles to the new row that you may have applied to the previous rows of the sheet. Let me illustrate this with a small example.

Here’s a Google Sheet that is storing Google Form responses. I’ve changed the default font family to Droid Sans, center-aligned the Country and Age column and also applied a different date format to the Date of Birth column.

Google Forms Response Sheet

Everything looks good but as soon as a new form submissions is made, the new row appended to the Google Sheet via Google Forms will lose all the formatting.

The cell alignment is not preserved, the custom date formats are ignored and so is the default font size and font family. Here’s a screenshot of the same sheet but with a new row added through Google Forms.

Google Forms Auto Formatting

Also see: Automate Google Forms through Workflows

Auto Format New Rows in Google Sheets

Since there’s no way for us to override this Google Forms behavior, we can take the help of Google Apps Script to automatically format new rows in Google Sheets that are added through Google Forms.

The idea is simple. We’ll create an onFormSubmit trigger inside the Google Sheet that will be executed whenever a new form is submitted. This trigger will take whatever formatting that has been applied to the previous row and apply to the current row.

To get started, open the Google Sheet and format the last row with the styles that you would like to apply to incoming form responses.

Next, go to Extensions > Apps Script menu and copy-paste the Google Script below. Run the createTrigger and you are good to go!

/**
 * @OnlyCurrentDoc
 */

const createTrigger = () => {
  ScriptApp.getProjectTriggers().forEach((trigger) => {
    ScriptApp.deleteTrigger(trigger);
  });
  ScriptApp.newTrigger('formRowOnFormSubmit').forSpreadsheet(SpreadsheetApp.getActive()).onFormSubmit().create();
};

const formRowOnFormSubmit = (e) => {
  if (!e) {
    throw new Error('Please do not run this function manually!');
  }
  const { range } = e;
  const row = range.getRowIndex();
  if (row > 2) {
    const sheet = range.getSheet();
    // Select the previous row range
    const sourceRange = sheet.getRange(`${row - 1}:${row - 1}`);
    // Get the recently added row
    const targetRange = sheet.getRange(`${row}:${row}`);
    // Copy the format only from the previous row to the current row
    sourceRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
  }
};

Conditional Formatting in Google Sheets

Learn more about conditional formatting in Google Sheets that allows you to apply automatic formatting to cells in spreadsheets that meet certain criteria.

How to Send WhatsApp Messages from Google Sheets using the WhatsApp API

WhatsApp with Google Sheets

This tutorial describes how you can use the new WhatsApp API with Google Apps Script to send WhatsApp messages from Google Sheets. The same approach would also work for sending WhatsApp messages from Google Forms when new form submissions are received.

Step 1: Create a WhatsApp App

Go to developers.facebook.com and click the Create App button to create a new app that we’ll use for sending WhatsApp messages.

Facebook WhatsApp App

Select Business as the app type.

Give your app a descriptive name (do not use any Facebook trademarks like WhatsApp or Facebook in the app name) and click the Create App button to create the app.

WhatsApp App Name

Once the app has been created, click the WhatsApp button on the next screen to add WhatsApp sending capabilities to your app.

On the next screen, you will be required to link your WhatsApp app to your Facebook business account. You will also have the option to create a new business account if you don’t have one yet.

Add WhatsApp App

Step 2: Add Recipient’s phone number

Facebook will provide you with a test WhatsApp phone number that will be default sending address of your app. For recipients, you’ll have the option to add a maximum of 5 phone numbers during the development phase without having to make any payment.

Your WhatsApp app will provide you with a temporary access token that will be valid for 23 hours. Make a note of this token as we’ll need it in a later step.

WhatsApp Phone Number

Next, click the Recipient Phone Number dropdown to add up to 5 different WhatsApp phone numbers to your app. You’ll receive a verification code on the added numbers and you’ll be able to send WhatsApp messages to only numbers that have been verified with the code.

Verify Phone Number

Step 3: Create WhatsApp Message Template

Switch to the template manager and create a new WhatsApp message template.

For this example, we’ll choose the category as Account Update and give a unique name for your message template. For languages, we’ll choose English for which the code language is en. If you use another language, make a note of the code language as it is required for sending messages.

WhatsApp Message Template

Write the Personalized Message

We are using variable parameters in the message and these will be replaced with the actual values from the Google Sheet. It is very similar to markers that you may have used inside Mail Merge and Document Studio with a small difference that these are positional markers and not named markers.

Here’s our message template where variables {{1}} and {{2}} are for customer name and item name respectively.

WhatsApp Message Template

It may take up to a minute for WhatsApp to approve your new message template.

Step 4: Send WhatsApp Messages

Now that all our configuration on the Facebook / WhatsApp side is complete, let’s work on the Google Sheet that will actually send these personalized WhatsApp messages in an automated manner.

Click here to copy the WhatsApp Sheet in your own Google account.

Google sheets WhatsApp

Next, add the phone numbers (with country code) in the Phone number column of the Google Sheet. You should only add numbers that you have verified with your test WhatsApp account in the previous step.

Then go to the Extension menu and choose Apps Script to open the underlying script. Replace WHATSAPP_ACCESS_TOKEN and WHATSAPP_TEMPLATE_NAME with the values that you have copied in the previous steps.

Click the Run button inside the Apps Script editor and it should instantly send the WhatsApp message to your listed phone numbers.

And what you have below is the actual WhatsApp message sent by the WhatsApp API replacing the variable markers in the template with actual values from Google Sheets.

WhatsApp Message Text

The Technical Details

How WhatsApp API works with Google Apps Script

The Google Apps Script connects to Google Sheets and retrieves the details of customers, including phone numbers, that are to be sent messages through WhatsApp.

// Get data from Google Sheets
// for sending messages through WhatsApp
const getSheetData_ = () => {
  const sheet = SpreadsheetApp.getActiveSheet();
  const [header, ...rows] = sheet.getDataRange().getDisplayValues();
  const data = [];
  rows.forEach((row) => {
    const recipient = {};
    header.forEach((title, column) => {
      recipient[title] = row[column];
    });
    data.push(recipient);
  });
  return data;
};

Next, the script iterates through each row of the sheet and sends a WhatsApp message by invoking the WhatsApp Cloud API with the UrlFetch service.

// Send Message with WhatsApp Cloud API

const sendMessage_ = (e) => {
  const apiUrl = 'https://graph.facebook.com/v13.0/114746974570888/messages';
  const request = UrlFetchApp.fetch(apiUrl, {
    muteHttpExceptions: true,
    method: 'POST',
    headers: {
      Authorization: `Bearer ${WHATSAPP_ACCESS_TOKEN}`,
      'Content-Type': 'application/json',
    },
    payload: JSON.stringify({
      type: 'template',
      messaging_product: 'whatsapp',
      to: e.recipient_number,
      template: {
        name: WHATSAPP_TEMPLATE_NAME,
        language: { code: LANGUAGE_CODE },
        components: [
          {
            type: 'body',
            parameters: [
              { type: 'text', text: e.customer_name },
              { type: 'text', text: e.item_name },
              { type: 'text', text: e.delivery_date },
            ],
          },
        ],
      },
    }),
  });

  const { error } = JSON.parse(request);

  if (error) {
    Logger.log(`😞 ${error}`);
  } else {
    Logger.log(`Message sent to ${recipient_number}`);
  }
};

const main = () => {
  getSheetData_().forEach((row) => {
    const status = sendMessage_({
      recipient_number: row['Phone Number'].replace(/[^\d]/g, ''),
      customer_name: row['Customer Name'],
      item_name: row['Item Name'],
      delivery_date: row['Delivery Date'],
    });
  });
};

Also see: Automate Workflows with Google Sheets

How to Play an MP3 File in Google Sheets

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 a button in your Google Sheet that, when clicked, will play the MP3 file in modal window.

Here’s a demo:

MP3 Player in Google Sheets

The audio files are hosted on Google Drive and when the Play button is clicked, the app will open a modal window with the audio player.

Add the Audio Player Button

To get started, create a new Google Sheet, go to the Insert menu and choose Create a New Drawing. Select Beveled Rectangle from the list of shapes, add some inline text and click Save to insert the button to your active Google Sheet.

Play Audio Button

Add the Player Script

Next, inside the Extension menu of Google Sheets, go to Script Editor and paste the following script.

const openAudioPlayer = () => {
  const cell = SpreadsheetApp.getActiveSheet().getActiveCell().getValue();
  const html = `<iframe src="${cell}" width="480" height="180" frameborder="0" scrolling="no"></iframe>`;
  const dialog = HtmlService.createHtmlOutput(html).setTitle('Play').setWidth(500).setHeight(200);
  SpreadsheetApp.getUi().showModelessDialog(dialog, 'Play Audio');
};

Switch to the Google Sheet you created, right click the Play button and assign the openAudioPlayer script to the button.

Audio Player in Google Sheets

Click OK to save your changes. Now play the URL of any MP3 file in any Google Sheet cell, click the Play button and the audio will play in a modal window.

Please ensure that the cell containing the audio file link is active when you click the Play button.

Also, if you are hosting the sound files in your Google Drive, the format of the link should be https://drive.google.com/file/d/<file-id>/preview.

How to Auto-Download Podcasts to Google Drive with Google Sheets

This tutorial describes how you can use Google Sheets to build your own podcast manager. You can specify a list of your favorite podcast shows in Google Sheets and it will automatically download new episodes to your Google Drive in neatly organized folders.

The setup is very simple, the app is completely open-source and you need no programming language.

How the Drive Podcast Manager Works?

You have to place the links of your favorite podcasts in column A of the Google Sheet as shown in the screenshot below.

Podcasts to Google Drive

The app will automatically download the latest episodes of each podcast to your Google Drive. You can open the MP3 files from your Google Drive or find them directly inside the same Google Sheet.

Google Sheets Podcast List

The app will create a new folder, titled Podcasts in your Google Drive. Inside this folder, it will create sub-folders for each podcast show with the folder name same as the title of the podcast.

Google Drive Podcasts Folder

Download Podcasts to Google Drive

Here’s how you can build your own podcast manager with Google Sheets and Google Drive.

  1. Click here to make a copy of the Google Sheet in your Google account.

  2. Open the copied spreadsheet, switch to the Subscriptions sheet and enter the RSS feed links of your favorite podcasts in column A. You may use our Apple Podcasts Lookup utility to find the RSS feed of any podcast that is listed on Apple Podcasts.

  3. Go to the Extensions menu and choose Script Editor to open the underlying Google Apps Script file.

  4. Choose the Install function from the list of functions and click Run to install the app. You may have to authorize the app once since it needs permission to save files to Google Drive on your behalf.

That’s it. The app will create a cron job that runs every few hours in the background and download the latest episodes of your favorite podcasts to your Google Drive.

We even have a built-in MP3 player embedded inside Google Sheets that will play the latest episode of each podcast when you click the Play button.

MP3 Player in Google Sheets

The Technical Details

If you are curious to know how the whole thing works, here’re the technical details.

The app uses the Spreadsheet API to read the list of podcasts from the Google Sheet. It then uses the XML service of Apps Script to parse the RSS feed and extract new podcast episodes that have been published since the last check.

All podcast RSS feeds are required to have an <item> tag with a <enclosure> tag inside. The <enclosure> tag contains the URL of the MP3 file and this is what the app uses to get the download URL of the corresponding episode.

const parseRSS = (xmlUrl, lastUpdatedTime) => {
  const feed = UrlFetchApp.fetch(xmlUrl).getContentText();
  const doc = XmlService.parse(feed);
  const root = doc.getRootElement();
  const channel = root.getChild('channel');
  const episodes = channel
    .getChildren('item')
    .map((item) => ({
      date: new Date(item.getChildText('pubDate')),
      title: item.getChildText('title'),
      enclosure: item.getChild('enclosure')?.getAttribute('url')?.getValue(),
    }))
    .filter(({ date }) => date > lastUpdatedTime)
    .filter(({ enclosure }) => enclosure);
  return { title: channel.getChildText('title'), episodes };
};

Once the app has a list of new episodes, it uses the UrlFetch service to download the podcasts and saves them to Google Drive in a folder specific to the podcast show.

The app then writes a new row to the Google Sheet with the link of the Google Drive file and a timestamp of when the episode was downloaded.

const getPodcastFolder = (folderName) => {
  const parentFolder = DriveApp.getFoldersByName('Podcasts').next();
  const folders = parentFolder.getFoldersByName(folderName);
  if (folders.hasNext()) return folders.next();
  return parentFolder.createFolder(folderName);
};

const downloadPodcast = (podcastTitle, episodeUrl, episodeTitle) => {
  try {
    const blob = UrlFetchApp.fetch(episodeUrl).getBlob();
    const folder = getPodcastFolder(podcastTitle);
    const file = folder.createFile(blob);
    SpreadsheetApp.getActiveSheet().appendRow([
      new Date(),
      `=HYPERLINK("${episodeUrl}";"${episodeTitle}")`,
      `https://drive.google.com/file/d/${file.getId()}/view`,
    ]);
  } catch (f) {
    console.error(f);
  }
};