Saturday, August 5, 2017

Google Sheet Script to Navigate various HTML Pages

Here 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 = HtmlService.createTemplateFromFile(htmlPage);
  html.counter = count;
  var htmlx = html.evaluate();
  return htmlx;
};

function getScriptUrl() {
  var url = ScriptApp.getService().getUrl();  
  return url;
};

Following is the code for "index.html":

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <?var url = getScriptUrl();?>
    Hello I'm Index!
    <br><br>
      <a href='<?=url?>?page=apple&count=<?!= ((counter*1)+1) ?>'>
        <input name="apple" id="apple" type="button" value="Apple Page"></a>
      <br><br>
      <a href='<?=url?>?page=google&count=<?!= ((counter*1)+1) ?>'>
      <input name="google" id="google" type="button" value="Google Page"></a>
      <br><br>
      You have clicked
    <input type="text" id="label" name="label" value="<?!= (counter*1) ?>" readonly="readonly"
    style="text-align:center;width:20px;font-weight:bold;border:none;background:transparent;"/>
    times
  </body>
</html>

Following is the code for "apple.html":

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <?var url = getScriptUrl();?>
    Hello I am Apple!
    <br><br>
      <a href='<?=url?>?page=index&count=<?!= ((counter*1)+1) ?>'>
        <input name="index" id="index" type="button" value="Index Page"></a>
      <br><br>
      <a href='<?=url?>?page=google&count=<?!= ((counter*1)+1) ?>'>
      <input name="google" id="google" type="button" value="Google Page"></a>
      <br><br>
      You have clicked
    <input type="text" id="label" name="label" value="<?!= (counter*1) ?>" readonly="readonly"
    style="text-align:center;width:20px;font-weight:bold;border:none;background:transparent;"/>
    times
  </body>
</html>

Following is the code for "google.html":

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <?var url = getScriptUrl();?>
    Hello I am Google!
    <br><br>
      <a href='<?=url?>?page=apple&count=<?!= ((counter*1)+1) ?>'>
        <input name="apple" id="apple" type="button" value="Apple Page"></a>
      <br><br>
      <a href='<?=url?>?page=index&count=<?!= ((counter*1)+1) ?>'>
      <input name="index" id="index" type="button" value="Index Page"></a>
      <br><br>
      You have clicked
    <input type="text" id="label" name="label" value="<?!= (counter*1) ?>" readonly="readonly"
    style="text-align:center;width:20px;font-weight:bold;border:none;background:transparent;"/>
    times
  </body>
</html>

Monday, July 3, 2017

Google Sheet Script for border style

Question:

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 right borders lenght should be fitted to rows with data only as well.


I am total noob in scripts, could somebody help me with proper script for this?
And i hope this script can be set to autorun, so i don’t have to run it each time.

Or maybe there is some other solution more simple :)

Solution:

Try the following script code:

////////////////////////////////////////

function fillBorders() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("Sheet1");
  var r = s.getRange("D69:J79");
  var v = r.getValues();
  var firstRow = r.getRow();
  var lastRow = r.getLastRow();
  var lastFilledRow = lastRow;
  r.setBorder(false,false,false,false,false,false);
 
  for(var i=v.length-1;i>=0;i--) {
    var isRowBlank = true;
    for(var j=0;j<v[0].length;j++) {
      if(v[i][j]!="") {
        isRowBlank = false;
        lastFilledRow = firstRow + i;
        break;
      }
    }
    if(isRowBlank==false)
      break;
  }
 
  s.getRange("D"+firstRow+":J"+lastFilledRow).setBorder(true,true,true,true,true,true);
  SpreadsheetApp.flush();
};

////////////////////////////////////////

in the above script code, change the sheet name "Sheet1" as per your need.

Saturday, July 1, 2017

Google Sheet Script to extract URL from a Cell containing function HYPERLINK

In 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 = activeRange.getFormula();
  if(formula=="") return "";
  var rangeA1Notation = formula.match(/\((.*)\)/).pop();
  var range = activeSheet.getRange(rangeA1Notation);
  var f = range.getFormulas();
  var v = range.getValues();
  for(var i=0;i<f.length;i++) {
    for(var j=0;j<f[0].length;j++) {
      if(f[i][j]=="") v[i][j]="";
      var matchURL = f[i][j].match(/\"(.*?)\"/);
      if(matchURL==null) v[i][j]="";
      else v[i][j]=matchURL.pop();
    }
  }
  return v;
};

/////////////////////////////////////////////////

copy the above script code in the script editor of your spreadsheet and then use custom function "=extractURL(A2:A10)" to extract the URLs from the range "A2:A10"

Screenshot:


Monday, February 20, 2017

Google Script to auto delete old files from a Google Drive Folder

If 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:

//////////////////////////////////////////////////

function deleteOldSheets() {
  var df = DriveApp.getFolderById("xxx_GOOGLE_DRIVE_FOLDER_ID_xxx");
  var arr = new Array();
  var files = df.getFiles();
  while( files.hasNext() ) {
    var f = files.next();
    arr.push( [ [ f.getId() ] , [ f.getLastUpdated() ] ] );
  }
  arr.sort( sortFunction );
  
  for( var i=100 ; i<arr.length ; i++ )
    DriveApp.getFileById( arr[i][0] ).setTrashed( true );
};

function sortFunction( a , b ) {
  var aDate = new Date(a[1]);
  var bDate = new Date(b[1]);
  if ( aDate === bDate ) return 0;
  else if ( aDate < bDate ) return 1;
  else return -1;
};

//////////////////////////////////////////////////

The above script code will help you to delete the back up files ( from a Google Drive folder ) which are excessive and not required anymore, the script will move these files to "Trash".

And if you want to automatically run the above script daily, then in the script editor you can set the trigger for it.

NOTE: In the above script you'll need to replace the Google Drive folder Id.

Thursday, November 12, 2015

Google Spreadsheet Count of colored cells in a range in Custom Function

Count 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 cells, and also you need to pass the cell in the second parameter which have the background that you need to count in the range (provided in first parameter).

Put the following script code in the script editor of your spreadsheet:

/////////////////////////////////////////////////

/**
* @param {range} countRange Range to be evaluated
* @param {range} colorRef Cell with background color to be searched for in countRange
* @return {number}
* @customfunction
*/

function countColoredCells(countRange,colorRef) {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formula = activeRange.getFormula();
  
  var rangeA1Notation = formula.match(/\((.*)\,/).pop();
  var range = activeSheet.getRange(rangeA1Notation);
  var bg = range.getBackgrounds();
  var values = range.getValues();
  
  var colorCellA1Notation = formula.match(/\,(.*)\)/).pop();
  var colorCell = activeSheet.getRange(colorCellA1Notation);
  var color = colorCell.getBackground();
  
  var count = 0;
  
  for(var i=0;i<bg.length;i++)
    for(var j=0;j<bg[0].length;j++)
      if( bg[i][j] == color )
        count=count+1;
  return count;
};

/////////////////////////////////////////////////

NOTE:


When you change the color of any cell the value will not get auto updated in the result,
so you need to temporary change the value of any of one of the cell in the range (and then change it back as you want..)

this will update the color counting result...

For getting sum instead of count of cells having a background color, look at the following link:
http://igoogledrive.blogspot.com/2015/11/google-spreadsheet-sum-of-colored-cells.html

Wednesday, November 11, 2015

Google Spreadsheet Sum of a colored cells in a range in Custom Function

Sum 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 colored cells, and also you need to pass the cell in the second parameter which have the background that you need to sum in the range (provided in first parameter).

Put the following is the script code in the script editor of your spreadsheet:


////////////////////////////////////////////////////


/**

* @param {range} sumRange Range to be evaluated
* @param {range} colorRef Cell with background color to be searched for in sumRange
* @return {number}
* @customfunction
*/

function sumColoredCells(sumRange,colorRef) {

  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formula = activeRange.getFormula();
  
  var rangeA1Notation = formula.match(/\((.*)\,/).pop();
  var range = activeSheet.getRange(rangeA1Notation);
  var bg = range.getBackgrounds();
  var values = range.getValues();
  
  var colorCellA1Notation = formula.match(/\,(.*)\)/).pop();
  var colorCell = activeSheet.getRange(colorCellA1Notation);
  var color = colorCell.getBackground();
  
  var total = 0;
  
  for(var i=0;i<bg.length;i++)
    for(var j=0;j<bg[0].length;j++)
      if( bg[i][j] == color )
        total=total+(values[i][j]*1);
  return total;
};

////////////////////////////////////////////////////

NOTE:

When you change the color of any cell the value will not get auto updated in the sum result,
so you need to temporary change the value of any of one of the cell in the range (and then change it back as you want..)

this will update the colored cells sum result...

For getting count instead of sum of values of cells having a background color, look at the following link:
http://igoogledrive.blogspot.com/2015/11/google-spreadsheet-count-of-colored.html

Saturday, November 7, 2015

Google Spreadsheet Getting the last 5 values before the previous last 5

Question:

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 the same values in column C and the row number in column D


Now I have the following formula in cell C1:
=query(arrayformula({A:A,row(A:A)}),"select Col1,Col2 where Col1 is not null",0)
to get the both columns having equal number of rows (that is rows having values in Column C)


Now I have the following formula in cell C1:
=query(arrayformula({A:A,row(A:A)}),"select Col1,Col2 where Col1 is not null order by Col2 desc",0)
to get the data in descending order of row number, as we want second last 5 rows...


Now I have the following formula in cell C1:
=query(arrayformula({A:A,row(A:A)}),"select Col1,Col2 where Col1 is not null order by Col2 desc offset 5",0)
to start the data from second last 5 rows, I have used "offset" in query formula.


Now I have the following formula in cell C1:
=query(arrayformula({A:A,row(A:A)}),"select Col1,Col2 where Col1 is not null order by Col2 desc limit 5 offset 5",0)
to limit the data upto only 5 rows.


Now I have the following formula in cell C1:
=query(arrayformula({A:A,row(A:A)}),"select Col1 where Col1 is not null order by Col2 desc limit 5 offset 5",0)
to get the data in only column C of second last 5 rows.


Now I have the following formula in cell C1:
=average(iferror(query(arrayformula({A:A,row(A:A)}),"select Col1 where Col1 is not null order by Col2 desc limit 5 offset 5",0)))
to finally get the average of second last 5 rows.