Hello and welcome to our blog series on automating . 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. Google Sheets with Apps Script 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 , 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. UiApp Writing Functions to Insert Multiple Rows in Google Sheets Now, on your spreadsheet, click the tab and then as shown in the image below. Extensions Apps Script . If you're continuing on the , then create a new script file with a preferred name, I'll call it previous blog 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 - and respectively. These functions will come in handy when you need to insert multiple rows at once, saving you time and effort. insertRowsBefore() insertRowsAfter() 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 , then locate the function in your files and edit the codes there. And only add one of the following functions in your files per your needs. previous blog createCustomMenu() // 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. Also published . here

The code in this story is for educational purposes. The readers are solely responsible for whatever they build with it.

How to check word count in Google Docs?

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

Subscribe To Our News Letter For Free Contents

Hire Me

Inserting Multiple Rows in Google Sheets with Apps Script — A Guide

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Access User Location with Permission Handler in Flutter

10 Indications That You Should Invest in Automation Via APIs

10 Commandments for AI-Assisted Social Media Marketers

11 Best Automation Testing Tools to Try in 2021

12 Use Cases of AI and Machine Learning In Finance

12 Ways You Can Use Email to Nurture Leads to Conversion

Access User Location with Permission Handler in Flutter

10 Indications That You Should Invest in Automation Via APIs

10 Commandments for AI-Assisted Social Media Marketers

11 Best Automation Testing Tools to Try in 2021

12 Use Cases of AI and Machine Learning In Finance

12 Ways You Can Use Email to Nurture Leads to Conversion

Light-Mode

Classic

Newspaper

Minty

Dark-Mode

Neon Noir

Minty

HN StartUps