CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetscheckboxtriggers
user20403203
user20403203

Reputation: 19

How can I automate input using checkbox?

I am currently working on a project for managing inventory to keep track of the materials we have in school.
This page is used for teachers to input material information for the ones they wish to borrow. I used the datalabs.org for a reference and now I want to add additional features.

What I have to do looks like the following.

Image of my project

I have an input box for teachers to put down their Name. Using cell C7, SEARCH will be available to view all history catalog of BORROW and RETURN with the name typed in C7. What I want to do are two things.

FIRST: make corresponding number of checkboxes appear in column A. For example, if I search 4 logs under the name Ben, there will only be 4 checkboxes appearing in column A. If I search 10 logs under the name Bob, there will be 10 checkboxes appearing in column A (next to the logs). I have no idea how to make this possible....

SECOND: Once the checkbox is ticked, I want the ticked row to automatically fill in cell C9, C11 and C13. For this to happen, i also will need to limit the maximum number of ticked box to be 1 for Column A.

Can someone please help me out with this?

Upvotes: 0

Views: 145

Answers (1)

Ping
Ping

Reputation: 911

Try this script:

function onEdit(e) {
  const sheet = SpreadsheetApp.getActiveSheet();
  const sName = sheet.getName();

  if (sName === 'yourSheetName' && e.range.getColumn() === 2 && e.range.getRow() > 19) {
    const len = sheet.getRange('B20:B').getValues()
     .filter(row => row[0] != '').length;
    const range = sheet.getRange(20,1,len,1);
    sheet.getRange('A20:A').removeCheckboxes();
    range.insertCheckboxes().uncheck();
  }
}

!! Do not change the function name. !! This is a simple onEdit trigger for google sheet apps script. It will monitor any edit event, and if the sheet being edited is named as 'yourSheetName', and the edited range is Column B and Row > 19,

The function will 1st delete all checkbox from A20:A, then check the values in B20:B and count the length of cells which is not empty, and apply the same amount of checkboxes at A20:A.

you can also modify this code to fit it into your search function, instead of onEdit trigger, just run it when everytime search function is triggered, would also be a way to go.

To get the selected data displayed at C9, C11, C13, you won't need appscript, a checked checkbox equals to TRUE in googlesheet, just put an XLOOKUP formula in the three cells, lookup for true value in A Column, and return the related result should get the job down.

But you will need an onEdit trigger to keep A Column clean, which when a new row is seleted, the other checkboxes need to be unchecked.

An easier way to get this done, is uncheck all boxes in A20:A and check only the event.range, combimed with the 1st fuction, will be something like this:

function onEdit(e) {
  const sheet = SpreadsheetApp.getActiveSheet();
  const sName = sheet.getName();

  if (sName === 'yourSheetName' && e.range.getRow() > 19) {
    const len = sheet.getRange('B20:B').getValues().filter(row => row[0] != '').length;
    if (e.range.getColumn() === 2) {
      const range = sheet.getRange(20,1,len,1);
      sheet.getRange('A20:A').removeCheckboxes();
      range.insertCheckboxes().uncheck();
    }
    if (e.range.getColumn() === 1 && e.range.getValues()[0][0] === true) {
      sheet.getRange('A20:A').uncheck();
      e.range.check();
    }
  }
}

Upvotes: 1

Related Questions