Google sheet + App script: use showDialog box to find and replace value

Question

Hi I'm using this script to show a dialog box so I can enter a value then hit the submit button and get the script newcc(num) to inserted the value entered into the box onto another worksheet in Column 'I' where the check value from Column 'A' matches but for some reason it wont work any help would be great here is my GS code

    function newcostcode(){

   showDialogCostCode()
   
   }
 

function newcc(num){
 
    var ss = SpreadsheetApp.openById('1kiDnEawj0--- ID----NfjHQ-zeBHSvQ');
    var ws =  ss.getSheetByName('Data');
    

if (newcc(num) !== ''){
      
       var check = SpreadsheetApp.getActive().getSheetByName('Ticket PDF').getRange('N2').getValue();
      
       ws.getRange(2, 1, ws.getLastRow() - 1, 1)
      .createTextFinder(check)
      .findAll()
      .pop()
      .offset(0, 8, 1, 1)//column I PO AFE
      .setValue(newcc(num))
    
  }
}



function showDialogCostCode() {
      var html = HtmlService.createHtmlOutputFromFile('updateCostCode')
      .setWidth(400)
      .setHeight(200);
       SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .showModalDialog(html, 'Update Cost Code ');

}

and here's part of my html code

                  <div class="form-group col-md-4">
                   
                      <label for="costcode">Enter New Cost Code</label>
                      <input type="text" id="CC" class="form-control  col-md-2" >
                     
                      <br>
                      
                      <button onclick="updateCC()" class="btn btn-primary  ">Submit</button>
                      
                      <script>
                      function updateCC(){
                      var newCC = document.getElementById("CC");
                      var num = newCC.value
                      google.script.run.newcc(num);
                      }
                      </script>
                  </div>

Jason E. Jason E. · Accepted Answer · 2020-12-17T18:43:33

You are trying to compare the value of a function newcc(num) instead of the passed parameter num only.

Solution

Use the passed variable num to replace newcc(num) in comparing values. Your newcc(num) function should look like this:

function newcc(num){
 
  var ss = SpreadsheetApp.openById('1kiDnEawj0--- ID----NfjHQ-zeBHSvQ');
  var ws =  ss.getSheetByName('Data');
  
  
  if (num !== ''){
    
    var check = SpreadsheetApp.getActive().getSheetByName('Ticket PDF').getRange('N2').getValue();
    
    ws.getRange(2, 1, ws.getLastRow() - 1, 1)
    .createTextFinder(check)
    .findAll()
    .pop()
    .offset(0, 8, 1, 1)//column I PO AFE
    .setValue(num)
    
  }
}

Google sheet + App script: use showDialog box to find and replace value

1 Answers

Solution