-1

I have a Google Sheets workbook I use to track proofreading jobs. There is a Status sheet that uses queries to pull info about active jobs from the Job Log sheet and then does some calculations to show my progress.

Screenshot: Status Sheet w/ Sidebar

Link: View-Only Copy of Status Sheet

I have created a custom sidebar to choose a row by Priority (Column B) and then easily input the Pages Read (Column I) and Minutes Spent (Column L), but I don't know how to apply the new values to the sheet. I would also like to modify the sidebar to select the Priority (Column B) from a dropdown instead of entering the value.

Here is what I have for UpdateSidebar.html:

<!DOCTYPE html>
<html>

<head>
  <base target="_top">
</head>

<body bgcolor="#039EB2">
  <font face="arial" color="white">
    <b>Select a job by Priority and enter the updated values to apply.</b><br><br>

    <!-- Create input fields to accept values from the user -->

    Job Priority:<br>
    <input type="text" id="Priority"><br><br>
    <!-- I would like this to be a dropdown with values from B13:B instead of text input -->

    Pages Read:<br>
    <input type="text" id="Pages"><br><br>

    Minutes Spent:<br>
    <input type="text" id="Minutes"><br><br>

    <!-- Create a button to update values -->
    <button onclick='saveUpdate()'>Update Job Status</button><br><br><br>

    <script>
      function saveUpdate() {
        //Get the value of the input fields 
        var priority = document.getElementById("Priority").value
        var pages = document.getElementById("Pages").value
        var minutes = document.getElementById("Minutes").value

        //Log the value of input fields in the web browser console (usually used for debugging)
        console.log(priority, pages, minutes)

        //Send the values as arugments to the server side function
        google.script.run.applyName(priority, pages, minutes)
      }
    </script>

  </font>
</body>

</html>

And here is my current code:

function showSidebar() {
  var html = HtmlService
      .createHtmlOutputFromFile('UpdateSidebar')
      .setTitle('Status Update Sidebar')
      .setWidth(200);
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .showSidebar(html);
}


function applyName(priority, pages, minutes){
  var ss = SpreadsheetApp.getActive()
  var sheetStatus = ss.getSheetByName("Status")
  var sheetLog = ss.getSheetByName("Job Log")

  <!-- //Whatever needs to happen to:
  //Select row with priority in Column B
  //Apply pages to Column I
  //Apply minutes to Column L -->
}

I'm fairly new to coding in Google Sheets, so I would greatly appreciate any help. Thanks!

ncsushley
  • 27
  • 9

1 Answers1

0

How to input values elected from the sidebar into the selected row of the sheet?

  • You need to implement a functionality to establish in which row the selected priority is located - a good way to do it is with indexOf()
  • You need to select a cell in the desired column and the established row an set its value to the desired parameter

Modify your function applyName as following:

function applyName(priority, pages, minutes){

  var ss = SpreadsheetApp.getActive()
  var sheetStatus = ss.getSheetByName("Status")
  var sheetLog = ss.getSheetByName("Job Log")

  //Whatever needs to happen to:
  //Select row with priority in Column B
  // the following line selects a static range, it can be modifed to a dynamic one if necessary
  var firstRow = 14;
  var lastRow = 18;
  var columnBvalues = sheetStatus.getRange(firstRow, 2, lastRow-firstRow+1, 1).getValues();
  var valueArray = columnBvalues.map(function(column){
    return column[0].toString();
  });  
  var position = valueArray.indexOf(priority);
  Logger.log(valueArray);
  Logger.log(position);
  if (position > - 1){
    var selectedRow = firstRow + position;
    //Apply pages to Column I = Column 9
    sheetStatus.getRange(selectedRow, 9).setValue(pages);
    //Apply minutes to Column L
    sheetStatus.getRange(selectedRow, 12).setValue(minutes);
  }
}

How to implement a dropdown into an html sidebar and populate it with values from the sheet?

Please see this post where something very similar was implemented.

ziganotschka
  • 25,866
  • 2
  • 16
  • 33
  • The updated applyName function works perfectly to set the values in the specified columns of the selected row. Thank you! – ncsushley Feb 07 '20 at 15:29
  • However, I am not understanding how to apply the linked solution to my project to implement the dropdown with my Priority values. – ncsushley Feb 07 '20 at 15:30
  • The linked solution is using [scriptlets](https://developers.google.com/apps-script/guides/html/templates) which allow you to run Apps Script code within the html file and populate the [html dropdown](https://developers.google.com/apps-script/guides/html/templates) with options that you can retrieve from your spreadsheet with `getRange(...).getValues();` – ziganotschka Feb 07 '20 at 16:07