CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
KingOtta
KingOtta

Reputation: 35

How to make autofill a cell with datavalidation without changing the entire row on Google sheet?

I'm automating the appointment process at my company and I have been successfully implementing some google scripts with time trigger. Whenever someone takes an appointment on Calendly it creates at row with several information through Zapier. I then have a script with several functions that operates on the newly added rows. One function auto sort the new row based on the date column then two others functions fill two columns with a checkbox(FALSE) and a datavalidation based on a list of choice. All of those functions are time triggered, let's say 30 minutes. The problem is whenever the trigger happens it automatically checks the checkbox to TRUE for the entire column and to the first choice of the list for the entire datavalidation column. How can I solve that ?


var SORT_COLUMN_INDEX = 4;
var ASCENDING = true;
var NUMBER_OF_HEADER_ROWS = 1;
var activeSheet;

function autoSort() {
  console.log(sheet, activeSheet)
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var range = sheet.getDataRange();

  if (NUMBER_OF_HEADER_ROWS > 0) {
    range = range.offset(NUMBER_OF_HEADER_ROWS, 0);
  }
  range.sort( {
    column: SORT_COLUMN_INDEX,
    ascending: ASCENDING
  } );
}
// Fonction to automatically add data validation in column K
function setDataValidationComing() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var listOfChoices = ["Coming","Not Coming","Message Left", "Unreachable"]
var validation = SpreadsheetApp.newDataValidation().requireValueInList(listOfChoices).build();;
  sheet.getRange("K2").setDataValidation(validation);

var lr = sheet.getLastRow();
var fillDownRange = sheet.getRange(2, 11, lr-1);
sheet.getRange("K2").copyTo(fillDownRange);
}

// Fonction to automatically add checkbox for appointment honored in column L
function setCheckboxCame() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var validation = SpreadsheetApp.newDataValidation().requireCheckbox().build();
  sheet.getRange("L2").setDataValidation(validation);

var lr = sheet.getLastRow();
var fillDownRange = sheet.getRange(2, 12, lr-1);
sheet.getRange("L2").copyTo(fillDownRange);
}

Here is a screenshot of the google sheet. Google sheet screenshot

Thanks for your help, I've just started using Google Script a week ago !

Upvotes: 1

Views: 186

Answers (1)

Laura Dye
Laura Dye

Reputation: 284

The problem is whenever the trigger happens it automatically checks the checkbox to TRUE for the entire column and to the first choice of the list for the entire datavalidation column.

It is not actually checking it to true for the entire column. It is copying the value of the first row after the header, and applying that value to each checkbox in the column. The first row of data shows 'Coming' so if you run that script, it will apply Coming to all of them. If you change it to 'Not Coming', it would apply 'Not coming' to every row. This is because of this line:
sheet.getRange("K2").copyTo(fillDownRange);

You don't want to copy the value of K2, you want to only copy the validation. So that line should really be:

`sheet.getRange("K2").copyTo(fillDownRange, SpreadsheetApp.CopyPasteType.PASTE_DATA_VALIDATION, false);`

As for the checkbox, that's a little bit trickier, but the same concept applies:

function setCheckboxCame() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];

    // UPDATED THIS PART
    var validation = SpreadsheetApp.newDataValidation().requireCheckbox();
    validation.setAllowInvalid(false);
    validation.build();

    var lr = sheet.getLastRow();
    var fillDownRange = sheet.getRange(2, 12, lr-1);
    // CHANGE THIS:
    //sheet.getRange("L2").copyTo(fillDownRange);
    // TO THIS: 
    fillDownRange.setDataValidation(validation);

}

Upvotes: 3

Related Questions