Quick Reference

Knowledge Sharing

check rows for cell match in Google sheet Script

check rows for cell match in Google sheet Script The following describes on how to check rows for cell match in Google Sheet Script function onSearch() { var ss1 = SpreadsheetApp.getActiveSpreadsheet(); var sheet1 = ss1.getSheetByName("Main Sheet"), searchVal1 = sheet1.getRange("A1").getValue(), searchCol1 = sheet1.getRange('A2:A').getValues(); for (var i = 0, len = searchCol1.length; i < len; i++)...

Get position of sub string in Google Sheet script

Get position of sub string in Google Sheet script The following will demonstrate how to get position of sub string in google sheet script. The position can be a single character or sub string. In the following example, position of “@” character can be found using “indexof” function stringIndexOfTest1(){ var test_mail = "test@gmail.com"; var...

Create Custom menus in Google Sheet Script

Custom menus in Google Sheet Script The following is used to create custom menus in Google sheet script. The new menus can be quickly used to run the program. function onOpen() { var ui = SpreadsheetApp.getUi(); ui.createMenu('Custom Menu1') .addItem('First item1', 'menuItem11') .addSeparator() .addSubMenu(ui.createMenu('Sub-menu1') .addItem('Second item1', 'menuItem21')) .addToUi(); } function menuItem11() { SpreadsheetApp.getUi()  .alert('You clicked...

Paste Special Values in Google Sheet Script

Paste Special Values in Google Sheet Script The following will paste special values in Google Sheet Script. This avoids transferring the formats. Here contentOnly is set True. It is an important criterion. function moveValuesOnly() { var ss1 = SpreadsheetApp.getActiveSpreadsheet(); var source1 = ss1.getRange('Sheet1!A1:A3'); source1.copyTo(ss1.getRange('Sheet2!A1'), {contentsOnly: true}); //source1.clear(); } If there only one cell to...

Auto Run Subroutine every 1 Second in Excel VBA

Auto Run Subroutine every 1 Second in Excel VBA The following snippet of code will auto run subroutine every 1 second in Excel VBA Sub Cal_Range() Range(“A1:B100″).Calculate Application.OnTime DateAdd(“s”, 1, Now), “Cal_Range” End Sub Public RunWhen As Double Public Const cRunWhat = “my_Procedure” Sub StartTimer() RunWhen = Now + TimeSerial(0, 10, 0) Application.OnTime earliesttime:=RunWhen,...

How to skip error in Excel VBA Program

How to skip error in Excel VBA Program The following snippet will advise on how to skip error in Excel VBA Program.  It contains “On Error”,”Resume Next”,Err.number,etc..   on error resume next ‘—-Line contain possibility of error. if err.number>0 then err.number=0 goto skip end if   skip:  

Image URLs to actual images using Excel VBA

Image URLs to actual images using Excel VBA The following program converts Image URLs to actual images using Excel VBA. It does not need to get the information using IE or Httpserver requests.   Sub URLPictureInsert()     Dim shp As Shape     Dim Rge As Range     Dim Col As Long     On Error Resume Next     Application.ScreenUpdating = False     Set Rng =...

Delete all pictures in a selected range of cells Excel VBA

Delete all pictures in a selected range of cells Excel VBA The following program Deletes all pictures in a selected range of cells Excel VBA. Sub DeletePics() Dim PicRng As Range Dim Pic As Picture Dim Rng As Range Application.ScreenUpdating = False Set Rng = Range("B1:B100") For Each Pic In ActiveSheet.Pictures Set PicRng =...

Create new CSV file with copied data using Excel VBA

Create new CSV file with copied data using Excel VBA Create New CSV file with copied data  using Excel VBA from a macro file. Besides that,  the data transfer takes place to csv with copy paste program codes. Therefore, the following program generates csv file with copied data. Sub csv_gen() Dim wb_main As Workbook...

Published On

December 2018
M T W T F S S
« Nov    
 12
3456789
10111213141516
17181920212223
24252627282930
31