In this post, I demonstrate how to clear, delete, insert, and replace rows using Google Apps Script on Google Sheets.
How to Video:
Video Notes:
- Legacy Apps Script Editor Used in Video
- Apps Script (Script Editor) is now located under tab ‘Extensions’ instead of ‘Tools’ on Google Sheets
Code in Video:
function clearRow() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var editSheet = ss.getSheetByName("EDIT");
var lastRowEdit = editSheet.getLastRow();
for(var i = 2; i <= lastRowEdit; i++)
{
if(editSheet.getRange(i,1).getValue() == 'TRAIN')
{
editSheet.getRange('A' + i + ':C' + i).clear();
}
}
}
function deleteRow() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var editSheet = ss.getSheetByName("EDIT");
var lastRowEdit = editSheet.getLastRow();
for(var i = 2; i <= lastRowEdit; i++)
{
if(editSheet.getRange(i,1).getValue() == 'TRAIN')
{
editSheet.deleteRow(i);
}
}
}
function insertRow() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var editSheet = ss.getSheetByName("EDIT");
var lastRowEdit = editSheet.getLastRow();
for(var i = 2; i <= lastRowEdit; i++)
{
if(editSheet.getRange(i,1).getValue() == 'TRAIN')
{
editSheet.insertRowAfter(i);
}
}
}
function replaceRow() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var editSheet = ss.getSheetByName("EDIT");
var lastRowEdit = editSheet.getLastRow();
for(var i = 2; i <= lastRowEdit; i++)
{
if(editSheet.getRange(i,1).getValue() == 'TRAIN')
{
editSheet.getRange('A' + i + ':C' + i).setValues([['AIRPLANE', 'ORANGE', 30]]);
}
}
}Related Posts
How to Back Up Google Drive Files using Apps Script - In this post, I demonstrate how to back up Google Drive Files using Google Apps Script with Google Sheets.
How to Embed Charts on Google Sheets using Apps Script - In this post, I demonstrate How to Create Charts on Google Sheets using Google Apps Script.
How to Search Google Drive on Google Sheets using Apps Script - In this post, I demonstrate how to search Google Drive by File Name on Google Sheets using Apps Script
How to Create a Menu on Google Sheets using Apps Script - In this post, I demonstrate how to Create a Customer Menu on Google Sheets using Google Apps Script.
How to Get and Set Values on Google Sheets using Apps Script - In this post, I demonstrate how to Get and Set Values using Google Apps Script on Google Sheets.
How to Create Data Validation on Drop Down using Google Apps Script - In this post, I demonstrate How to Create Data Validation Drop-down List Using Google Apps Script on Google Sheets.
Create Stock Trend Analyzer on Google Sheets - In this video, I demonstrate how to analyze stock using the GOOGLEFINANACE Function on Google Sheets with Google Apps Script.
Scrape Amazon Website to Google Sheets - In this video, I demonstrate how to Scrape Amazon Website to Google Sheets using software called ScraperAPI.
Create Alert Popups on Google Sheets - In this post, I demonstrate how to create 3 different types of alert popups using Google Apps Script on Google Sheets.
Combine Multiple Spreadsheets into One Google Sheet - In this post, I demonstrate how to Combine Multiple Spreadsheets into one Sheet using Google Apps Script.
How to Get Next Open Row and Column using Google Apps Script - In this post, I demonstrate how to get the next open row and column using Google Apps Script on Google Sheets.
How to Get Map Time and Distance using Google Apps Script - In this post, I demonstrate how to get the Map Time and Distance between locations using Google Apps Script using the Map Class.
How to Create Dependent Dropdown on Google Sheets - In this post, I demonstrate how to Create a Dependent Dropdown list on Google Sheets using Google Apps Script.
How to Call Rest API on Google Sheets - In this post, I demonstrate how to call a Rest API using Google Apps Script and Displaying the data on a Google Sheet.
How to Create Google Doc from Apps Script - In this post, I demonstrate how to create a Google Doc using Google Apps Script and save it in Google Drive. In this example, I use the data from a Google Sheet.
Email Last Update on Google Sheet - In this post, I demonstrate how to email the last update made on a google sheet. This is a way to monitor any changes made on Google Sheets
How to Call JSON Web Service to Populate Google Sheet - In this post, I demonstrate how to Call a JSON Web Service and Populate a Google Sheet with a table of raw data.
Scrape Website Data to Google Sheets - In this post, I demonstrate how to Scrape data from a Website and import it to a Google Sheet. I used JavaScript and Reg Expressions to accomplish the task.
Create Report Builder on Google Sheets using QUERY Function - In this post, I demonstrate how to use the QUERY function and Google Apps Script and build multiple report views from a list.
Copy Row from Sheet to Sheet using Apps Script on Google Sheets - In this post, I show how to copy a row of data from one sheet to another using Google Apps Script. This video also shows how to search a column on Google Sheets.
