CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Derrick M
Derrick M

Reputation: 11

Google script to change a cell value based on another cell that contains a formula

I made this sheet to create invoices to send out for the work that we do.

The main tab (Invoice) is what we actually print out and send. Basically every cell on the invoice tab has a formula in it to pull information from the other 2 tabs (paperless, and clients) to actually populate the invoice. You choose a client from the dropdown menu, and then the bill to information is populated using the information from the clients tabs, and the rest of the invoice is populated using the information from the paperless tab.

All of the information in the paperless tab (dates of service, hours worked, miles driven, service code, etc.) is populated from an external source, our timeclock.

I already have formulas in the invoice tab that do 90% of the work for me, they may not be the most elegant formulas, but they do the job. Up until recently, they did 100% of the work, but there has been a change to the billing rates for a certain service code, and that's where I have the issue.

You can see under billing rate in ColI I have the following formula

=IF(B12="1:1 ADL (OR526)", "$32.23", IF(B12="2:1 ADL (OR526-ZE)","$64.46", IF(B12="1:1 at DSA Event (OR542-R1)", "$32.23", IF(B12="Group DSA (OR542-W2)", "$18.61","")))

Not pretty, but it's simple and it used to work fine. Now the issue I have though is the service code "Group DSA (OR542-W2)" has multiple rates that vary depending on client. The rates are as follows $18.06, $18.61, $20.15, $22.03, $24.29, and $27.11.

So my question is, what is the best way I can approach this? I'm terrible at the scripting side of things, but I found the following script in an older thread and tried to appropriate it, but I don't know how to add the other service codes to it, and it acts pretty wonky. It doesn't clear the validation for the blank entries when switching between clients, and sometimes doesn't input the correct values.

function addValidation() {

  // Get the spreadsheet and active sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  // Get the data as an Array you can iterate and manipulate
  var data = sheet.getDataRange().getValues();

  // Store a rule to use for the Data Validation to be added if ColB == "Group DSA (OR542-W2)"
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(['$18.06', '$18.61', '$20.15', '$22.03', '$24.29', '$27.11']).build();

  // Loop the sheet
  for(var i=0; i<data.length; i++) {

    // Test ColB. Note that the array is 0-indexed, so A=0, B=2, etc...
    // To change which columns you're testing, change the second value.
    if(data[i][1] == "Group DSA (OR542-W2)") {

      // If it's "Group DSA (OR542-W2)," add the Data Validation rule to Col I for that row.
      // Note that .getRange() is _not_ 0 indexed, which is why you need `i+1` to get the correct row

      sheet.getRange(i+1, 9).clear().setDataValidation(rule);

      // If ColB == "1:1 ADL (OR526)," mark ColI as "32.23"
    } else if(data[i][1] == "1:1 ADL (OR526)") {
      sheet.getRange(i+1, 9).clearDataValidations().setValue("32.23");
    }
  }
}

I also tried using this code, but it didn't work at all, which I'm assuming is because I have a formula in B2 because when I deleted the formula and entered the service code manually it did work.

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var s=ss.getActiveSheet()
  var nota=e.range.getA1Notation()
  if(nota=="B12"){
  var val=e.range.getValue()
    if(val=="1:1 ADL (OR526)"){
      s.getRange("I12").setDataValidation(null)
      s.getRange("I12").setValue("$32.23")
  }
      else{
  s.getRange('I12').clearContent()      
  var cell = s.getRange('I12');
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(['$18.06', '$18.61', '$20.15', '$22.03', '$24.29', '$27.11']).build();
  cell.setDataValidation(rule);
   }}}

I'm not a coder, I know very basic python and Java, but nothing that is of any help here. I can usually mash a bunch of formulas together to make something work, but in this case I'm just at a loss. I've been banging my head against a wall for several days now. If there's anyone out there willing to help me out, or even point me in the right direct of some materials to read which could help me out, I would really appreciate it.

Thank you in advance.

Upvotes: 1

Views: 110

Answers (1)

jcommando
jcommando

Reputation: 132

I would suggest using the formula IFS in ur formula

You can take a look at this article for help regarding the IFS formula , https://support.google.com/docs/answer/7014145

...IF(B12="Group DSA (OR542-W2)", IFS(condition1,"$18.06",condition2,"$18.61",condition3,"$20.15")

You should create a table to list which clients are classified under which rates under the service "Group DSA (OR542-W2)", then by using VLOOKUP to obtain their name and their rates will be available easily.

Thus, this simplify the work for you as you will not have to use Apps Script to do it.

Upvotes: 1

Related Questions