Wednesday, July 24, 2013

Google Spreadsheet Daily insert text at the bottom of a column

Question:

( by 
Roy Silverman )

Hi all, 

I wanted to ask if it is possible to have a function/formula run on one of my google spreadsheet once everyday automatically. 

The purpose of this formula is to check what is the last cell in a specific column that does not contain any characters and add the number 1. 

Help will be highly appreciated ! 

Thanks !

Solution:

You can achieve this with the help of Scripts.

Have a look at the following screenshot of my Spreadsheet:




Have a look at the following script code:

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

function addValueInLastCell() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  var column = sheet.getRange("C:C"); //for column C
  var values = column.getValues();  
  for(var i=0;i<values.length;i++)
    if(values[i][0]=="") { values[i][0]=1; break; }
  column.setValues(values);
}

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

put the above code in the Script editor of your Spreadsheet. And after inserting this code, you have to set the Triggers (Time Driven) so that the above code executes daily...


And If you are not much familiar with Scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

And If you are not much familiar with Triggers then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-set-trigger-on-script-in-google.html

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

3 comments:

  1. Thanks !

    For some reason I am getting the following message when trying to implement the function :

    "TypeError: Cannot call method "getRange" of null. (line 4, file "roy_code")"

    Any ideas ?

    ReplyDelete
    Replies
    1. And did you changed the sheet name ?? It might be the case that "Sheet1" doesn't exists in your spreadsheet, so rename Sheet1 in the code as per your requirement..

      Delete