Wednesday, July 17, 2013

Google Spreadsheet Script to update value from one sheet to another

Question:

( by KarlS )


I have a spreadsheet that has 2 worksheets.  The first is a list of registrations for an event.  The second allows me to show a single person using the a Query formula and how much they paid towards registration.

I want to be able to change the Received amount for the person listed in the second worksheet without having to go to the first worksheet.  I am open to a script, a button to submit the information, etc.  I just don't know how to do this in the spreadsheet.


Textual description:

Sheet1
Contains data in columns A through X.  Column B is a person's name, Column W is the amount of money we have received from this person, what they owe to us.

Sheet2
Cell B2 is a Data Validation list from a Range, which is: Sheet1!B2:B
Cell A2 contains the formula =QUERY(Sheet1!A1:X,"Select B, G, L, M, N, O, P, Q, R, S, T, U, V, W, X where (B contains """ & B1 & """)") So information on a person is listed based on the person selected in cell B2.
Cell E1 will contain the new value to go into Cell W___  From the Query. 

How do I get the information put into Sheet2 Cell E1 into the appropriate record in Sheet1?

Karl

Using Windows 7, Chrome browser.


Solution:

Have a look at the following screenshot of Sheet1:






Have a look at the following screenshot of Sheet2:




Have a look at the following code:

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

function onEdit(e)

{
  var ss =SpreadsheetApp.getActiveSpreadsheet();
  var sheet2 =ss.getSheetByName("Sheet2");
  var activecell =ss.getActiveCell();
  
  if(activecell.getA1Notation()=="E1")
  {
    kishan(activecell.getValue());
  }
}

function kishan(currentvalue)
{
  var ss =SpreadsheetApp.getActiveSpreadsheet();
  var sheet2 =ss.getSheetByName("Sheet2");
  var sheet2cellB1 =sheet2.getRange("B1");
  var sheet1 =ss.getSheetByName("Sheet1");
  var datarange =sheet1.getDataRange();
  var values =datarange.getValues();
  
  for(var i=0;i<values.length;i++)
  {
    if(values[i][1]==sheet2cellB1.getValue())
    {
      sheet1.getRange(i+1, 23).setValue(currentvalue);
      return;
    }
  }

}





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

put the above code in your script editor, and then you can use it directly in your Google Spreadsheet or you can use its functionality in the Google Apps Script.



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 

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. Thank you for the help, Kishan,

    The script puts "undefined" in the target location. Also, could you put commentin the script so it can be followed easier? This is my first time with a script in a spreadsheet.

    ReplyDelete
  2. OK, sorry for rushing the first comment. If I have the input focus in cell E1, and manually Run the script, it puts that value in the right place. If focus is in an empty cell, it puts undefined in the target location. Ideally this would either run when I exit cell E1 in Sheet2 OR run based on a "Submit" button in a different cell, using the value of E1 and not the active cell value.

    ReplyDelete
    Replies
    1. Hi Karl,

      Don't run the script manually... It will run automatically whenever you update the value in Cell E1 of Sheet2 as the function kisha() is called in onEdit(e).

      So after copying the above script in your script editor, close your script editor.. and then update value in cell E1 of Sheet2 and you will notice the script executing as per your requirement...

      Delete