0

I am a GAS newbie and I am trying to automatically update a monthly budget report from another sheet to keep tab on my project expenses.

Budget sheet example: https://docs.google.com/spreadsheets/d/1ggYjZmGBLnfP-Wzq5dFKpfjaOZj78lgJr8LAl2GtV-I/edit?usp=sharing

I am trying to build a code that can match data from 'Actual' sheet based on a project name such as 'Project1' and then copy and paste the data from 'Salary', 'Goods', 'Expenses' etc. to corresponding cells in the Budget sheet based on the same project name. The budget sheet gets new rows as new budgets are added for the projects. In case the correct project name is not found in the budget sheet, then this should go unnoticed. I get the actual numbers from our ERP and will update the 'Actual' sheet whenever I get a new report from the ERP.

I have built an extra menu button with 'update' which I hope would perform the above action. The code looks as below so far:

(add row function adds automatically last row to the table and retains the formulas)

// global 
var ss = SpreadsheetApp.getActive();

function onOpen() {
   var ui = SpreadsheetApp.getUi();
   var menu = ui.createMenu('Menu');
   var item = menu.addItem('New Row', 'addRow');
   var item2 = menu.addItem('Update', 'updateTable');
   item.addToUi();
}

function addRow() {
   var sh = ss.getActiveSheet(), lRow = sh.getLastRow(); 
   var lCol = sh.getLastColumn(), range = sh.getRange("InsertFormulaRangeHere");
   sh.insertRowsAfter(lRow -1, 1);
   range.copyTo(sh.getRange(lRow,1,1,lCol), {contentOnly:true})
  ;
}

function updateTable() {

   import
}

So far I have looked into getRange script and importange, vlookup and match formulas but I am lost.

Any advice how to go about this?

Nils Aslak
  • 1
  • 1
  • Welcome. I'm probably missing something here but... why not just use `vlookup` to get the value from Actual to the Budget sheet. For example, cell N25 = `=vlookup($A25,Actual!$A$2:$E$3,5,false)`. – Tedinoz Feb 01 '19 at 07:01
  • Hi @Tedinoz! Thank you for the reply. The problem with vlookup is that, I need to manually build the formulas for each and every cell that I want to look in the beginning. I am afraid that I will need to keep updating the formulas as I upload more data to the "Actual" sheet. The goal was to automate the data entry as much as possible. The optimal solution would be a formula/script that would scan the entire table for the values based on the project name and the expense label, then match these. – Nils Aslak Feb 08 '19 at 06:31
  • Also vlookup doesn't seem to like that the lookup value is text which leads total column formulas not to work. – Nils Aslak Feb 08 '19 at 07:38
  • Regarding `VLOOKUP`, it doesn't update the `SUBTOTAL` because that formula includes `INDIRECT`. Forewarned is forearmed - I suggest you read [Why does my Google Sheets formula not automatically recalculate?](https://stackoverflow.com/questions/46588906/). Also, FWIW, your "Budget" sheet name has a leading space; impossible to detect visually, but it kill any/all formula and script if you don't make allowances. Suggest you rename without a space. – Tedinoz Feb 09 '19 at 06:49
  • "GrossProfitActual%" contains the formula `=R25/R25`. This is probably meant to be 'P25/H25'. – Tedinoz Feb 09 '19 at 07:14
  • Regarding `"addRow"`. Is that working OK? What does `"InsertFormulaRangeHere"` refer to? Regarding `"updateTable"`. This is obviously a work-in-progress. Let's ignore detailed code for the moment and focus on the process flow. Would you please edit your question to describe the step-by-step processes that you would take to update the Budget table from the Actual table. – Tedinoz Feb 09 '19 at 07:26

0 Answers0