DEV Community: Nibesh Khadka

How to Integrate ChatGpt Into Google Sheets With Apps Script?

Nibesh Khadka — Mon, 25 Sep 2023 00:19:30 +0000

Find the Last Row In Google Sheets With Google Apps Script!

Nibesh Khadka — Fri, 09 Jun 2023 18:15:00 +0000

Hello!, I wrote a blog on finding the last non-empty rows a couple of months ago. Since then, I have found a better, faster, and easier method, which works in most cases.

Find the Last Row & Column

You can copy and paste one of the following functions as per your needs.

FindLastRowNColBoundScript(), is meant for a bound script, is very easy to implement, and works on the currently active worksheet.
FindLastRowNColIndependentScript() should be used with an independent script. The given code is very simple. You can modify this function and pass variables such as ID and Worksheet names as parameters to make this function more dynamic.

/**
 * Function should be used for bound script.
 * Function use getDataRange().getValues() method to 
 * find lastRow and LastColumn of that Spreadhseet.
 */
function findLastRowNColBoundScript() {
  const sheet = SpreadsheetApp.getActiveSheet();

  const data = sheet.getDataRange().getValues();
  const lastRow = data.length;
  const lastCol = data[0].length;

  console.log(lastRow);
  console.log(lastCol);

  return [lastRow, lastCol];
}

/**
 * Function should be used for independent script.
 * Function use getDataRange().getValues() method to 
 * find lastRow and LastColumn of that Spreadhseet
 */
function findLastRowNColIndependentScript() {
  const ss = SpreadsheetApp.openById("<Your Spreadsheet's ID>");
  const sheet = ss.getSheetByName("<Name of the worksheet>");

  const data = sheet.getDataRange().getValues();
  const lastRow = data.length;
  const lastCol = data[0].length;

  console.log(lastRow);
  console.log(lastCol);

  return [lastRow, lastCol];
}


/**
 * This script is for finding last non empty rows and columns in a spreadsheet. 
 * This works in most cases but not in some cases such as columns with checkboxes
 * It is created in such a way that it only works with bound script.
 *
 * Created by: Nibesh Khadka.
 * linkedin: https://www.linkedin.com/in/nibesh-khadka/
 * website: https://nibeshkhadka.com
 */

This strategy of using DataRange() to find the last row and columns works in most cases but few such as columns filled with checkboxes on their entire rows.

Thank You for Your Time

My name is Nibesh Khadka, and as a freelance automation expert, I specialize in automating Google products with Apps Script. If you need my services let me know.

Don’t forget to like and share this blog.

How To Delete Empty Rows and Columns In Google Sheets?

Nibesh Khadka — Tue, 02 May 2023 20:15:00 +0000

Welcome to this very short daily blog where I give a very simple script to automate Google Products. In this chapter, we'll custom menu that'll delete empty rows and columns in current active spreadsheets.

Delete Empty/Extra Rows and Column

The following bound script will do three things:

Create a custom menu in your spreadsheets tabs with the title Custom Menu.
After you call to select the custom menu, It will check all the extra rows and columns after the last rows and columns with data.
Then delete all those extra rows and columns.



function deleteExteriorRowsNColumns() {

  // get sheets and data

  const sheet = SpreadsheetApp.getActiveSheet();

  const data = sheet.getDataRange().getValues();

// determine last row and column

  const lastRow = data.length;

  const lastCol = Math.max(...data.map((arr) => arr.length));

// get maximum rows and columns sheets

  const maxRows = sheet.getMaxRows();

  const maxCols = sheet.getMaxColumns();

// only remove rows and columns if there are empty rows or columns beyond last row and columns

  if (maxRows > lastRow) {

    sheet.deleteRows(lastRow + 1, maxRows - lastRow);

  }

  if (maxCols > lastCol) {

    sheet.deleteColumns(lastCol + 1, maxCols - lastCol);

}

}

/**


OnOpen trigger that creates menu
@param {Dictionary} e
*/
function onOpen(e) {
createCustomMenu();
}


/**


Menu creates menu UI in spreadsheet.
*/
function createCustomMenu() {
let menu = SpreadsheetApp.getUi().createMenu("Custom Menu"); 


menu.addItem("Delete Empty Rows and Columns", "deleteExteriorRowsNColumns");

  menu.addToUi();

}

How To Add Apps Script Code To a Spreadsheet?

If you don't know how to add this script to your sheet then, then just click the Extensions tab and then Apps Script as shown in the image below.

Now, similar to the previous blogs, you can now just:

Save the code.
Reload the document. Where you'll see the custom menu as shown below
And execute the function from the custom menu as shown in the image below.

Thank You for Your Time

My name is Nibesh Khadka, and as a freelance automation expert, I specialize in automating Google products with Apps Script. So let's get started! If you need my services let me know.

Don’t forget to like and share this blog.

Use This Script to Separate The First and Last Names in Spreadsheets?

Nibesh Khadka — Sat, 29 Apr 2023 20:15:00 +0000

Would you like to separate the column in your spreadsheet that consists of the full names of people into first and last names? Well then stick this very simple script to your spreadsheets apps script editor and you'll be able to do just that.

Split The Selected Column

The following bound script will do three things:

Create a custom menu in your spreadsheets tabs with the title Custom Menu.
After you call select the custom menu, It will check the cell you've selected to split into. The script won't work if you don't select a cell in the right column.
Separate the whole column into two columns, with the first value in the first column and the second(& rest if there are any) in the new column.

function splitName() {
  // get sheet and data
  const sheet = SpreadsheetApp.getActiveSheet();
  // get selected row
  const activeColIndex = sheet.getActiveRange().getColumn();

  const data = sheet.getDataRange().getValues();
  const lastRow = data.length;

  sheet.getRange(1,activeColIndex,lastRow,1).splitTextToColumns();

}

// if you're new and only want this feature the use this code
/**
 * OnOpen trigger that creates menu
 * @param {Dictionary} e
 */
function onOpen(e) {
  createCustomMenu();
}

/**
 * Menu creates menu UI in spreadsheet.
 */
function createCustomMenu() {
  let menu = SpreadsheetApp.getUi().createMenu("Custom Menu"); // Or DocumentApp or SlidesApp or FormApp.

  menu.addItem("Split Names", "splitName");
  menu.addToUi();
}

/**
 * Code By Nibesh Khadka.
 * I am freelance and Google Workspace Automation Expert.
 * You can find me in:
 * https://linkedin.com/in/nibesh-khadka
 * https://nibeshkhadka.com
 * me@nibeshkhadka.com
 */

How To Add Apps Script Code To a Spreadsheet?

If you don't know how to add this script to your sheet then, then just click the Extensions tab and then Apps Script as shown in the image below.

Now, similar to the previous blogs, you can now just:

Save the code.
Reload the document. Where you'll see the custom menu as shown below
And execute the function.

Executing The Function

Here are a few images to guide what the operation will look like in your docs.

Thank You for Your Time

My name is Nibesh Khadka, and as a freelance automation expert, I specialize in automating Google products with Apps Script. So let's get started! If you need my services let me know.

Don’t forget to like and share this blog.

How to Easily Copy a Column From One Sheet to Another in Google Sheets?

Nibesh Khadka — Wed, 26 Apr 2023 18:18:11 +0000

In this blog, I will be sharing a script that allows you to easily select a column in a Google Sheets spreadsheet and copy it to another spreadsheet while ensuring that the values are aligned in the same rows. Whether you're a Google Apps Script Developer or just someone looking to streamline their workflow, this script will be a helpful tool for you.

This is Nibesh Khadka. I am a freelance Google Apps Script Developer and Content Creator.

Referencing Columns Before Copying

Copying a column from one sheet to another can be challenging if we don't have a unique column as a reference to put the values in the correct column. We can use columns like ID, Email, or any other column that exists in both sheets as a reference. In this article, we'll create a spreadsheet with two sheets and use the "ID" column as a reference to copy the "Name" column from Sheet1 to Sheet2.

You can just download and use this sheet or create your own. You can either follow the steps or download the complete code from the git repository.

Script Editor

Now this is the bound script. So, open the spreadsheet you want to use for this project and open the script editor from there.

HTML Form

For this project, unlike in the previous one, we'll be using Custom Dialog Boxes with HTML instead of UI prompts because we'll need multiple input boxes for this project.

Create a new HTML file in your script editor. Then copy and paste the following codes

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <!-- <style>
    .targetColumnInfo {
      color: grey;
      font-size: 10px;
    }
  </style> -->
</head>

<body>
  <form onsubmit="formSubmitHandler(event)">
    <h2>Source Sheet</h2>
    <label for="sourceSheet">Source Sheet Name:</label><br>
    <input type="text" id="sourceSheet" name="sourceSheet"><br>
    <label for="referenceColumn">Reference Column Name</label><br>
    <input type="text" id="referenceColumn" name="referenceColumn"><br>
    <label for="sourceColumn">Source Column To Copy From:</label><br>
    <input type="text" id="sourceColumn" name="sourceColumn"><br>
    <hr>
    <h2>Target Sheet</h2><br>
    <label for="targetSheet">Target Sheet Name:</label><br>
    <input type="text" id="targetSheet" name="targetSheet"><br>
    <label for="targetReferenceColumn">Reference Column Name</label><br>
    <input type="text" id="targetReferenceColumn" name="targetReferenceColumn"><br>
    <label for="targetColumn">Target Column To Copy To:*</label><br>
    <input type="text" id="targetColumn" name="targetColumn"><br>
    <!-- <p class="targetColumnInfo">*Leave Last Input Empty to create new target column</p> -->

    <input type="submit" value="Submit" />

  </form>

  <script>
    function formSubmitHandler(event) {

      const sourceSheet = event.target['sourceSheet'].value;
      const referenceColumn = event.target['referenceColumn'].value;
      const sourceColumn = event.target['sourceColumn'].value;
      const targetSheet = event.target['targetSheet'].value;
      const targetReferenceColumn = event.target['targetReferenceColumn'].value;
      const targetColumn = event.target['targetColumn'].value ?? "";
      const data = [sourceSheet, referenceColumn, sourceColumn, targetSheet, targetReferenceColumn, targetColumn];

      // call server side function
     google.script.run.withSuccessHandler(closeDialog).setTargetColumns(data);

      event.preventDefault();

    }

    function closeDialog() { google.script.host.close() }
  </script>
</body>
</html>

There are two script runners to notice here.

withSuccessHandler() is used to link the function in the form in the front-end to the function in the backend(script) code during operation success, in this case, setTargetColumns() is the backend function we're using.
close() which is used to close the dialog box. We are using it with withSuccessHandler() to call the backend and then close the dialog box.

When rendered the Form looks like this:

Feel free to style the HTML Form to make it more beautiful.

In this HTML form you'll have to provide six pieces of information:

The name of the Source Worksheet.
The name of the Reference Column in the source sheet.
The name of the Source Column in the source sheet whose value is to be copied.
The name of the Target Worksheet where the value is to be copied.
The name of the Reference Column in the target sheet.
The name of the Target Column in the target sheet, that will hold the copied value.

Copy Column to Different Sheet

Now let's write the script to copy the values from one column to another.

function renderForm() {
  const html = HtmlService.createHtmlOutputFromFile('form')
    .setWidth(400)
    .setHeight(600);// play with dimension to fit your need
  SpreadsheetApp.getUi() 
    .showModalDialog(html, "Copy Column With Reference");
}

//["Sheet1", "ID", "Name", "Sheet2", "ID", "Name"]
function setTargetColumns(data) {
  try {
    const sourceSheetName = data[0];
    const referenceColumnName = data[1];
    const sourceColumnName = data[2];
    const targetSheetName = data[3];
    const targetReferenceColumnName = data[4];
    const targetColumnName = data[5];

    // get sheet and data
    const sheet = SpreadsheetApp.getActiveSpreadsheet();
    const sourceSheet = sheet.getSheetByName(sourceSheetName);

    const sourceSheetData = sourceSheet.getDataRange().getValues();
    const sourceSheetLastRow = sourceSheetData.length;
    const sourceSheetLastColumn = sourceSheetData[0].length;
    // get header column of source sheet
    const sourceSheetHeader = sourceSheet.getRange(1, 1, 1, sourceSheetLastColumn).getValues().flat();

    // find the index of the given column names
    const referenceColumnIndex = sourceSheetHeader.indexOf(referenceColumnName.trim());
    if (referenceColumnIndex === -1) throw "Reference Column Not Found";

    const sourceColumnIndex = sourceSheetHeader.indexOf(sourceColumnName.trim());
    if (sourceColumnIndex === -1) throw "Source Column Not Found"; // if the name is not found then throw error

    const sourceSheetData2 = [sourceSheet.getRange(2, referenceColumnIndex + 1, sourceSheetLastRow, 1).getValues().flat(), sourceSheet.getRange(2, sourceColumnIndex + 1, sourceSheetLastRow, 1).getValues().flat()]


    const targetSheet = sheet.getSheetByName(targetSheetName);
    const targetSheetData = targetSheet.getDataRange().getValues();
    const targetSheetLastRow = targetSheetData.length;


    const targetReferenceColumnIndex = sourceSheetHeader.indexOf(targetReferenceColumnName.trim());
    if (targetReferenceColumnIndex === -1) throw "Target Sheets Reference Column Not Found";

    const targetColumnIndex = sourceSheetHeader.indexOf(targetColumnName.trim());
    if (targetColumnIndex === -1) throw "Target Sheet's Target Column Not Found";

    const targetSheetRefData = targetSheet.getRange(2, targetReferenceColumnIndex + 1, targetSheetLastRow - 1, 1).getValues().flat();
    const targetSheetColData = targetSheet.getRange(2, targetColumnIndex + 1, targetSheetLastRow - 1, 1).getValues();


    for (let i = 0; i < sourceSheetData2[0].length; i++) {
      for (let j = 0; j < targetSheetRefData.length; j++) {
        if (targetSheetRefData[j] === sourceSheetData2[0][i]) {
          targetSheetColData[j] = [sourceSheetData2[1][i]];
          break;
        }
        continue;
      }
    }
    // set new values 
    targetSheet.getRange(2, targetColumnIndex + 1, targetSheetLastRow - 1, 1).setValues(targetSheetColData);

  } catch (e) {
    // alert error
    SpreadsheetApp.getUi().alert(`Error: ${e}`)
  }
}


/**
 * Menu creates menu UI in the document it's bound to.
 */
function createCustomMenu() {
  const menu = SpreadsheetApp.getUi().createMenu("Copy Columns");

  menu.addItem("Copy Column", "renderForm");
  menu.addToUi();
}


/**
 * OnOpen trigger that creates menu
 * @param {Dictionary} e
 */
function onOpen(e) {
  createCustomMenu();
}

/**
 * This script is for safely and accurately copying one column to 
 * another column in two sheets while properly aliging with other
 * rows.
 * It is created in such a way that it only works with bound script.
 *
 * Created by: Nibesh Khadka.
 * linkedin: https://www.linkedin.com/in/nibesh-khadka/
 * website: https://nibeshkhadka.com
 */

Now, similar to the previous blogs, you can now just:

Save the code.
Reload the spreadsheet. Where you'll see the custom menu as shown below
And execute the function.

After this, you'll see a menu like the image below in the spreadsheet.

Error

In case you leave any input box empty or you gave the wrong names to the boxes, you'll see the error alert like the image below.

Thank You for Your Time

My name is Nibesh Khadka, and as a freelance automation expert, I specialize in automating Google products with Apps Script. So let's get started! If you need my services let me know.

Don’t forget to like and share this blog.

How to check word count in Google Docs?

Nibesh Khadka — Tue, 25 Apr 2023 06:58:53 +0000

Are you tired of manually counting the number of words in your Google Docs while working on your blog or article? Well, fret not, as I have got you covered with a simple yet effective solution. In this tutorial series on Apps Script, I will walk you through a step-by-step guide on how to create a script that can read your Google Docs and return the exact word count of your document, making your life easier and your writing process more efficient. So, let's dive in and learn how to see how many words you have on Google Docs!

Welcome to the apps script tutorial series, where I, Nibesh Khadka, provide short and sweet scripts and teach a few things along the way to make our life less miserable.

Google Docs Word Counter Script

The given script will do just exactly what was promised earlier.

/**
 * Word Counter is A simple script to count number of words written in a google docs with apps script.
 */
function wordCounter() {
  let doc = DocumentApp.getActiveDocument();
  let data = doc.getText();

  let processedData = data.split("\n").filter(String).map(item => item.split(" ")).flat().filter(String);

  let wordCount = processedData.length;
  // now alet the word count
  DocumentApp.getUi().alert(`You have ${wordCount} words in this document.`)
}


/**
 * Menu creates menu UI in the document it's bound to.
 */
function createCustomMenu() {
  let menu = DocumentApp.getUi().createMenu("Count Number of Words"); // Or DocumentApp or SlidesApp or FormApp.

  menu.addItem("Word Counter", "wordCounter");
  menu.addToUi();
}


/**
 * OnOpen trigger that creates menu
 * @param {Dictionary} e
 */
function onOpen(e) {
  createCustomMenu();
}

/**
 * Word Counter is a script to count number of words written in Google Docs with Google Apps Script. 
 * It is created in such a way that it only works with bound script.
 * 
 * Created by: Nibesh Khadka.
 * linkedin: https://www.linkedin.com/in/nibesh-khadka/
 * website: https://nibeshkhadka.com
 */

How to use Google Apps Script For Non-Coders?

This is a container-bound script i.e the script has to be attached to a file, in this case, a Google Docs. So, first, you'll have to open the document in which you'd want to count the number of words.

There just click the Extensions tab and then Apps Script as shown in the image below.

There, on code.gs file, just replace those codes with the codes above.

Now, similar to the previous blogs, you can now just:

Save the code.
Reload the document. Where you'll see the custom menu as shown below
And execute the function.

Executing The Function

Here are a few images to guide what the operation will look like in your docs.

Thank You for Your Time

My name is Nibesh Khadka, and as a freelance automation expert, I specialize in automating Google products with Apps Script. So let's get started! If you need my services let me know.

Don’t forget to like and share this blog.

How to Insert Multiple Rows in Google Sheets with Apps Script?

Nibesh Khadka — Mon, 24 Apr 2023 10:35:17 +0000

Hello and welcome to our blog series on automating Google Sheets with Apps Script. In this tutorial, we will guide you on how to insert multiple rows in Google Sheets using a simple script. We'll explore how to use UiApp to create a custom user interface for inserting rows in Google Sheets and how to add dynamic row insertion features to the menu.

Whether you are a beginner or an experienced user, this guide will provide you with step-by-step instructions to help you streamline your workflow and save time.

Adding Dynamic Row Insertion Features with UiApp in Google Sheet

Google Sheets is a powerful tool for managing data, but sometimes users need to insert multiple rows at once. To make this process more efficient, we can use the UiApp class from Apps Script. With UiApp, we can create a simple user interface that allows the user to specify the number of rows they want to insert. However, UiApp has its limitations, such as not being able to create dynamic input for inserting rows above or below. To overcome this limitation, we can add two functions to the menu that will insert rows above or below after asking for the number of rows to enter.

Writing Functions to Insert Multiple Rows in Google Sheets

Now, on your spreadsheet, click the Extensions tab and then Apps Script as shown in the image below.

If you're continuing on the previous blog, then create a new script file with a preferred name, I'll call it insert_multiple_rows.

There copy and paste the following codes.

function insertMultipleRowsAbove() {
  // get sheet and data
  let sheet = SpreadsheetApp.getActiveSheet();
  // get selected row
  let activeRowIndex = sheet.getActiveRange().getRowIndex();
  // get ui
  let ui = SpreadsheetApp.getUi();
  // ask for number of rows to insert
  let response = ui.prompt(`Selected row: ${activeRowIndex}.

  Enter the number of rows you want to insert above the selected row:`);

  // Process the user's response.
  if (response.getSelectedButton() == ui.Button.OK) {
    let numRows = Number(response.getResponseText());
    sheet.insertRowsBefore(activeRowIndex, numRows);
    ui.alert(`${numRows} has been inserted above the ${activeRowIndex} row.`);

  } else {
    ui.alert(`Please press the ok button after you enter the number.`);
  }

}

function insertMultipleRowsBelow() {
  // get sheet and data
  let sheet = SpreadsheetApp.getActiveSheet();
  // get selected row
  let activeRowIndex = sheet.getActiveRange().getRowIndex();
  // get ui
  let ui = SpreadsheetApp.getUi();
  // ask for number of rows to insert
  let response = ui.prompt(`Selected row: ${activeRowIndex}.

  Enter the number of rows you want to insert below the selected row:`);

  // Process the user's response.
  if (response.getSelectedButton() == ui.Button.OK) {
    let numRows = Number(response.getResponseText());
    sheet.insertRowsAfter(activeRowIndex, numRows);
    ui.alert(`${numRows} has been inserted after the ${activeRowIndex} row.`);

  } else {
    ui.alert(`Please press the ok button after you enter the number.`);
  }

}

Here, we created two functions - insertMultipleRowsAbove() and insertMultipleRowsBelow(). Both functions perform almost the same task of asking the user for the number of rows to insert and then inserting them either above or below the selected row index. The only difference between the two functions is the method used to insert the rows - insertRowsBefore() and insertRowsAfter() respectively. These functions will come in handy when you need to insert multiple rows at once, saving you time and effort.

Adding the Insert Multiple Rows Function to the Custom Menu

Now we'll add these two functions to our menu.

Again if you're continuing to the previous blog, then locate the createCustomMenu() function in your files and edit the codes there. And only add one of the following functions in your files per your needs.

// if you're new and only want this feature the use this code
/**
 * OnOpen trigger that creates menu
 * @param {Dictionary} e
 */
function onOpen(e) {
  createCustomMenu();
}

/**
 * Menu creates menu UI in spreadsheet.
 */
function createCustomMenu() {
  let menu = SpreadsheetApp.getUi().createMenu("Insert Multiple Rows"); // Or DocumentApp or SlidesApp or FormApp.

  menu.addItem("Insert Multiple Rows Above", "insertMultipleRowsAbove");
  menu.addItem("Insert Multiple Rows Below", "insertMultipleRowsBelow");
  menu.addToUi();
}
//======================================================//
// if you're continuing the previous blog then add this

/**
 * Menu creates menu UI in spreadsheet.
 */
function createCustomMenu() {
  let menu = SpreadsheetApp.getUi().createMenu("Highlight Duplicate Rows"); // Or DocumentApp or SlidesApp or FormApp.

  menu.addItem("Highlight Duplicate Row", "colorDuplicateRows");
  menu.addItem("Delete Duplicate Row", "deleteDuplicateRows");
  menu.addItem("Reset Colors", "colorReset");
  menu.addItem("Insert Multiple Rows Above", "insertMultipleRowsAbove");
  menu.addItem("Insert Multiple Rows Below", "insertMultipleRowsBelow");
  menu.addToUi();
}

From here, similar to the previous blogs, you can now just:

Save the code.
Reload the spreadsheet.
And execute the function.

You'll see the respective menus and titles depending on the codes you choose above.

Executing The Function

Here are a few images to guide what the operation will look like in your sheet.

Thank You for Your Time

My name is Nibesh Khadka, and as a freelance automation expert, I specialize in automating Google products with Apps Script. So let's get started! If you need my services let me know.

Don’t forget to like and share this blog.

Removing Duplicates in Google Sheets: A Guide for Non-coders

Nibesh Khadka — Thu, 16 Mar 2023 06:35:24 +0000

Ugh.., I have so many duplicate rows in my spreadsheet. Well so do many other people. With the script in this article, you can introduce a menu button that lets you delete duplicate rows in your spreadsheet.

This blog is the second part of the blog I published a few days ago: “How to Highlight Duplicate Rows in Google Sheets?”. There I provided a simple script to highlight duplicate rows. This script will extend that script to delete those duplicate rows.

For Non-Coders

If you’re not a coder and don’t wanna be bothered with an explanation then follow this step:

Open Script editor. On your spreadsheet click the Extensions tab and then Apps Script as shown in the image above.
Remove all the codes in Code.gs. Then copy and paste the code from the Full Code section in this blog.
Save and then reload the spreadsheet. Now, reopen the Apps script as instructed in step 1 (Sometimes the script doesn't work without saving and reloading).

If you’re already using the previous script then you can just copy and paste this script over the previous one.

Full Code

Here’s the full script for this blog including the previous blog’s code.


/**
 *The colorDuplicateRows() colors the duplicate rows.
 *Each unique rows and its duplicates are highlighted with different color
 */
function colorDuplicateRows() {
  // get sheet and data
  let sheet = SpreadsheetApp.getActiveSheet();
  let data = sheet.getDataRange().getValues();

  // find lastCol
  let lastCol = Math.max(...data.map((arr) => arr.length));

  // convert nested arrays to string
  data = data.map((arr) => arr.join(""));
  // find unique ones among arrays
  let uniqueRows = [...new Set(data)].filter(String);
  // create unique color for each unique arr
  let uniqueColor = {};
  uniqueRows.forEach((val) => (uniqueColor[val] = getRandomUniqueColor()));

  // console.log(uniqueColor);
  // find duplicate row for each item
  let duplicateRows = data
    .map((x, i) => (isDuplicateRow(x, data) ? i + 1 : ""))
    .filter(String);
  // console.log(duplicateRows);

  // now reset color before highlighting duplicate rows
  colorReset();

  duplicateRows.forEach((rowNum) => {
    for (let i = 0; i < uniqueRows.length; i++) {
      //console.log(range.getValues())
      // compare each item with uniqe items and assing color accordingly
      if (
        sheet.getRange(rowNum, 1, 1, lastCol).getValues().flat().join("") ===
        uniqueRows[i]
      ) {
        sheet
          .getRange(rowNum, 1, 1, lastCol)
          .setBackground(uniqueColor[uniqueRows[i]]);
      }
    }
  });
}

/**
 * Function takes two items: row and arr.
 * The parameter "row" is a string to be compared to items in array "arr".
 * Inspired from https://stackoverflow.com/a/68424642/6163929
 * @param {String} row
 * @param {Array<String>} arr
 * @returns {Boolean}
 */
function isDuplicateRow(row, arr) {
  return row === "" ? false : arr.indexOf(row) != arr.lastIndexOf(row);
}

/**
 * Menu creates menu UI in spreadsheet.
 */
function createCustomMenu() {
  let menu = SpreadsheetApp.getUi().createMenu("Highlight Duplicate Rows"); // Or DocumentApp or SlidesApp or FormApp.

  menu.addItem("Highlight Duplicate Row", "colorDuplicateRows");
  menu.addItem("Delete Duplicate Row", "deleteDuplicateRows");
  menu.addItem("Reset Colors", "colorReset");
  menu.addToUi();
}

/**
 * OnOpen trigger that creates menu
 * @param {Dictionary} e
 */
function onOpen(e) {
  createCustomMenu();
}

/**
 * ColorReset is used to reset bg color of spreadsheet to its original color.
 */
function colorReset() {
  let sheet = SpreadsheetApp.getActiveSheet();
  sheet.getDataRange().setBackground("");
}

/**
 * Function creates a unique random color as hashcode.
 * @returns {String}
 */
function getRandomUniqueColor() {
  // thanks to function https://dev.to/rajnishkatharotiya/generate-unique-color-code-in-javascript-3h06
  let n = (Math.random() * 0xfffff * 1000000).toString(16);
  return "#" + n.slice(0, 6);
}


/**
 *The deleteDuplicateRows() funciton deleted the duplicate 
 *rows in current active spreadsheet.
 */
function deleteDuplicateRows() {
  // get sheet and data
  let sheet = SpreadsheetApp.getActiveSheet();
  let data = sheet.getDataRange().getValues();



  // convert nested arrays to string
  data = data.map((arr) => arr.join(";"));
  // find unique ones among arrays
  let uniqueRows = [...new Set(data)].filter(String);
  // convert the values from string to qrray again
  let newData = uniqueRows.map(itemStr => itemStr.split(";"));

  // find last row and col for new range.
  let newLastRow = newData.length;
  let newLastCol = Math.max(...newData.map((arr) => arr.length));

  // reset previous colors if the highlight function is used
  colorReset();


  // clear previous contents to save only unique values
  sheet.clearContents();
  // set new values
  sheet.getRange(1, 1, newLastRow, newLastCol).setValues(newData);

}


/**
 * Code By Nibesh Khadka.
 * I am freelance and Google Workspace Automation Expert.
 * You can find me in:
 * https://linkedin.com/in/nibesh-khadka
 * https://nibeshkhadka.com
 * me@nibeshkhadka.com
 */

deleteDuplicateRows() is the new function added to the script of the previous blog. Also, the menu item is extended to add the function.

Similar to the previous blog, you can now just:

Save the code.
Reload the spreadsheet.
And execute the function.

You’ll able to see the menu with the title “Highlight Duplicate Rows“.

Now, you can check duplicate rows in your spreadsheet and delete them if you want with ease.

Thank You for Your Time

I make Google Add-Ons and can also write Google Apps Scripts for you. If you need my services let me know.

Don’t forget to like and share this blog.

Using Apps Script to Count Checkboxes in Google Sheets

Nibesh Khadka — Thu, 09 Mar 2023 14:52:32 +0000

You have checkboxes in your spreadsheet, you’ll like to count them easily. Well, this script I’ll provide will help you with exactly that. Non-coders can easily follow instructions and add this script to their projects.

Namaste! This is Nibesh Khadka. I am a freelancer that develops scripts to automate Google Workspace Apps like Gmail, Google Sheets, Google Docs, etc.

For Non-Coders

If you’re not a coder and don’t wanna be bothered with a tedious explanation then you can just follow these steps:

Open Script editor. On your spreadsheet click the extensions tab and open the apps script as shown in the image above.
Remove all the code in code.gs. Then, copy and paste the code from the Full Code section in this blog.
Save and then reload the spreadsheet. Now, reopen the Apps script as instructed in step 1 (Sometimes the script doesn't work without saving and reloading).

After this, you just have to make a minor edit as instructed below.

Full Code

Here’s the full script for this blog:

// make minor change here.
// insert the number equivalent of your column with checkboxes here inside square boxes, like [1,2]
// for instance 1 for A, 5 for E, and so on.
const checkBoxColNumber = [5, 8];

/**
 * The function creates a custom menu in spreadsheet
 */
function menu() {

  SpreadsheetApp.getUi()
    .createMenu("Count Checkboxes")
    .addItem("Count Checkboxes", "countCheckbox")
    .addToUi();

}

/**
 * OnOpen is resposbile to insert menu to spreadsheet.
 */
function onOpen(e) {
  menu();
}



/**
 * CountCheckbox goes through the active sheet and counts the number of checked and unchecked box among non-empty rows
 */
function countCheckbox() {

  // get sheet and data
  let sheet = SpreadsheetApp.getActiveSheet();
  let data = sheet.getDataRange().getValues();
  // remove first header column
  data.shift();

  // initate counts as zero
  let trueCount = 0;
  let falseCount = 0;

  // value count will be a nested list but as same length as Counting Checkboxes
  let checkBoxColValueCounts = [...checkBoxColNumber];

  // loop through checkBoxColNumber list and data list 
  for (let j = 0; j < checkBoxColNumber.length; j++) {
    for (let i = 0; i < data.length; i++) {
      // [...new Set(data[i])].filter(String) check to get unique value if its either just true or false
      if ([...new Set(data[i])].filter(String)[0] === true || [...new Set(data[i])].filter(String)[0] === false || data[i].join("") === "") { continue; }
      // if values is true push increase true count else false count
      if (data[i][checkBoxColNumber[j] - 1] === true) {
        trueCount += 1;

      } else {
        falseCount += 1;

      }
    }

    // add true and false count as nested list in respective place
    checkBoxColValueCounts[j] = [trueCount, falseCount];
    // reset 
    //console.log(checkBoxColValueCounts)
    trueCount = 0;
    falseCount = 0;
  }

  // create alert string with all info embeded
  let countStatsString = "";
  for (let j = 0; j < checkBoxColValueCounts.length; j++) {
    countStatsString += `\n In the ${checkBoxColNumber[j]} column, There are ${checkBoxColValueCounts[j][0]} boxes checkd and ${checkBoxColValueCounts[j][1]} unchecked boxes among non-empty rows.`
  }
  // alert the result
  SpreadsheetApp.getUi().alert(countStatsString);
}

Using Script in Your Spread Sheet

For this script to work in your spreadsheet you’re gonna have to make a tiny change in code.

// make minor change here.
// insert the number equivalent of your column with checkboxes here inside square boxes, like [1,2]
// for instance 1 for A, 5 for E, and so on.
const checkBoxColNumber = [5, 8];

See this code in the beginning, you’ll have to replace [5, 8] these values with the number equivalent of the letters representing the columns with the checkbox values in your spreadsheet. For instance, 5 for E and 8 for H. If it’s just one column then just insert one value inside the square box without the comma like [5].

Navigating the Menu

After this, you should save the script and reload your spreadsheet you should be able to see the menu in your spreadsheet, with the title “Count Checkboxes“ similar to the image below.

When you run this function you’ll see an alert box with the sum of all checked and unchecked boxes for non-empty rows in your spreadsheet.

Thank You for Your Time

If you would like me to write short and quick blogs like these then leave requests in the comments.

I make Add-Ons for Google and can also write Google Apps Scripts for you. If you need my services let me know.

How to Highlight Duplicate Rows in Google Sheets?

Nibesh Khadka — Wed, 01 Mar 2023 07:13:57 +0000

You use sheets a lot and sometimes have difficulty identifying duplicate rows. Then after this blog, you’ll be equipped with a script that’ll highlight duplicate rows in your sheet.

Namaste! This is Nibesh Khadka. I am a freelancer that develops scripts to automate Google Workspace Apps like Gmail, Google Sheets, Google Docs, etc.

For Non-Coders

If you’re not a coder and don’t wanna be bothered with an explanation then follow this step:

Open Script editor. On your spreadsheet click the extensions tab and open the apps script as shown in the image above.
Remove all the codes in Code.gs. Then copy and paste the code from the Full Code section in this blog.
Save and then reload the spreadsheet.Now, reopen the Apps script as instructed in step 1 (Sometimes the script doesn't work without saving and reloading).

Full Code

Here’s the full script for this blog.

/**
 * Function finds and highlights duplicate rows with unique colors
 */
function colorDuplicateRows() {
  // get sheets and data
  const ss = SpreadsheetApp.getActiveSheet();
  let data = ss.getDataRange().getValues();

  // get last col
  const lastCol = data[0].length;

  // convert rows in data to strings for easy comparision
  data = data.map(row => row.join(""));

  // extract unique rows
  const uniqueRows = [... new Set(data)].filter(String);// also remove empty string if there were empty rows

  // unique color object to hold color for each unique row
  const uniqueColor = {};
  // fill the values;
  uniqueRows.forEach(row => uniqueColor[row] = getRandomUniqueColor());

  // find duplicate rows indexes in the data
  const duplicateRowsIndexes = data.map((row, ind) => isDuplicateRow(row, data) ? ind + 1 : "").filter(String);

  // reset color to default before applying new colors
  colorReset();

  // highlight duplicate rows
  duplicateRowsIndexes.forEach(rowInd => {
    const range = ss.getRange(rowInd, 1, 1, lastCol) // get range
    // highlight
    range.setBackground(uniqueColor[range.getValues().flat().join("")])
  })

}

/**
 * Function takes two items: row and arr.
 * The parameter "row" is a string to be compared to items in array "arr".
 * Inspired from https://stackoverflow.com/a/68424642/6163929
 * @param {String} row
 * @param {Array<String>} arr
 * @returns {Boolean}
 */
function isDuplicateRow(row, arr) {
  return row === "" ? false : arr.indexOf(row) != arr.lastIndexOf(row);
}

/**
 * Menu creates menu UI in spreadsheet.
 */
function createCustomMenu() {
  let menu = SpreadsheetApp.getUi().createMenu("Highlight Duplicate Rows"); 

  menu.addItem("Highlight Duplicate Row", "colorDuplicateRows");
  menu.addItem("Reset Colors", "colorReset");
  menu.addToUi();
}

/**
 * OnOpen trigger that creates menu
 * @param {Dictionary} e
 */
function onOpen(e) {
  createCustomMenu();
}

/**
 * ColorReset is used to reset bg color of spreadsheet to its original color.
 */
function colorReset() {
  let sheet = SpreadsheetApp.getActiveSheet();
  sheet.getDataRange().setBackground("");
}

/**
 * Function creates a unique random color as hashcode.
 * @returns {String}
 */
function getRandomUniqueColor() {
  // thanks to function https://dev.to/rajnishkatharotiya/generate-unique-color-code-in-javascript-3h06
  let n = (Math.random() * 0xfffff * 1000000).toString(16);
  return "#" + n.slice(0, 6);
}

/**
 * Code By Nibesh Khadka.
 * I am freelance and Google Workspace Automation Expert.
 * You can find me in:
 * https://linkedin.com/in/nibesh-khadka
 * https://nibeshkhadka.com
 * me@nibeshkhadka.com
 */

Is The Row Duplicate?

To find if a row is duplicate or not we’re using isDuplicateRow() function in our script.

function isDuplicateRow(x, arr) {
  return x === "" ? false : arr.indexOf(x) != arr.lastIndexOf(x);
}

This function takes two parameters: a string and an array. The string is a row converted to a string and an array is a list of rows each converted to a string.

Reset Color and Random Unique Color

To highlight duplicate rows we’ll have to do two things.

The first is to reset previous highlights(if they exist) with the colorReset() function.
Then color them with colors that are unique to each set of duplicate rows, with the getRandomUniqueColor() function.

function colorReset() {
  let sheet = SpreadsheetApp.getActiveSheet();
  sheet.getDataRange().setBackground("");
}


function getRandomUniqueColor() {
  // thanks to function https://dev.to/rajnishkatharotiya/generate-unique-color-code-in-javascript-3h06
  let n = (Math.random() * 0xfffff * 1000000).toString(16);
  return "#" + n.slice(0, 6);
}

You should know that the colors will be different each time you run the function since they are random. In real-world use cases, it is difficult to hardcode colors because the number of sets of duplicate rows can vary.

Lets Color Duplicate Rows

Now that all the helper functions are ready let’s put them all in a proper order to color the duplicate rows in our sheet with our colorDuplicateRows() function.

function colorDuplicateRows() {
  // get sheets and data
  const ss = SpreadsheetApp.getActiveSheet();
  let data = ss.getDataRange().getValues();

  // get last col
  const lastCol = data[0].length;

  // convert rows in data to strings for easy comparision
  data = data.map(row => row.join(""));

  // extract unique rows
  const uniqueRows = [... new Set(data)].filter(String);// also remove empty string if there were empty rows

  // unique color object to hold color for each unique row
  const uniqueColor = {};
  // fill the values;
  uniqueRows.forEach(row => uniqueColor[row] = getRandomUniqueColor());

  // find duplicate rows indexes in the data
  const duplicateRowsIndexes = data.map((row, ind) => isDuplicateRow(row, data) ? ind + 1 : "").filter(String);

  // reset color to default before applying new colors
  colorReset();

  // highlight duplicate rows
  duplicateRowsIndexes.forEach(rowInd => {
    const range = ss.getRange(rowInd, 1, 1, lastCol) // get range
    // highlight
    range.setBackground(uniqueColor[range.getValues().flat().join("")])
  })

}

Ya.. Ok, But where’s the menu?

Our function is ready it will work every time you run the function from the script(if not follow step 3 from the non-coders section) but we want to run this function from our sheet.

So, let’s create our custom menu with the createCustomMenu() function.

function createCustomMenu() {
  let menu = SpreadsheetApp.getUi().createMenu("Highlight Duplicate Rows");

  menu.addItem("Highlight Duplicate Row", "colorDuplicateRows");
  menu.addItem("Reset Colors", "colorReset");
  menu.addToUi();
}

Then, render the custom menu to the spreadsheet with OnOpen() trigger.


function onOpen(e) {
  createCustomMenu();
}

Save the code and reload the spreadsheet then you’ll able to see the menu with the title “Highlight Duplicate Rows“.

Thank You for Your Time

I make Google Add-Ons and can also write Google Apps Scripts for you. If you need my services let me know.

Find and Delete Duplicate Files With In Google Drive With Google Apps Script

Nibesh Khadka — Wed, 22 Feb 2023 09:33:25 +0000

Liquid syntax error: Unknown tag 'endraw'

How to Find the Last Non-Empty Row in A Column With Google Apps Script?

Nibesh Khadka — Thu, 26 Jan 2023 13:29:22 +0000

Intro

I am writing this short blog to share a solution to a problem that has been bugging me for a while, which is to find the last row in a column accurately.

Problem

Usually, when you use google apps script and want to find the last non-empty row we use something similar to the following code.

//...... 
// after certain codes
//last row
let lastRow = sheet
      .getRange(1, 1)
      .getNextDataCell(SpreadsheetApp.Direction.DOWN)
      .getRow();

If you've used this code then you know that this doesn't always work.

Helper Function

But first, a function to get all columns in a spreadsheet.

function getColumnNames() {
  let spreadsheet = SpreadsheetApp.openById(SPRD_ID);
  let activeSheet = spreadsheet.getSheetByName(SHEET_NAME);
  let lastCol = sheet
    .getRange(1, 1)
    .getNextDataCell(SpreadsheetApp.Direction.NEXT)
    .getColumn();

 // find the corrent index of url column
  let columns = activeSheet.getRange(1, 1, 1, lastCol).getValues().flat();
  console.log(columns)
  return columns;
}

Solution

So, this solution in stack overflow suggests using the logic of finding the last non-empty row starting from the bottom. I have modified this code to suit my needs.

Then the following code can calculate the last row of a column.

function getUrlColumnNRowNumber(colName = "Email") {

  try {
    let spreadsheet = SpreadsheetApp.openById(SPRD_ID);
    let activeSheet = spreadsheet.getSheetByName(SHEET_NAME);

    // find the corrent index of url column
    let columns = getColumnNames();
    let ind = columns.findIndex((val) => val.toLowerCase().trim() === colName.toLowerCase().trim());

    // check if -1 is returned else increase index by 1 to match with index system of spreadsheet
    let colIndex = ind === -1 ? null : ind + 1;
    if (colIndex === null) throw { "error": "No Column Name Found" };

    let maxRows = activeSheet.getMaxRows();
    // ger all the values with emtpy rows
    let columnRaw = activeSheet
      .getRange(2, colIndex, maxRows, 1)
      .getValues()
      .flat();

    let columnRawReverse = [...columnRaw].reverse();
    // find first non empty value  in the list 
    let firstNonEmptyCell = columnRawReverse.findIndex((el) => el !== "");

    // subtract it with maxrows and add 1 becuase range started from 2.
    //  if all rows are empty then return 2 
    let lastRowInCol = firstNonEmptyCell !== -1 ? maxRows - firstNonEmptyCell + 1 : 2;
    console.log([colIndex, lastRowInCol]);
    return [colIndex, lastRowInCol];
  }
  catch (e) {
    if (e.error === "No Column Name Found") {
      console.log("No column found");
      return [null, null]
    }

  }
}

If you've understood the code then no need to read further.

Explanation

Let's go over some minor details:

// find the corrent index of url column
    let columns = getColumnNames();
    let ind = columns.findIndex((val) => val.toLowerCase().trim() === colName.toLowerCase().trim());

   // check if -1 is returned else increase index by 1 to match with index system of spreadsheet
    let colIndex = ind === -1 ? null : ind + 1;
    if (colIndex === null) throw { "error": "No Column Name Found" };

Here, we get column names i.e, the first row from the spreadsheet. Then, on the second line, we check if the column name exists in the spreadsheet if not we are throwing an error, however, if it exists then we return the column index.


    let maxRows = activeSheet.getMaxRows();
    // ger all the values with emtpy rows
    let columnRaw = activeSheet
      .getRange(2, colIndex, maxRows, 1)
      .getValues()
      .flat();

    let columnRawReverse = [...columnRaw].reverse();

Now, we get the max number of rows in the current spreadsheet. Get the values in the column we're checking. The reverse is done to implement the findIndex() method next. Remember we're fetching values from the second row so, if the column is empty we have to consider this as well.


  // find first non empty value  in the list 
    let firstNonEmptyCell = columnRawReverse.findIndex((el) => el !== "");

Here, we use the findIndex() method to find the first non-empty value in the reversed list.

 // subtract it with maxrows and add 1 becuase range started from 2.
    //  if all rows are empty then return 2 
    let lastRowInCol = firstNonEmptyCell !== -1 ? maxRows - firstNonEmptyCell + 1 : 2;

The findIndex() returns -1 if the condition is not satisfied and if so then, we'll return 2 as the last row. Else, just subtract the index returned, with max rows, and then add 1(because we fetched the value from the second row).

Thank You

This is Nibesh Khadka from Khadka's Coding Lounge. Find my blogs here. Please do like and share if you like my work. Also, subscribe to get notified about the next upload.

I make Google Add-Ons and can also write Google Apps Scripts for you. If you need my services let me know directly or from fiverr or upwork.

Thank you for your time.