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.

Here is a sample of the spreadsheet:  https://docs.google.com/spreadsheet/ccc?key=0At1nRlqGXWuvdF9nSk52dG4zVDVfV3I4VkNid18yMHc&usp=sharing

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:

http://igoogledrive.blogspot.com/p/paid-projects.html

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,

Kishan.

iGoogleDrive