-1

I would like to use Google Sheets as a form to query for data, select and store the data I'm interested in.

Below what I'd like to try. Please let me know if feasible, and if so, could you tell how to?

  1. Automatically pull data I can do this, I can query an external API. Say data is imported in column A.

  2. Once data is pulled, I want to select only the cells I'm interested in. How could I? Possible to create a tick option, say in column B, for each imported element ?

The selected cells should be automatically appended to another column - say column C. This is the part I'd like to know if allowed by Google Sheet: I need to associate a function to a tick, to append data on a column, but I also need to persist that appended data.

Example, if I run another query at point 1 and column A is updated, I don't want elements in C to be replaced.

The thing could be of course done with a web site, but wonder if I could mimic a web app for productivity.

This is a mockup to show what I'd like to achieve: enter image description here

I import data on column A. I can create tickboxes in column B : how to bind them to rows in column A ? (pseudo code for column B: IF( ISTICKED(B1), A1, ISBLANK(TRUE)) )

I want to have cells in column C copied "by value" from column B and appended, I mean if I change column A, still column C kept former results, while if I am changing selections on column B, I can see changes in column C.

5
  • Welcome. You say that you can retrieve the data into Column A, then "I want to select only the cells I'm interested in". Would you please edit your question to include a sample of the data "pulled" into Column A - how many columns of data it represents, how the columns are separated, and which columns you are interested in. Would you also describe whether and how you have made an attempt to solve this question? – Tedinoz Apr 25 at 2:30
  • Thank you the extra information. I'm sure that a formula could be created to display the checked values BUT... you said "I want to have cells in column C copied "by value" from column B and appended". This rules out a formula because they are dynamic; the solution to your question requires Apps Script. It would also require more thought about appending, just so that you can avoid duplicates etc. – Tedinoz Apr 26 at 7:18
  • Thank you. I added a tag for apps script - hope to find some hint here. Not familiar with google apps script – user305883 Apr 26 at 8:39
  • @user305883 Regarding Google Apps Script, start by reading developers.google.com/apps-script/guides/sheets. Then try it and if you still need help edit the question to show what you tried and a brief description of your search efforts as is suggested in How to Ask – Rubén Apr 27 at 2:58
  • @user305883 There are lots of questions on StackOverflow/Webapps about copying checkboxed information but none with your exact scenario. Your answer needs to be tailored. Even questions like Google Sheets Log row to another sheet (same book) when checkbox checked contains helpful information BUT, let me stress, that answer is not a direct replacement for your scenario. If you are new to Apps Script, you could probably use some help. Perhaps then, you can identify the next steps in your script answer. – Tedinoz Apr 29 at 0:09
1

This is not an answer per se, but it is a guide that will assist the questioner to develop their script from a limited understanding of apps script coding.

There are two functions in the script:

  • onOpen - this will allow create a new menu Item called Custom menu. It is largely copied directly from Apps Script Guide on menus

  • copycheckedboxes - this is the raw shell of some code that:

    • gets the data in Columns A and B,
    • loops through the rows in Column A
    • for each row, tests whether the checkbox is checked (true)
  • if the checkbox is checked, there are a number of things to be done:

    • make sure that the value in Column A isn't already on Column C
    • copy Column A value to the next blank row on Column C
    • sort Column C by alpha
    • set the checkbox back to unchecked - false
    • do any other things yet not listed

This "to-do" list needs to be done efficiently. It's not suggested that each step on the list should be done in sequence (as shown in the code). Rather, the list is shown as a guide to the events that needs to be considered and appropriate answers/code should be developed.


function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('Copy Checkbox items', 'copycheckedboxes')
      .addToUi();
}

function copycheckedboxes() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sheet1=ss.getSheetByName("Sheet1");
  
  // get some variables
  var lastRow = sheet1.getLastRow();
  var checkedcopyarray = []
  
  // get the data
  var range = sheet1.getRange(1,1,lastRow,2).getValues();
  var values = range.getValues();
  

  Logger.log(values); // DEBUG
  Logger.log(values[0][0]); // DEBUG
  Logger.log(values[0][1]); // DEBUG

  // loop though column A 
  for (var i= 0; i<lastRow;i++){
    if (values[i][1] == true){
      Logger.log("row ="+(i+1)+" and checkbox is true - copy this item")
      // make sure that this isn't already on Column C
      // copy Column A value to the next blank row on Column C
      // sort Column C by alpha
      // set the checkbox back to unchecked - false
    }
    else{
      Logger.log("row ="+(i+1)+"and checkbox is false - do item")

    }

  }

}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.