Last active
February 11, 2019 08:43
-
-
Save moayadhani/b0aa936a32a842b50c258238ce85988c to your computer and use it in GitHub Desktop.
How to Add a New Cell to an Existing Named Range on Google Script
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
//This code is developed by Moayad Hani Yacoub Abu Rmilah and can be used freely for whatever purpose | |
/*This example shows how to: | |
1- create customized menu on Google Sheet using Google Script | |
2- Check if a sheet exists using sheet name | |
3- Get user entry by using UI dialog | |
4- Add a cell to an existing named range | |
5- Change text to camel case using regex expressions | |
6- Showing messages to the user */ | |
//You need to accept the required permissions for this code to work. | |
var databaseSheetName = 'Database'; | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var databaseSheet = ss.getSheetByName(databaseSheetName); | |
var ui = SpreadsheetApp.getUi(); | |
//Create customized menu | |
function onOpen(e) { | |
ui.createMenu('Customized Menu'). | |
addSubMenu(ui.createMenu('Database:') | |
.addItem('New Entry', 'addNewEntry')) | |
.addToUi(); | |
} | |
function addNewEntry(){ | |
//return if sheet is missing | |
if (isSheetMissing(databaseSheetName)){ | |
return; | |
} | |
var myNamedRange = ss.getRange('RangeName1'); | |
if (!myNamedRange){ | |
Browser.msgBox('A named range with name "RangeName1" does not exist. It may have been deleted or renamed.'); | |
} | |
var newEntryPrompt = ui.prompt('New Entry', 'Key in a new entry:', Browser.Buttons.OK_CANCEL); | |
if (newEntryPrompt.getSelectedButton() == ui.Button.CANCEL){ | |
return; | |
} | |
var entryText = newEntryPrompt.getResponseText(); | |
entryText = toCamelCase(entryText); | |
var allEntries = myNamedRange.getValues(); //this is a 2-D array | |
var newArr = allEntries.join().split(','); //splits the 2-D array into 1-D array | |
if(newArr.indexOf(entryText) != -1){ | |
Browser.msgBox('Your keyed entry already exists.'); | |
return; | |
} | |
var firstRow = myNamedRange.getRow(); | |
var lastRow = myNamedRange.getLastRow(); | |
var columnNum = myNamedRange.getColumn(); | |
databaseSheet.getRange(lastRow + 1, columnNum).setValue(entryText); | |
var newRange = databaseSheet.getRange(firstRow, columnNum, 2 + lastRow - firstRow); | |
ss.setNamedRange('RangeName1', newRange); | |
} | |
//Check if a sheet is missing (by sheet name) | |
function isSheetMissing(mySheetName){ | |
if (!ss.getSheetByName(mySheetName)){ | |
Browser.msgBox('The sheet named ' + mySheetName + ' does not exist. You may have deleted or renamed the sheet.'); | |
return true; | |
} | |
} | |
//Capitalize the first character of each word using regex expressions in string replace | |
function toCamelCase(str){ | |
return str.toLowerCase().replace(/\b(.)/g, function($1) { return $1.toUpperCase(); }); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment