Pulling Row Data Into a Google Sheets Sidebar then replacing new entered data into original row sele-CodePudding

I am currently trying to modify a already written code to be able to pull in row data of certain columns into the Sidebar then after the user submits it updates the row that was selected.

I have been able to achieve pulling the exact Row columns into an array but have not been able to print them as values in the Sidebar nor update the selected row. I have included all of my Sidebar code some of it may be unnecessary thank you all for your help.

SideBar Code.gs

//CREATE CUSTOM MENU
function onOpen() { 
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("User Input SideBar Menu")
    .addItem("Constr.Sidebar Form","showFormInSidebar")
    
    .addToUi();
}
 function onSelectionChange(e) {
  
  // const range = e.range;
  // var row = range.getActiveRowIndex();
  // var address = getRange(row,1).getValues();
  // var pco = getRange(row,26).getValues();
  // var psi = getRange(row,27).getValues();
  // var pdi = getRange(row,40).getValues();
  // var pdo = getRange(row,41).getValues();
  // var qa_inspection = getRange(row,63).getValues();
  // var qa_bonus = getRange(row,64).getValues();
  // var qai_super = getRange(row,65).getValues();
  // var qa_cert = getRange(row,66).getValues();
  // var qac_bonus = getRange(row,67).getValues();

  // document.getElementById('PCO').value = pco;
  // document.getElementById('PSI').value = psi;
  // document.getElementById('PDI').value = pdi;
  // document.getElementById('PDO').value = pdo;
  // document.getElementById('QAInspectDate').value = qa_inspection;
  // document.getElementById('QAOTBONUS').value = qa_bonus;
  // document.getElementById('QAISuper').value = qai_super;
  // document.getElementById('QACertDate').value = qa_cert;
  // document.getElementById('QACBonus').value = qac_bonus;

getValuesOfActiveRow();
document.getElementById('PCO').setValue(vs[0]);
}
//OPEN THE FORM IN SIDEBAR 
function showFormInSidebar(vs) {      
  var form = HtmlService.createTemplateFromFile('Index').evaluate().setTitle( 'Details');
  SpreadsheetApp.getUi().showSidebar(form);
}

//PROCESS FORM
function processForm(formObject){ 
  var sheet = SpreadsheetApp.getActiveSheet();
  // Logger.log([formObject.first_name]);
  sheet.appendRow([
    formObject.SPACE,
    formObject.SPACE,
    formObject.SPACE,
    formObject.SPACE,
    formObject.SPACE,
    formObject.SPACE,
    formObject.SPACE,
    formObject.SPACE,
    formObject.SPACE,
    formObject.SPACE,
    formObject.SPACE,
    formObject.SPACE,
    formObject.SPACE,
    formObject.SPACE,
    formObject.SPACE,
    formObject.SPACE,
    formObject.SPACE,
    formObject.SPACE,
    formObject.SPACE,
    formObject.SPACE,
    formObject.SPACE,
    formObject.SPACE,
    formObject.SPACE,
    formObject.SPACE,
    formObject.SPACE,
                formObject.PCO,
                formObject.PSI,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.PDI,
                formObject.PDO,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                formObject.SPACE,
                
                formObject.QAInspectDate,
                formObject.QAOTBONUS,
                formObject.QAISuper,
                formObject.QACertDate,
                formObject.QACBonus
                
                //Add your new field names here
                ])
               ;
}

//INCLUDE HTML PARTS, EG. JAVASCRIPT, CSS, OTHER HTML FILES
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

// new code
function getValuesOfActiveRow() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('LC 2022V2');
  let vs = sh.getRange(sh.getActiveRange().getRow(),1,1,sh.getLastColumn()).getValues().map(r => [r[0], r[25], r[26], r[48], r[49], r[88], r[89], r[90], r[91], r[92]]);
  Logger.log(vs);
  return vs[0];
  
  
}

function saveValuesActiveRow(formObject) {
  //Logger.log(form);
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('LC 2022V2');
  //sh.getRange(sh.getCurrentCell().getRow(), 1, 1, sh.getLastColumn()).setValues([[form.PCO,form.PSI,form.PDI,form.PDO,form.QAInspectDate,form.QAOTBONUS,form.QAISuper,form.QACertDate,form.QACBonus]])
sh.getRange(sh.getCurrentCell().getRow(),26).setValues(formObject.PCO);

}

Index.html

<!doctype html>
<html lang="en">
  <head>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=yes">
    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css" integrity="sha384-9aIt2nRpC12Uk9gS9baDl411NQApFmC26EwAOH8WgZl5MYYxFfc NcPb1dKGj7Sk" crossorigin="anonymous">
    <?!= include('JavaScript'); ?> <!-- See JavaScript.html file -->
    <title>Contact Details</title>
  </head>
  
  <body >
    <div >
      <?!= include('Form'); ?> <!-- See Form.html file -->
    </div>
    <!-- Optional JavaScript -->
    <!-- jQuery first, then Popper.js, then Bootstrap JS -->
    <script src="https://code.jquery.com/jquery-3.5.1.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C OGpamoFVy38MVBnE IbbVYUew OrCXaRkfj" crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/umd/popper.min.js" integrity="sha384-Q6E9RHvbIyZFJoft 2mJbHaEWldlvI9IOYy5n3zV9zzTtmI3UksdQRVvoxMfooAo" crossorigin="anonymous"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js" integrity="sha384-OgVRvuATP1z7JjHLkuOU7Xw704 h835Lr 6QL9UvYjZE3Ipu6Tp75j7Bh/kR0JKI" crossorigin="anonymous"></script>
  </body>
</html>

JavaScript.html

<script>
  // Prevent forms from submitting.
  function preventFormSubmit() {
    var forms = document.querySelectorAll('form');
    for (var i = 0; i < forms.length; i  ) {
      forms[i].addEventListener('submit', function(event) {
      event.preventDefault();
      });
    }
  }
  window.addEventListener('load', preventFormSubmit);    
      
      
  function handleFormSubmit(formObject) {
    //google.script.run.processForm(formObject);
    google.script.run.saveValuesActiveRow(formObject);
    document.getElementById("myForm").reset();
  }
</script>

Form.htm

    <form id="myForm" onsubmit="handleFormSubmit(this)">
      <div >
        <label for="PCO">PCO</label>
        <input  type="text"  id="PCO" name="PCO" placeholder="PCO" value="<?= row[0] ?>"/>
      </div>
      <div >
        <label for="PSI">PSI</label>
        <input  type="text"  id="PSI" name="PSI" placeholder="PSI" value="<?= row[1] ?>"/>
      </div>
      <div >
        <label for="PDI">PDI</label>
        <input  type="text"  id="PDI" name="PDI" placeholder="PDI" value="<?= row[2] ?>"/>
      </div>
      <div >
        <label for="PDO">PDO</label>
        <input  type="text"  id="PDO" name="PDO" placeholder="PDO" value="<?= row[3] ?>"/>
      </div>
      <div >
        <label for="QAInspectDate">QA Inspection Date</label>
        <input  type="date"  id="QAInspectDate" name="QAInspectDate" placeholder="QAInspectDate" value="<?= row[4] ?>"/>
      </div>
      <div >
        <label for="QAOTBONUS">QA OT BONUS</label>
        <select  id="QAOTBONUS" name="QAOTBONUS" value="<?= row[5] ?>"/>
          <option value="" selected>Choose...</option>
          <option value="Fail1st">Fail 1st</option>
          <option value="Fail2nd">Fail 2nd</option>
          <option value="Fail3rd">Fail 3rd</option>
          <option value="Pass1st">Pass 1st</option>
          <option value="Pass2nd">Pass 2nd</option>
          <option value="Pass3rd">Pass 3rd</option>
        </select>
      </div>
       <div >
        <label for="QAISuper">QAI Super</label>
        <input  type="text"  id="QAISuper" name="QAISuper" placeholder="QAISuper" value="<?= row[6] ?>"/>
      </div> 
      <div >
        <label for="QACertDate">QA Cert Date</label>
        <input  type="date"  id="QACertDate" name="QACertDate" placeholder="QACertDate" value="<?= row[7] ?>"/>
      </div>
      <div >
        <label for="QACBonus">QAC Bonus</label>
        <input  type="text"  id="QACBonus" name="QACBonus" placeholder="QACBonus" value="<?= row[8] ?>"/>
      </div> 
       <button type="submit" >Submit</button>
    </form>

CodePudding user response：

Description

I think the difficulty in you scenario is your spreadsheet is so long (< 50 columns) you have difficulty editing certain columns. So instead you want a dialog with the column values that you usually have to edit in a more concise form.

Here is an example of how to use HTMLTemplate to get the values of a selected row to a custom dialog, edit the values and return the new values to the spreadsheet.

I use the mouseenter event to determine if the user leaves the sidebar to pick a new row and returns to the sidebar and updates the values from the selecte row.

Here is my test sheet with sidebar before changing any values. Change a value and hit the submit button to see what happens.

Code.gs

function onOpen(e) {
  let ui = SpreadsheetApp.getUi();
  let menu = ui.createMenu("Test");
  menu.addItem("Show SideBar","showSideBar");
  menu.addToUi();
}

function showSideBar() {
  try {
    let html = HtmlService.createTemplateFromFile("HTML_SideBar");
    html.data = getSelectedRow();
    html = html.evaluate();
    SpreadsheetApp.getUi().showSidebar(html);
  }
  catch(err) {
    SpreadsheetApp.getUi().alert(err);
  }
}

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .getContent();
}

function getSelectedRow() {
  let cell = SpreadsheetApp.getCurrentCell();
  let sheet = cell.getSheet();
  let row = sheet.getRange(cell.getRow(),1,1,sheet.getLastColumn()).getValues()[0];
  row.forEach( (col,index) => { if( col instanceof Date ) row[index] = Utilities.formatDate(col,"GMT","MM/dd/yyyy") } );
  return JSON.stringify({ range: cell.getA1Notation(), row: row });
}

function setSelectedRow(row) {
  let cell = SpreadsheetApp.getCurrentCell();
  let sheet = cell.getSheet();
  sheet.getRange(cell.getRow(),1,1,sheet.getLastColumn()).setValues([row]);
}

HTML_Sidebar.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <div id="mainContainer">
      <? var row = JSON.parse(data) ?>
      <label for="currentRange">Current Range:</label>
      <input id="currentRange" type="text" value="<?= row.range ?>">
      <label for="column1">Column 1:</label>
      <input id="column1" type="text" value="<?= row.row[0] ?>">
      <label for="column2">Column 2:</label>
      <input id="column2" type="text" value="<?= row.row[1] ?>">
      <label for="column3">Column 3:</label>
      <input id="column3" type="text" value="<?= row.row[2] ?>">
      <label for="column4">Column 4:</label>
      <input id="column4" type="text" value="<?= row.row[3] ?>">
      <label for="column5">Column 5:</label>
      <input id="column5" type="text" value="<?= row.row[4] ?>">
    </div>
    <input id="submitButton" type="button" value="Submit changes" onclick="submitOnClick()">
    <?!= include("JS_SideBar"); ?>
  </body>
</html>

JS_SideBar.html

<script>
  function mainContainerMouseEnter() {
    try {
      google.script.run.withSuccessHandler(
        function (row) {
          row = JSON.parse(row);
          document.getElementById("currentRange").value = row.range;
          document.getElementById("column1").value = row.row[0];
          document.getElementById("column2").value = row.row[1];
          document.getElementById("column3").value = row.row[2];
          document.getElementById("column4").value = row.row[3];
          document.getElementById("column5").value = row.row[4];
        }
      ).withFailureHandler(
        function (err) {
          alert(err);
        }
      ).getSelectedRow();
    }
    catch(err) {
      alert("Error in mainContainerMouseEnter: " err);
    }
  }

  function submitOnClick() {
    try {
      row = [];
      row[0] = document.getElementById("column1").value;
      row[1] = document.getElementById("column2").value;
      row[2] = document.getElementById("column3").value;
      row[3] = document.getElementById("column4").value;
      row[4] = document.getElementById("column5").value;
      google.script.run.withFailureHandler(
        function (err) {
          alert(err);
        }
      ).setSelectedRow(row);
    }
    catch(err) {
      alert("Error in submitOnClick: " err);
    }
  }

  (function () {
    document.getElementById("mainContainer").addEventListener("mouseenter",mainContainerMouseEnter);
  })();
</script>

References