How can I automate input using checkbox?

Question

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.

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?

Check the Simple Triggers function of apps scripts developers.google.com/apps-script/guides/triggers — Ping
– Ping, Commented Nov 7, 2022 at 7:55

Wicket · Accepted Answer · 2022-12-16 02:49:21Z

1

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();
    }
  }
}

edited Dec 16, 2022 at 2:49

Wicket

39.6k9 gold badges81 silver badges201 bronze badges

answered Nov 7, 2022 at 8:26

Ping

9111 gold badge5 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user20403203 Over a year ago

Thank you so much for the reply. I have tried out the code you told me but it doesn't seem to work. I might have to look more into it to figure out what the entire thing means. Adding on, I have tried out the method of XLOOKUP but I already have some apps script code written down that automatically inputs data into C9, C11 and C13. Which, when this happens, all formats and function originally inputted seems to disappear. I don't know how to make the function and the format of the cell to stay there permanently, so if you know the answer a little help on that would also be great.

Ping Over a year ago

"I don't know how to make the function and the format of the cell to stay there permanently", short answer, you can't, but you can use apps script to handle this, like values, apps script can also set formula and format into a given range.

Collectives™ on Stack Overflow

How can I automate input using checkbox?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related