Google Apps Script Expertise

Dump Data From Another Spreadsheet

function dataDump() {

  //adjust these values
  var sheetName = "Absence Tracker"; //add the name of the sheet (tab) where you’d like the data to dumped
  var ssURL = "https:/"; //add the url of the spreadsheet you’d like to pull from
  var sheet = "My Sheet"; //add the name of the sheet (tab) you’d like to pull from
  var lastCol = 3; //add the number of columns you’d like to pull

  //do not edit below this line
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName(sheetName);
  var pullFrom = SpreadsheetApp.openByUrl(ssURL).getSheetByName(sheet);
  var lastRow = pullFrom.getRange("A:A").getValues().filter(String).length;
  var data = pullFrom.getRange(1, 1, lastRow, lastCol).getValues();
  s.getRange(1, 1, lastRow, lastCol).setValues(data);
}

Control Forms with Sheets

function transferToForm() {

  //adjust these values
  var formURL = "https:/"; //add the url to your form in between the quotations
  var sheetName = "YOUR Data"; //add the name of your sheet (tab) that includes the list for the dropdown
  var columnOfNames = 1; //change number to column number where your list for the dropdown is located
  var itemNumber = 1; //what number is the question on the form? is it the first question? second?

  //do not edit below this line
  var form = FormApp.openByUrl(formURL);
  var ss = SpreadsheetApp.getActive();
  var studentList = ss.getSheetByName(sheetName);
  var lastRow = studentList.getRange(1, columnOfNames, 5000, 1).getValues().filter(String).length;
  var sslist = [];
  var itemlist = form.getItems();
  for (var i = 2; i <= lastRow; i++) {
    var student = [studentList.getRange(i, columnOfNames).getValue()];
    sslist.push(student);
  }
  itemlist[itemNumber - 1].asMultipleChoiceItem().setChoiceValues(sslist);
}

Send Email for Every Row

function sendEmails() {
  var ss = SpreadsheetApp.getActive();

  //adjust these values
  var sheet = "Sheet1"; //change to the sheet from which you'd like to pull emails
  var startrow = 2; //change number to the row you'd like to start checking
  var checkcol = 1; //if this column is empty, the email will not send
  var xcol = 20; //this should be an empty column; it will stamp an X in this column once the email has been sent
  var message = "How are you?"; //this will be the message that gets sent
  var subject = "Hello!"; //this will be the subject of the email
  var emailcol = 2; //this should be the column that has the emails listed

  //do not edit below this line
  for (i = startrow; i <= ss.getSheetByName(sheet).getLastRow(); i++) {
    var checkvalue = sheet.getRange(i, checkcol).getValue();
    var xvalue = sheet.getRange(i, xcol).getValue();
    if (checkvalue !== "" && xvalue == "") {
      var email = sheet.getRange(i, emailcol).getValue();
      MailApp.sendEmail(email, subject, message);
      sheet.getRange(i, xcol).setValue("X");
    }
  }
}