Compare data in source sheet with target sheet and copy missing rows to target sheet with Google Apps Script
Source sheet has 8 columns and target sheet has 9 columns (note first 8 columns are the same, the 9th column is used to set a url link once it has been mailed out). I don't want the target sheet to be sorted. The url link has to be on the appropriate row it pertains to.
This is the code I am working with so far. It does pull and compare the data, but it keeps adding a blank row at the beginning and after each run. I can't figure out why it does that?
function addNewStudents() {
let ss = SpreadsheetApp.getActiveSpreadsheet()
let source = ss.getSheetByName('Accepted Students')
let sourceValues = source.getRange(2, 1, source.getLastRow(), 8).getValues().filter(String)
//sourceValues.shift()
//Logger.log(sourceValues)
let targetSheet = ss.getSheetByName('Confirmation Letters')
let targetValues = targetSheet.getRange(2, 1, targetSheet.getLastRow(), 8).getValues().filter(String)
//console.log(targetValues)
let diff = targetValues.showDif(sourceValues)
targetValues = (diff.length && diff) ? targetValues.concat(diff) : targetValues
// console.log(targetValues)
if (targetValues === '') {
var ui = SpreadsheetApp.getUi()
ui.alert("No new students")
} else {
targetSheet.getRange(2, 1, targetValues.length, targetValues[0].length).setValues(targetValues)
}
//console.log(targetValues.length)
}
Array.prototype.showDif = function (array) {
let that = this;
return array.filter(function (r) {
return !that.some(function (x) {
return r.join() === x.join()
})
})
}
Via Active questions tagged javascript - Stack Overflow https://ift.tt/m7Z9o1i
Comments
Post a Comment