tag:blogger.com,1999:blog-67994284266803729122024-03-25T11:39:13.896+05:30iGoogleDriveKishhttp://www.blogger.com/profile/13751098853627072874noreply@blogger.comBlogger357125tag:blogger.com,1999:blog-6799428426680372912.post-9785212488320949862019-07-26T01:05:00.000+05:302019-07-26T01:05:25.384+05:30Google Sheets - Data Validation (Dynamic Dropdown) - using Google Apps ScriptIn Google Sheets, if you want to have the dynamic dropdown, that is data validation based on the some other (adjacent) cell, then it is possible using Google Apps Script.
Consider the following example:
Screenshot of Sheet "ROI" which has the data from which we have to get the value of dependent data validation:
Screenshot of Sheet "Sales" in which Data Validation is required:
Kishhttp://www.blogger.com/profile/13751098853627072874noreply@blogger.com5tag:blogger.com,1999:blog-6799428426680372912.post-47205892962683456842019-06-03T22:17:00.000+05:302019-06-03T22:17:09.496+05:30Google Apps Script to delete all the filter views in a spreadsheetIf you have created lots of filter views in a Google Spreadsheet and find it hard to delete all of them one by one manually then you can use the following Google Apps Script code to delete all filter views in one go!
function deleteAllFilterViews() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var id = ss.getId();
var myFilterViews = Sheets.Spreadsheets.get(id, {
Kishhttp://www.blogger.com/profile/13751098853627072874noreply@blogger.com30tag:blogger.com,1999:blog-6799428426680372912.post-49435951437279391012017-09-15T15:39:00.000+05:302017-09-15T15:39:20.833+05:30Convert ##m ##s formatted text to a number in secondsQuestion:
Hello!I'm hoping to find some way to convert this data into numbers so I can use those numbers for other formulas. I have checked the reporting system of the program I am pulling this info from and I have no choice but to pull this data in this format.Here's the raw data:
NameResponse TimeDuration
Agent A1m 58s19m 27s
Agent B1m 3s18m 7s
Agent C49s16m 27s
and I would like to Kishhttp://www.blogger.com/profile/13751098853627072874noreply@blogger.com0tag:blogger.com,1999:blog-6799428426680372912.post-23004691178278209442017-08-05T13:26:00.000+05:302017-08-05T13:30:40.362+05:30Google Sheet Script to Navigate various HTML PagesHere is the Google Sheet Script to Navigate various HTML Pages by passing the needed data via URL parameter.
Screencast:
Following is the code for "Code.gs":
function doGet(e) {
var htmlPage,count;
e.parameter.page==null ? htmlPage="index" : htmlPage = e.parameter.page;
e.parameter.count==null ? count=0 : count = e.parameter.count;
var html = Kishhttp://www.blogger.com/profile/13751098853627072874noreply@blogger.com0tag:blogger.com,1999:blog-6799428426680372912.post-12638994318906441192017-07-03T10:30:00.001+05:302017-07-03T10:30:46.761+05:30Google Sheet Script for border styleQuestion:
I have columns and rows: D69:J79 filled with data, data in rows is changing on daily basis, one day it’s D69:J79, another it’s shorter D69:J77 or D69:J75 etc, rows below are blanks accordingly, columns stay the same.
I want script to make regular square line border around this data, but with one rule - bottom line of square should be right below latest row with data. And left and Kishhttp://www.blogger.com/profile/13751098853627072874noreply@blogger.com0tag:blogger.com,1999:blog-6799428426680372912.post-11542096085857528062017-07-01T11:37:00.000+05:302017-07-01T11:37:34.403+05:30Google Sheet Script to extract URL from a Cell containing function HYPERLINKIn Google Sheet, if you have a cell or a range containing functions HYPERLINK and if you want to extract URL(s) from it then you can do it with the help of following script:
/////////////////////////////////////////////////
function extractURL() {
var activeRange = SpreadsheetApp.getActiveRange();
var activeSheet = activeRange.getSheet();
var formula = Kishhttp://www.blogger.com/profile/13751098853627072874noreply@blogger.com0tag:blogger.com,1999:blog-6799428426680372912.post-68881598176813424092017-02-20T13:24:00.000+05:302017-02-20T13:30:37.431+05:30Google Script to auto delete old files from a Google Drive FolderIf you are creating auto backup of any file in Google Drive using script ( either daily or on any edits ), so the number of back up file keeps on increasing. Now, if you want to delete the excessive files, for example if you want to delete all the files except the last 100 back up files, then you can achieve it by using the following script code:
/////////////////////////////////////////////////Kishhttp://www.blogger.com/profile/13751098853627072874noreply@blogger.com4tag:blogger.com,1999:blog-6799428426680372912.post-35951538213636982552015-11-12T00:14:00.000+05:302015-11-12T00:32:08.927+05:30Google Spreadsheet Count of colored cells in a range in Custom FunctionCount of colored cells in a range in Custom Function
I have the following formula in cell E9:
=countColoredCells(A1:C6,E1)
and the following formula in cell F9:
=countColoredCells(A1:C6,F1)
countColoredCells is a custom function created with the help of the following script code, in this function you need to pass the range in first parameter in which you want to count the colored Kishhttp://www.blogger.com/profile/13751098853627072874noreply@blogger.com118tag:blogger.com,1999:blog-6799428426680372912.post-17522613375393356902015-11-11T19:43:00.000+05:302018-03-23T13:56:23.642+05:30Google Spreadsheet Sum of a colored cells in a range in Custom FunctionSum of a colored cells in a range in Custom Function
I have the following formula in cell E9:
=sumColoredCells(A1:C6,E1)
and the following formula in cell F9:
=sumColoredCells(A1:C6,F1)
sumColoredCells is a custom function created with the help of the following script code, in this function you need to pass the range in first parameter in which you want to sum the values of the Kishhttp://www.blogger.com/profile/13751098853627072874noreply@blogger.com101tag:blogger.com,1999:blog-6799428426680372912.post-82127225204455045572015-11-07T23:04:00.001+05:302015-11-07T23:04:26.729+05:30Google Spreadsheet Getting the last 5 values before the previous last 5Question:
Hi, I am trying to get the average values in a row present at the last 5 rows before the last 5 rows, in other words in a sheet with 15 rows in a column I would like to know how to attain the average value of rows 5 to 10.
Solution:
Following is the screenshot of the sheet having data in column A:
Now I have the following formula in cell C1:
=arrayformula({A:A,row(A:A)})
to get Kishhttp://www.blogger.com/profile/13751098853627072874noreply@blogger.com0tag:blogger.com,1999:blog-6799428426680372912.post-64874173942717333752015-11-04T15:37:00.000+05:302015-11-04T15:37:40.147+05:30Google Spreadsheet Script for manipulating JSONThe following URL "http://shanghai.anjuke.com/ajax/pricetrend/comm?cid=10052", has the JSON data:
Now to fetch this data in Google Spreadsheet, use the following script:
/////////////////////////////////////////////////
function extract(url){
var response = UrlFetchApp.fetch(url);
var data = JSON.parse(response.getContentText());
var comm = data.comm;
 Kishhttp://www.blogger.com/profile/13751098853627072874noreply@blogger.com2tag:blogger.com,1999:blog-6799428426680372912.post-31016233236304350322015-10-23T15:27:00.003+05:302015-10-23T15:30:50.529+05:30Google Spreadsheet return a subset given complex criteriaQuestion:
Here is my sample sheet:
Given the example data in this sheet, I want to return a subset automatically that meets the following criteria:
* 4 total rows
* 1 row where color=green
* 1 row where color=red
* 2 rows where color=blue
* The COMBINED value of the PRICE column of the four returned rows cannot exceed a sum of 30
* The returned set should provide the highest Kishhttp://www.blogger.com/profile/13751098853627072874noreply@blogger.com0tag:blogger.com,1999:blog-6799428426680372912.post-76177427397676292572015-10-09T00:09:00.001+05:302015-10-09T00:09:34.614+05:30Google Spreadsheet generating a list of the most frequently used words in an entire sheetQuestion:
Hey guys,
I have a spreadsheet made up of columns that list the top 100 search terms on a website for each month of the year. Is there a formula I can use to search all of the columns and generate a list of the overall top 20 terms?
Any help would be greatly appreciated.
Thanks!
Solution:
Screenshot of "Sheet2":
First of all to get the unique values of Sheet1, I have the Kishhttp://www.blogger.com/profile/13751098853627072874noreply@blogger.com0tag:blogger.com,1999:blog-6799428426680372912.post-78749535290541110562015-10-07T19:53:00.003+05:302015-10-07T19:53:39.755+05:30Google Sheet Select items by column that contains few parameters separated by commaQuestion:
Hi everyone, I am trying to create some sort of inventory database using google sheets. I have one sheet 'Inventory' that lists all the items in my database. In another sheet 'Search' I want to have an ability to display items that qualify size dispersion. The problem is that items have multiple sizes listed in one cell separated by comma.
TimestampNameWeightPriceTire sizes
Kishhttp://www.blogger.com/profile/13751098853627072874noreply@blogger.com2tag:blogger.com,1999:blog-6799428426680372912.post-43505530268304856032015-10-03T14:41:00.003+05:302015-10-03T14:41:50.311+05:30Google Spreadsheet Query and Vlookup FormulaQuestion:
Hi, I am new to using google sheets, but am finding them very helpful. I have 1 problem I haven't figured out yet though.
I have a sheet with the following info.
Name Den Amount
Bob Tiger 55.00
Tom &Kishhttp://www.blogger.com/profile/13751098853627072874noreply@blogger.com0tag:blogger.com,1999:blog-6799428426680372912.post-84163143528527717622015-09-20T14:26:00.002+05:302015-09-20T14:26:49.366+05:30Google Spreadsheet Column of "from" labels along each IMPORTRANGEd row of dataQuestion:
Hi all, I've asked it before but got no reply. I will try a more visual approach this time.
Here is what I have in a spreadsheet:
Sheet description | URL to IMPORTRANGE
Classroom 1 | https://docs.google.com/spreadsheets/d/1yjSEFek(...)
Classroom 2 | https://docs.google.com/Kishhttp://www.blogger.com/profile/13751098853627072874noreply@blogger.com0tag:blogger.com,1999:blog-6799428426680372912.post-86722992583698668922015-09-05T15:17:00.002+05:302015-09-05T15:17:38.492+05:30Google Sheet Query involving counting with differences between columnsQuestion:
Here's a sample of what the sheet looks like:
A B C1 0 Win2 0 Win3 0 Lose4 4 Lose5 0 Win3 2 Lose2 0 Lose0 1 Win1 2 LoseI want to Kishhttp://www.blogger.com/profile/13751098853627072874noreply@blogger.com0tag:blogger.com,1999:blog-6799428426680372912.post-22266516479588013482015-09-03T15:26:00.000+05:302015-09-03T15:29:15.094+05:30Google Sheets Query involving two groups of dataQuestion:
The easiest way to explain what I'm trying to do is to give an example of the data, so here's a simple example:
A B C DName1 4 Name4 3Name2 3 Kishhttp://www.blogger.com/profile/13751098853627072874noreply@blogger.com0tag:blogger.com,1999:blog-6799428426680372912.post-40548147523653890062015-09-01T09:47:00.003+05:302015-09-01T09:47:57.745+05:30Split list and append DataQuestion:
I have a sheet called Master which has 3 columns: Name Home_Address, Work_Address. Note that a Name may have one or both associated addresses.
In a second sheet, Called Split. This sheet has 3 columns: Name, Address, Type I want to split each name and address to make 1 row for each Address, be it work or home, and add a last column, Type, with either Work or Home for values. Kishhttp://www.blogger.com/profile/13751098853627072874noreply@blogger.com0tag:blogger.com,1999:blog-6799428426680372912.post-68286494250315048862015-07-23T15:25:00.000+05:302015-07-23T15:41:15.415+05:30Fetch data in MS Excel from Google Sheet
Question:
( by Chris Gob Shite )
I have a google form which is capturing data that is saved in a Google Sheet
I want to merge that data into an official form in MS Word
MS Word will only merge with an Excel sheet and not a Google Sheet
Currently just copy/pasting everytime there is a new entry
Just wondering
Any Sheets gurus know how to Create a Kishhttp://www.blogger.com/profile/13751098853627072874noreply@blogger.com0tag:blogger.com,1999:blog-6799428426680372912.post-36377075521850661242014-04-25T20:27:00.001+05:302014-04-25T20:27:34.742+05:30Google Spreadsheet Solution With Advanced Query FormulaQuestion:
( by ANTONINO L )
Who can help me to solve this problem:
I would like to sum the number of packages in a column having a specific code for each month.
How can I do?
Thanks
SHEET1SHEET 2
DATISUMMARY
CODDATAQ.TA'CODDESCRQ.TA' GENQ.TA' FEBQ.TA' MAR
6401/03/2014564PROD15
222713/02/201442227PROD2105
222711/03/201453339PROD3
345609/01/2014133456PROD413
Kishhttp://www.blogger.com/profile/13751098853627072874noreply@blogger.com0tag:blogger.com,1999:blog-6799428426680372912.post-43508138325617640822013-10-26T17:52:00.001+05:302013-10-26T17:54:19.470+05:30Google Spreadsheet How to stack different columns using a formula or query.Question:
( by Gilles-Japon )
Using Mac 10.8.5, Chrome or Safari
I have a form that collects information for an entire family. We ask each participating member of the family the same information (rental of ski, size of shoe,...). Some of the members have to and some don't have to fill the information (they do not want the service). My problem is that the information of all the Kishhttp://www.blogger.com/profile/13751098853627072874noreply@blogger.com0tag:blogger.com,1999:blog-6799428426680372912.post-90661933150648783712013-10-18T08:59:00.001+05:302013-10-18T08:59:45.181+05:30Converting Text responses to numerical valuesQuestion:
( by Jake Probst )
Hello,
I am doing some monitoring with a student using this form:
The results end up like this:
What I would like to do now, is create a separate sheet to graph each column over time. In order to do this, I was thinking I would use a conversion sheet that uses IF THAN statements to convert the results to numerical values.
This is one Kishhttp://www.blogger.com/profile/13751098853627072874noreply@blogger.com2tag:blogger.com,1999:blog-6799428426680372912.post-84491673008455492172013-10-14T07:39:00.002+05:302013-10-14T07:40:10.064+05:30Google Spreadsheet Copy data from one sheet to another if certain conditions are met removing duplicatesQuestion:
( by tic toc )
I would like to be able to copy data from Sheet1 to Sheet2 displaying only those rows with the highest values from Sheet1.
The following is an example of what I would like to do. Sheet3 is the result that I would like to achieve.
Sheet1:
Sheet3:
If "Name" and "Value 1" are the same, I only want to copy the row with the highest number in Kishhttp://www.blogger.com/profile/13751098853627072874noreply@blogger.com2tag:blogger.com,1999:blog-6799428426680372912.post-43901532295400714442013-10-04T23:12:00.001+05:302013-10-04T23:12:55.645+05:30Google Spreadsheet Compare between software version numbersQuestion:
( by Spon4ik )
I need to compare between several version numbers with unusual decemas for integer values like 4.0.4 & 4.3.0
and also to be able to filter and display only the greatest or lowest version.
MIN/MAX doesn't see it as numbers, and doesn't work for me.
any suggestions?
I need help with a filtering and comparison between numbers as 3.2.5>2.4.3>2.2.6 Kishhttp://www.blogger.com/profile/13751098853627072874noreply@blogger.com0