Google Sheets Script - changing the contents of a cell based on a given condition

1
votes

I'm attempting to set up a submit button that changes the contents of a cell based on the selection the user makes on another sheet.
[ Something along the lines of getrange(match()).setvalue() ]

I can't seem to find a way to identify the correct range with the script. Attached is my dummy spreadsheet.

Here's what I would like to happen: The user will select answers to question 1 and 2 on the 'List' sheet (cells A2 and B2 with cell validation). Once the user clicks the SUBMIT button on the 'List' sheet, the associated cell contents on the 'Master' sheet change to FALSE.

Currently, the 'List' sheet uses a timestamp in column C to populate the 'Database' Sheet with the options for validation. The script for making the submit button work has eluded me. Your help is greatly appreciated.

https://docs.google.com/spreadsheets/d/1JE3DWK7L0JKIt4XixfGjvA6qus5MlTMy9_hrqTRVOWg/edit?usp=sharing

1
google-apps-scriptgoogle-sheets

1 Answers

1
votes

I took a look at your spreadsheet and added some code:

function forButton() {
  //Logger.log('it ran!'); 
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var activeSheet = ss.getActiveSheet();
  var masterSheet = ss.getSheetByName('Master');

  var inputValues = activeSheet.getRange(2, 1, 1, 3).getValues();
  var secondInput = inputValues[0][1];
  var allColumnTwoVals = masterSheet.getRange(2, 2, 6, 1).getValues();
  allColumnTwoVals = allColumnTwoVals.join().split(",");

  Logger.log('secondInput: ' + secondInput);
  Logger.log('allColumnTwoVals: ' + allColumnTwoVals);

  var foundRow = allColumnTwoVals.indexOf(secondInput);
  masterSheet.getRange(foundRow+2, 3, 1, 1).setValue("TRUE");

  masterSheet.activate();
  Browser.msgBox("New Values Just Entered!", Browser.Buttons.YES_NO_CANCEL);
};

I assigned a script to the image of the button?

Assign Script