Google-sheets – Google Spreadsheets Data Management

google apigoogle sheetsuser interface

I am looking for a way to manage the input of some data in Google Spreadsheets. Before I roll up my sleeves and write a new UI using the Google API I thought I would see if there was anything out there that does what I need.

The data is sorted by the name column, and the other columns are information about the person that needs to be updated from time to time (because it needs to be updated periodically I don't think a Google Form will work). I want a way to enter in a name and then be prompted to update the other fields in the row, as well as check to make sure the inputted data is of the right form.

Is there anything out there I might want to look into?

Best Answer

I'd use a script in the spreadsheet, see this example.

The script:

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSheet(); //Get the active sheet

  var sourceRange = e.source.getActiveRange(); //Get currently edited cell
  var sourceRow = sourceRange.getRow();        //Get row of currently edited cell
  var sourceColumn = sourceRange.getColumn();  //Get column of currently edited cell
  var sourceValue = sourceRange.getValue();     //Get value of currently edited cell 

  if(sourceColumn == 1 && sourceValue != ''){ // If column 1 is edited and has a value (change number if you have name in another column)
    var name = sourceValue; // Name is value of edited cell (this is not necessary, only used to show name in questions below)

    var age = setData(name, 'How old is ', '? (only numbers)', '^[0-9]*$', true); // Run function setData() (see below) and save return to variable 'age'
    if(!age){
      return false;
    }
    sheet.getRange(sourceRow, 2).setValue(age); //set value in currently edited row, column 2 to inserted age

    // Do the same as above for title and other (you can of course replace these with any columns you have
    var title = setData(name, 'What is ', '\'s title?', false, true);
    if(!title){ // if user canceled question (cross in top right corner), stop execution
      return false;
    }
    sheet.getRange(sourceRow, 3).setValue(title);

    var other = setData(name, 'Other info about ', '?', false, false);
    if(!other){
      return false;
    }
    sheet.getRange(sourceRow, 4).setValue(other);

    var otherNum = setData(name, 'Other info about ', '? (only numbers)', '^[0-9]*$', false);
    if(!otherNum){
      return false;
    }
    sheet.getRange(sourceRow, 5).setValue(otherNum);
  }
}

//Function used for each column
// Parameters:
//    Name: name of person edited, only used to show name in quesion
//    question1: Question text shown before name (set to '' if none)
//    question2: Question text shown after name (set to '' if none)
//    format: regular expression for what the text can contain
//    required: set to true if user has to enter data, and false if nu input is necessary. When set to true, the popup will just return if you try to pass it without data
function setData(name, question1, question2, format, required){
  var data = false;

  if(required){ // If current data is required:
    while(!data){ // If nothing is entered, or question is canceled (cross in top right corner), keep looping
      data = Browser.inputBox(question1 + name + question2); // Show input popup asking for current data

      if(data == 'cancel'){ // if user canceled question (cross in top right corner), stop execution
        return false;
      }

      if(format){ // If a format rule was passed, check it
        var regEx = new RegExp(format); //Define regex criteria
        if(!regEx.test(data)) { // Check if string matches regex criteria
          data = false;
        }
      }
    }

    // If current data isn't required
  }else{
    data = Browser.inputBox(question1 + name + question2); // Popup input
    if(!data){ // If nothing was entered, set data to empty string
      data = '';

      if(data == 'cancel'){
        return false;
      }

    }else{ // Something was entered
      if(data == 'cancel'){
        return false;
      }

      if(format){ //If format rule was entered, check it
        var regEx = new RegExp(format);

        do{ // If something is entered, and regex test didn't pass, keep looping
          if(!regEx.test(data) && data) { 

            data = Browser.inputBox(question1 + name + question2); // Popup input
          }
        }while(!data)

        if(!data){
          data = '';
        }
      }
    }
  }
  return data;
}

Hopefully my comments in the code describes most of it. And you can test it in the example sheet.

The main thing is the setData function, you use it for every column. It displays a prompt where you can enter data. As described in the comments, you have parameters for if the field is required (which wont let the user pass an empty field) and if the field has any formatting requirements, (using regex), pass the regexpresion you want to use to check the formats.

Related Topic