UrTrix - UnRevealed Tricks

Compare MacBook Prices Worldwide with Google Sheets

2023-11-04T07:07:00.001-07:00

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.

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.

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

source:https://ift.tt/UlCDsqe

Conditional Email Notifications with Google Forms - Route Responses to Different Email Addresses

2023-11-04T01:07:00.001-07:00

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 Forms

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

source:https://ift.tt/xOJLngi

How to Save Password Protected PDF Attachments from Gmail to Google Drive

2023-11-02T04:09:00.001-07:00

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 Drive

We’ll use the Gmail to Google Drive add-on for Google Sheets to automatically download password-protected PDF attachments from Gmail to Google Drive.

1. Build the Gmail Search Query

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

2. Choose the Google Drive Folder

On 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 / / / so the PDF files would be saved in a folder structure like bank.com/2024/01/15.

3. Unencrypt PDF Attachments

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

source:https://ift.tt/C5y4PjS

How to Send Base64 Images in Email with Google Apps Script

2023-10-26T00:08:00.001-07:00

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

source:https://ift.tt/vgt0JoP

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

2023-10-25T02:08:00.001-07:00

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 is 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 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:

source:https://ift.tt/gLpSNlF

How to Mail Merge with Outlook and Google Sheets

2023-09-20T01:08:00.001-07:00

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 Outlook

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

Go 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.com
Port: 587
Username: Your Outlook email address
Password: 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 merge

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

source:https://ift.tt/WNCa1RI

How to Extract Images from Google Docs and Google Slides

2023-09-13T01:07:00.001-07:00

Imagine you’re working with a lengthy Google Document, or a Google Slides presentation, and you need to extract all the embedded images from the text and save them as individual files.

Extract Individual Images

A simple solution to address this issue is as follows: convert your Google Document or Google Slide into a web page. Here’s how you can do it:

Go to the “File” menu. Select the “Share” submenu and then choose “Publish to Web.” It will generate a public web page that contains all the images from your document or slide. You can simply right-click an image on the page and select the “Save Image” option download it to your local disk.

What we have just discussed is a manual process but we can easily automate this with the help of Google Apps Script.

Extract all Images from a Google Document

Open your Google Document containing the images, go to the Extensions menu and choose Apps Script. Copy-paste the code below and run the saveGoogleDocsImages function to download all images to a specific folder in your Google Drive.

The images are sequentially numbered and the file extension is the same as that of the embedded inline image.

function saveGoogleDocsImages() {
  // Define the folder name where the extracted images will be saved
  const folderName = 'Document Images';

  // Check if a folder with the specified name already exists
  const folders = DriveApp.getFoldersByName(folderName);

  // If the folder exists, use it; otherwise, create a new folder
  const folder = folders.hasNext() ? folders.next() : DriveApp.createFolder(folderName);

  // Get all the images in the document's body and loop through each image
  DocumentApp.getActiveDocument()
    .getBody()
    .getImages()
    .forEach((image, index) => {
      // Get the image data as a Blob
      const blob = image.getBlob();

      // Extract the file extension from the Blob's content type (e.g., 'jpeg', 'png')
      const [, fileExtension] = blob.getContentType().split('/');

      // Generate a unique file name for each image based on its position in the document
      const fileName = `Image #${index + 1}.${fileExtension}`;

      // Set the Blob's name to the generated file name
      blob.setName(fileName);

      // Create a new file in the specified folder with the image data
      folder.createFile(blob);

      // Log a message indicating that the image has been saved
      Logger.log(`Saved ${fileName}`);
    });
}

Extract all Images from Google Slides

The Apps Script code to download images from a Google Slides presentation is similar. The function iterates over the slides in the presentation and then for each slide, the function iterates over the images in that slide.

function extractImagesFromSlides() {
  // Define the folder name where the extracted images will be saved
  const folderName = 'Presentation Images';

  // Check if a folder with the specified name already exists
  const folders = DriveApp.getFoldersByName(folderName);

  // If the folder exists, use it; otherwise, create a new folder
  const folder = folders.hasNext() ? folders.next() : DriveApp.createFolder(folderName);

  // Iterate through each slide in the active presentation
  SlidesApp.getActivePresentation()
    .getSlides()
    .forEach((slide, slideNumber) => {
      // Retrieve all images on the current slide
      slide.getImages().forEach((image, index) => {
        // Get the image data as a Blob
        const blob = image.getBlob();

        // Extract the file extension from the Blob's content type (e.g., 'jpeg', 'png')
        const fileExtension = blob.getContentType().split('/')[1];

        const fileName = `Slide${slideNumber + 1}_Image${index + 1}.${fileExtension}`;

        // Set the Blob's name to the generated file name
        blob.setName(fileName);

        // Create a new file in the specified folder with the image data
        folder.createFile(blob);

        Logger.log(`Saved ${fileName}`);
      });
    });
}

source:https://ift.tt/x9BfLmy

How to Mention Someone in a Slack Message from Google Forms

2023-09-12T05:08:00.001-07:00

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

For this example, we have an event registration form created with Google Forms. When a participant completes the form and clicks the submit button, a message is instantly posted in the company’s Slack channel with details of the attendee like their name and email address.

The Google Form has a “Preferred Location” question as well and based on this answer, a specific member of the team will be tagged or mentioned in the Slack message. For instance, if the attended selects New York as the location in the form, the internal Slack message will tag @Angus since they takes care of New York registrations.

Send Slack Messages on Google Form Submit

Go to your Google Form and launch Document Studio. Create a new workflow and add a Slack task.

Connect your Google Account to your Slack account and you’ll be presented with a list of #channels available in the Slack workspace that you have linked with your account.

Select the relevant Slack channel where you wish to post messages and specify the message template that will be sent to Slack on new form submissions.

The template message includes variables like which will be replaced with the values from the form submission. It also includes a scriptlet to add conditional content in the Slack message.

The Scriptlet looks at the Location entered in the Google Form and, based on the answer, a specific user gets tagged in the Slack message. You can learn more about the syntax of Scriptlets here.

🎉 {{Attendee Name}} would attend the event from {{Location}}

{% assign answer = "" %}
{% if answer == "New York" %}
  <@U08N2HAQTF9>
{% elsif answer == "Boston" %}
  <@U05N6HAQZM1>
{% else %}
  <@U09PX8AQLJ1>
{% endif %}

How to @Mention a Slack User

Slack assigns a unique ID to each account and we put this ID in the Slack message to mention / tag that user in the message.

To obtain the User ID of a Slack account, follow these steps:

Open slack.com, navigate to your Slack channel and find any @mention of the user you’re interested in.
Right-click the @mention, and from the contextual menu, select “Copy link.”

The user link you’ve copied will have the following format:

labnol.slack.com/team/U05N6HAQZM1

In this link, the portion starting with U… represents the User ID of the Slack user.

If you want to @mention this user in your Slack message, enclose the User ID inside angle brackets and prefix it with the @ symbol, like this:

How are you <@UABC123> 🎉

source:https://ift.tt/9aXewHc

How to Use Cron Expressions to Create Time Triggers in Apps Script

2023-09-02T02:07:00.001-07:00

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

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 = () => {
  const cronExpression = '*/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.

source:https://ift.tt/5i4KbFj

How to Enable Duet AI in your Google Workspace

2023-09-01T02:08:00.001-07:00

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.

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.

source:https://ift.tt/d7lNRE1

Post Google Forms Responses to Discord Channels

2023-08-28T11:07:00.001-07:00

Discord is a popular instant messaging and group-chatting platform, used by millions of people. Notably, it has gained significant traction within colleges and gaming communities. This tutorial shows how you can automatically post Google Form responses to a specific Discord channel with the help of Document Studio.

Send Google Form Responses to Discord

Imagine a college Discord server with dedicated #tech and #science channels for different societies. Student volunteers can fill in a Google Form and indicate which society they would like be a part of. When the respondent submits the Google Form, an instant notification is sent to the respective Discord channel.

To get started, go to the Discord channel where the Google Form notifications should be delivered and click the Settings icon to copy the webhook URL for that channel.

Now that you have generated the Discord webhook URL, go to your Google Form and create a new workflow in Document Studio. On the Conditions tab, specify the condition Preferred Society Exactly Match Tech Society since we want to notify a different Discord channel for each society.

On the Tasks tab, add the Discord task and add the webhook URL that you’ve generated in the previous step. For the message body, you can use any question title from the Google Form and these will be replaced with actual users submitted by the Google Form respondent.

Save the workflow and you’ll automatically receive notifications in Discord when the form is submitted.

Post to Discord with Apps Script

If you are curious to know how these messages are posted to Discord from Google Forms, the answer is Google Apps Script.

The script will transform the Google Form response into a JSON message and then make an HTTP POST request to the webhook URL to post a message to the Discord channel.

Here’s a simplified snippet of the script:

const postMessageToDiscord = (formData) => {

  var discordUrl = 'https://discordapp.com/api/webhooks/labnol/123';

  var params = {
    headers: {
      'Content-Type': 'application/x-www-form-urlencoded',
    },
    method: 'POST',
    payload: JSON.stringify({ content: formData }),
    muteHttpExceptions: true,
  };

  var response = UrlFetchApp.fetch(discordUrl, params);

  Logger.log(response.getContentText());
}

Also see: Send Google Forms to Slack

source:https://ift.tt/c7eS0dQ

How to Enable Push Notifications for File Changes in Google Drive with Apps Script

2023-08-26T07:08:00.001-07:00

Are you looking for a way to receive notifications in real-time when an important spreadsheet in your Google Drive get modified or is accidently deleted by sometimes? Well, Google Drive offers an API to help you set up a watch on any file in your Google Drive be it a document, presentation or even a PDF file. This means that you can receive instant notifications whenever the content or even permissions of that file changes.

This tutorial explains how you can setup watch notifications on any file in your Google Drive with the help of Google Apps Script.

Setup a File Watch in Google Drive

To get started, type script.new in the browser to open the Apps Script editor and add the code below to create a watch. You’d need the unique ID of the Google Drive file and the webhook URL where the notifications would be send when the file gets modified.

const API_BASE_URL = 'https://www.googleapis.com/drive/v3';
const SUBSCRIPTION_DURATION_MS = 24 * 60 * 60 * 1000; // 24 hours in milliseconds

/**
 * Starts a subscription for receiving notifications about changes to a Google Drive file.
 *
 * @param {string} fileId - The ID of the file to watch for changes.
 * @param {string} webhookUrl - The URL where notifications will be sent.
 * @returns {void}
 */
const startSubscription = (fileId, webhookUrl) => {
  try {
    // Prepare the payload for the channel subscription
    const channelPayload = {
      id: Utilities.getUuid(), // Generate a unique ID for the channel
      address: webhookUrl,
      expiration: Date.now() + SUBSCRIPTION_DURATION_MS,
      type: 'web_hook',
      token: `fileId=${fileId}&source=labnol.org`,
    };

    // Construct the API endpoint URL for starting the subscription
    const endPoint = Utilities.formatString(`${API_BASE_URL}/files/%s/watch?supportsAllDrives=true`, fileId);

    // Call the Drive API to start the subscription
    const response = UrlFetchApp.fetch(endPoint, {
      method: 'POST',
      contentType: 'application/json',
      headers: {
        Authorization: `Bearer ${ScriptApp.getOAuthToken()}`,
      },
      payload: JSON.stringify(channelPayload), // Convert payload to JSON string
    });

    // Parse the response to extract subscription information
    const { id, resourceId } = JSON.parse(response);

    // Store subscription information in script properties
    const subscriptions = { id, resourceId, fileId, webhookUrl };
    PropertiesService.getScriptProperties().setProperty('subscriptions', JSON.stringify(subscriptions));
  } catch (error) {
    // Handle errors that might occur during subscription setup
    console.error(`Error starting subscription: ${error.message}`);
  }
};

Initialize Drive Watch Trigger

By default, a file watch expires in an hour. To extend this duration to 24 hours, we’ll use the SUBSCRIPTION_DURATION_MS variable. Please note that there’s no way to set up an indefinite watch. We’ll thus setup a time-based trigger in Apps Script to automatically renew the watch every 24 hours.

const initializeWatchApp = () => {
  const fileId = '<<Drive File Id>>';
  const webhookUrl = 'https://<<Webhook URL>>';

  startSubscription(fileId, webhookUrl);

  ScriptApp.getProjectTriggers().forEach((trigger) => {
    ScriptApp.deleteTrigger(trigger);
  });

  ScriptApp.newTrigger('triggerRenewSubscription').timeBased().everyHours(24).create();

  // Used to add the necessary Drive Scope
  const file = DriveApp.getFileById(fileId);
  console.log(`Push notifications activated for ${file.getName()}`);
};

Renew File Watch Automatically

The trigger functions manages the process of creating and renewing channel subscriptions for receiving notifications about changes to specific files in Google Drive. It leverages UrlFetchApp.fetch method instead of the Drive.Files.watch service since the latter uses the older version v2 of Google Drive API.

Since we do not want multiple notifications for the same file, we manually stop any existing subscriptions for a file before adding a new watch.

const triggerRenewSubscription = () => {
  try {
    // Retrieve subscription information from script properties
    const data = PropertiesService.getScriptProperties().getProperty('subscriptions');
    const subscriptions = JSON.parse(data);
    const { resourceId, id, fileId, webhookUrl } = subscriptions;

    // Stop the current subscription
    UrlFetchApp.fetch(`${API_BASE_URL}/channels/stop`, {
      method: 'POST',
      contentType: 'application/json',
      headers: {
        Authorization: `Bearer ${ScriptApp.getOAuthToken()}`,
      },
      payload: JSON.stringify({ id, resourceId }),
    });

    // Start a new subscription with the same details
    startSubscription(fileId, webhookUrl);

    console.log('Channel subscription renewed successfully!');
  } catch (error) {
    console.error(`Error renewing subscription: ${error.message}`);
  }
};

Handline watch notfications

You may use a web service like webhook.site or requestbin.com to test webhook notifications for file changes.

It is also possible to publish a Google Script as a web app to handle POST notifications from the Drive API but there’s a limitation - Apps Script cannot read the header of an incoming web require and Drive notifications include the data in the X-Goog-Channel-ID, X-Goog-Channel-Token and X-Goog-Resource-State headers of the request.

source:https://ift.tt/9K3PeA5

How to Create Personalized Images in Bulk with Google Sheets

2023-08-07T08:07:00.001-07:00

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 - - 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 in a graphic was replaced with actual values from the Google Sheet.

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.

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.

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.

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.

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!

source:https://ift.tt/ti7eHp2

Emojis in Google Sheets

2023-08-05T06:07:00.001-07:00

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.

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

source:https://ift.tt/W0i7gkR

How to Email Google Sheets Automatically on a Recurring Schedule

2023-07-12T08:07:00.001-07:00

The Email Spreadsheets add-on for Google Sheets can save office workers a ton of time by automating the reporting of spreadsheet data and dashboards by email. With this add-on, you can schedule reports to be sent automatically on a recurring schedule, so you don’t have to manually email spreadsheets to colleagues anymore.

With Email Spreadsheets, you can schedule reports and it will automatically send them by email on a recurring schedule. You can email entire workbooks, specific sheets inside a workbook or even range of cells. Watch the video to get started.

And because the add-on runs on the Google Cloud, your spreadsheet reports will be delivered even while you are offline or on vacation.

Email Google Sheets Automatically

For this example, our Google Spreadsheet has two sheets - the first sheet contains a data table and the second sheet contains an image chart with a neatly formatted table. We’ll build a scheduled workflow that will email the sheets data, including charts, on the first Monday of every week.

Step 1: Select Sheets to Export

Install the Email Google Sheets addon from Google marketplace. Next, open any Google Spreadsheet in your Google Drive, go to the Extensions menu inside the sheet, choose Email Spreadsheets from the dropdown. Click Open to launch the app and click the Create Workflow button to create your first scheduled email report.

You’ll be presented with a list of sheets available in the current workbook. Select one or more sheets that you would like to send with the scheduled email. You may export sheets in PDF, Excel, CSV or a PNG image. Each sheet is attached as a separate file in the email but you can choose the “Entire Workbook” option to create a single file from all sheets in the workbook.

You may also use dynamic markers to customize the file name of the exported files. For instance, the marker - will append the current date and month to the exported sheet name.

Tip: If your Google Sheet table is large, you can specify the cell range in A1 notation (like A1:G14) and only the specified range would be exported.

Step 2: Custom PDF Export Settings

The Email Google Sheets addon lets you customize the PDF layout that is exported from Google Sheets. You can change the paper orientation (Portrait or Landscape), the paper size or alter the print margins to fit more content on a page. You can choose to show gridlines, notes, sheet names and page numbers in the exported file.

Step 3: Write the Email Template

Next, we create an email template that will be sent with your reports. You can specify one or email recipients in the TO, CC, or BCC fields.

You can also specify dynamic email recipients based on cell values in the spreadsheet. For instance, if the email address of the recipient is specified in cell B2 of a sheet titled “Employee Shifts”, you can put in the To field, and the add-on will pull the dynamic value from the cell at the time of sending the email report.

These dynamic cell values enclosed inside double curly braces can be used inside any of the email fields including subject, email body, and the sender’s name.

The email body can include dynamic cell values as well as ranges that make it easy of you to send portions of the spreadsheet without sharing the full workbook. For instance, you can write to include only the specific range (B2:F9) from the Wages sheet. Internally, the add-on converts the range to an HTML table, retaining all the display formatting with CSS, and embed it into the email.

Charts and Timelines can be embedded into the email body using a special marker - you can find these markers inside the markers dropdown of the email editor. Business can also add their own logo and signature in the email body.

Tip: Use the Test Email button to send an email with the exported files before setting up the schedule.

Step 4: Setup the Email Schedule

The Email Google Sheets add-on includes an email scheduler to help you set up recurring schedules visually.

You can schedule and send emails hourly, daily, weekly, monthly or even on a yearly recurring basis. It is also possible to exclude dates and your spreadsheet won’t be emailed on the specified dates.

That’s it. Save the workflow and it will be activated instantly. You can also schedule multiple emails from the same Google Spreadsheet by adding more workflows.

The Email Spreadsheets add-on is a powerful tool that can help you automate the reporting of spreadsheet data and dashboards by email. To learn more about the Email Spreadsheets add-on and to download it, please visit the Google Workspace Marketplace.

Download Email Sheets

Email Google Sheets - How it works?

The add-on is written in Google Apps Script. It uses the Google Sheets API to convert sheets to PDF files and uses the Gmail API for sending the converted files as attachments.

source:https://ift.tt/LIqUFmb

Create a BMI Calculator using Google Forms and Google Sheets

2023-06-16T08:07:00.001-07:00

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

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

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.

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.

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.

source:https://ift.tt/d5gekGw

How to Transcribe Audio and Video Attachments in Gmail

2023-06-14T05:07:00.001-07:00

The Save Gmail to Google Drive add-on lets you automatically download email messages and file attachments from Gmail to your Google Drive. You can save the email messages as PDF while the attachments are saved in their original format.

Transcribe Gmail Attachments

The latest version of the Gmail add-on adds support for transcribing audio and video attachments in Gmail messages. The transcription is done with the help of OpenAI’s Whisper API and the transcript is saved as a new text file in your Google Drive.

Here’s a step by step guide on how you can transcribe audio and video attachments in Gmail messages to text.

Step 1. Install the Save Gmail to Google Drive add-on from the Google Workspace marketplace. Open sheets.new to create a new Google Sheet. Go to the Extension menu > Save Emails > Open App to launch the add-on.

Step 2. Create a new workflow and specify the Gmail search criteria. The add-on will scan the matching email message for any audio and video files.

OpenAI’s speech-to-text API supports a wide range of audio and video formats including MP3, WAV, MP4, MPEG, and WEBM. The maximum file size is 25 MB and you’ll always be in the limit since Gmail doesn’t allow you to send or receive files larger than 25 MB.

Step 3. On the next screen, check the option that says Save Audio and Video Attachments as text and choose the file format, text or PDF, in which you would like to save the transcript.

You can include markers in the file name. For instance, if you specify the file name as , the add-on will replace the markers with the actual sender’s email and the email subject.

You would also need to specify the OpenAI API key that you can get from the OpenAI dashboard. OpenAI charges you $0.006 per minute of audio or video transcribed, rounded to the nearest second.

Save the workflow and it will automatically run in the background, transcribing messages as they land in your inbox. You can check the status of the workflow in the Google Sheet itself.

Also see: Speech to Text with Dictation.io

Speech to Text with Google Apps Script

Internally, the add-on uses the Google Apps Script to connect to the OpenAI API and transcribe the audio and video files. Here’s the source code of the Google Script that you can copy and use in your own projects.

// Define the URL for the OpenAI audio transcription API
const WHISPER_API_URL = 'https://api.openai.com/v1/audio/transcriptions';
// Define your OpenAI API key
const OPENAI_API_KEY = 'sk-putyourownkeyhere';

// Define a function that takes an audio file ID and language as parameters
const transcribeAudio = (fileId, language) => {
  // Get the audio file as a blob using the Google Drive API
  const audioBlob = DriveApp.getFileById(fileId).getBlob();

  // Send a POST request to the OpenAI API with the audio file
  const response = UrlFetchApp.fetch(WHISPER_API_URL, {
    method: 'POST',
    headers: {
      Authorization: `Bearer ${OPENAI_API_KEY}`,
    },
    payload: {
      model: 'whisper-1',
      file: audioBlob,
      response_format: 'text',
      language: language,
    },
  });

  // Get the transcription from the API response and log it to the console
  const data = response.getContentText();
  Logger.log(data.trim());
};

Please replace the OPENAI_API_KEY value with your own OpenAI API key. Also, make sure that the audio or video file you want to transcribe is stored in your Google Drive and that you have at least view (read) permissions on the file.

Transcribe Large Audio and Video Files

The Whisper API only accepts audio files that are less than 25 MB in size. If you have a larger file, you can use the Pydub Python package to split the audio file into smaller chunks and then send them to the API for transcription.

If the video file is large in size, you may extract the audio track from the video file using FFmpeg and send that to the API for transcription.

# Extract the audio from video
ffmpeg -i video.mp4 -vn -ab 256 audio.mp3

## Split the audio file into smaller chunks
ffmpeg -i large_audio.mp3 -f segment -segment_time 60 -c copy output_%03d.mp3

FFmpeg will split the input audio file into multiple 60-second chunks, naming them as output_001.mp3, output_002.mp3, and so on, depending on the duration of the input file.

source:https://ift.tt/lKmUbwQ

How to Send a Welcome Email to Google Form Respondents

2023-06-07T05:07:00.001-07:00

“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 and would like to send a welcome email automatically to each person as they fill the form.

The email should be personalized and include the name of the attendee. We would like also attach a PDF file, with the speaker bios and other details about the event, to the email.

Google Form for Event Registration

Here’s a sample Google Form that we are using to collect registrations for the event. The form requires attendees to fill in their name, email address and their preferred date to attend the event.

Personalized Welcome Email

Since we would like to send a personalized email to each attendee, we’ll use markers in the email template that will be replaced with the actual values from the Google Form. For instance, if we use the marker in the email template, it will be replaced with the actual name of the attendee that is filled in the Google Form.

Here’s how the email template looks like:

Hi ,

Thank you for registering for the Tech and AI event. We are excited to have you join us on .

We've attached a PDF file with speaker bios and other details about the event.

Your registration ID is . If you would like to make any changes to your registration, please visit 

We look forward to seeing you there!

Sincerely,
Digital Inspiration

The Response ID is a unique identifier for each Google Form response and the Response URL is the link to the Google Form response that the attendee can use to edit their response anytime later.

PDF File with Event Details

Upload the PDF file that you would like to attach to the email to Google Drive. Share the file with anyone who has the link and copy the link of the PDF file to the clipboard.

Send Automated Emails from Google Forms

Now that we have the Google Form and templates ready, we’ll be using the Email Notifications for Google Forms add-on to send the welcome emails automatically to each attendee.

Launch the add-on inside Google Forms, and click the Create Email Notification button to create a new rule for sending emails.

Give your rule a name, say Welcome Email, and paste the Google Drive File URL in the Attach Files input box. Next, click the Visual Editor button to open the email editor.

Enter the subject line and the email body containing the markers. You can also add inline images (like your brand’s logo) to the email body.

Send Email to the Form Respondent

The last step is to specify the email address of the person who filled the Google Form. This is the email address that will receive the welcome email.

There are two ways to specify the email address of the form respondent:

You can either check the Notify Form Submitter option and select the Email Address question from the drop-down. This will send the welcome email to the email address that the respondent has entered in the Google Form.
Alternatively, you may put the question title of the email address field in the Email Addresses to Notify input box. This is useful when you want to send the welcome email to a different email address that is not entered in the Google Form.

Barcode and QR Code in the Email

Tip: If you are using the add-on to generate event passes, you can include the barcode or QR code of the registration ID in the email body. The barcode can be scanned at the event venue to verify the attendee’s identity. Learn more.

Test the Email Notification Rule

That’s it. Click the Save button to enable the rule and it will send the welcome email to the form respondent as soon as they submit the Google Form.

Here’s a sample email that we received after filling the Google Form.

Also see: Getting Started with Google Form Notifications

source:https://ift.tt/vwZN3qf

How to Sort Google Sheets Automatically with Apps Script

2023-05-12T03:08:00.001-07:00

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

source:https://ift.tt/VGnvb1p

Essential Mac Apps and Utilities - 2023 Edition

2023-05-06T01:07:00.001-07:00

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.

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.

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.

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.

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.

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

source:https://ift.tt/74wnvSI

Find Free Udemy Courses with Google Sheets and the Udemy API

2023-05-03T08:07:00.001-07:00

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.

source:https://ift.tt/idKo3yZ

New MX Records for Using Gmail with Google Workspace

2023-04-27T01:07:00.001-07:00

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:

source:https://ift.tt/EysHuoW

How to Use Conditional Logic in Google Documents

2023-04-26T06:10:00.001-07:00

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, ~ '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.

source:https://ift.tt/UcSwmLv

How to Make Phone Numbers Callable in Google Sheets and Docs

2023-04-05T05:08:00.001-07:00

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

source:https://ift.tt/tQWkOMC

Useful NPM Tips and Tricks for Developers to Boost Productivity

2023-03-31T03:07:00.001-07:00

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-test
npx emoj unicorn
npx public-ip-cli
npx 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/repo
npx 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=0
npm 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 outdated
npm 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 outdated
npm outdated -g
npm-check
npm-check -u
ncu -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 audit
npm 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.

source:https://ift.tt/JXOonvZ