I made the following script to identify names that were not matching between two files. This works perfectly fine for the first loop, and it gets to the var prompt perfectly fine on the second loop, however whenever I try to complete the prompt on sheets it seemingly does nothing and just stalls there. It is not because of the input because if I do that input the first time it goes through fine. I must be missing something as to why this isn't looping properly. Any help would be greatly appreciated
function onEdit(e) {
startPoint();
}
function startPoint(){
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("README");
var cell = "N5";
var difference = sheet.getRange(cell).getValue().toFixed(2);
if (difference > 0){
yesDifference(difference);
}else noDifference(difference);
}
function yesDifference(num){
const ui = SpreadsheetApp.getUi()
const result = ui.alert(
'There is a difference of: ' +
num
+ '\nWould you like to solve the issue',
ui.ButtonSet.YES_NO)
if (result == ui.Button.YES){
findDifference(num);
}else{
return
}
}
function noDifference(num){
const ui = SpreadsheetApp.getUi()
const result = ui.alert(
'Tips are matching!');
return
}
function findDifference(num){
const ui = SpreadsheetApp.getUi();
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("README");
var missingNames = sheet.getRange("Z3:Z20").getValues();
for(var i = 0; i < missingNames.length; i++){
var person = missingNames[i].toString();
if(person.length > 1){
const result = ui.alert(
'I am not able to match:\n' + person + '\nbetween Harri and R365 would you like to try and find them?',
ui.ButtonSet.YES_NO);
if(result == ui.Button.YES){
findNameMatch(person);
}
}
}
return
}
function findNameMatch(name){
const ui = SpreadsheetApp.getUi();
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("README");
var allNames = sheet.getRange("A2:A100").getValues();
var filteredNames = [];
for(var i = 0; i < allNames.length; i++){
var person = allNames[i].toString();
if(!(person.length > 1)){
i = allNames.length;
}else{
if(!(filteredNames.includes(person))){
filteredNames.push(person);
}
}
}
var prompt = ui.prompt('Out of the following names:\n\n\n' + filteredNames.join('\r\n') + "\n\n\nPlease enter below which name is supposed to be " + name);
var fullName = prompt.getResponseText().toString();
var resp = ui.alert(fullName);
var firstName = fullName.substring(0, fullName.indexOf(' '));
var lastName = fullName.substring(fullName.indexOf(' ') + 1);
var originalFirst = name.substring(0, name.indexOf(' '));
var originalLast = name.substring(fullName.indexOf(' ') + 1);
var names = ui.alert(
'First Name: ' + firstName + "\nLast Name: " + lastName
)
changeName(originalFirst, firstName, originalLast, lastName);
startPoint();
}
function changeName(oldF, correctF, oldL, correctL){
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("365");
var allFNames = sheet.getRange("A2:A100").getValues();
var allLNames = sheet.getRange("B2:B100").getValues();
for(var i = 0; i < allFNames.length; i++){
var name = allFNames[i].toString();
var lastName = allLNames[i].toString();
if(!(name.length > 1)){
i = allFNames.length;
}else{
if((name === oldF) &&(lastName === oldL)){
var newFirst = "A" + (i + 2);
var newLast = "B" + (i + 2);
var newFNames = sheet.getRange(newFirst).setValue(correctF);
var newLNames = sheet.getRange(newLast).setValue(correctL);
const ui = SpreadsheetApp.getUi();
const result = ui.alert(
'The names have been changed at ' + newFirst + ", and " + newLast + " to " + correctF + ", and " + correctL);
i = allFNames.length;
}
}
}
return
}
I have created a spreadsheet with minimal data in it to recreate the issue. On the R365 Sheet if you edit one of the names it should trigger the function the same way
You want to update name of customers that aren't being matched with existing data but your loop is not working.
Consider this answer:
Differences
'onEdit(e)` is a Simple trigger and is timing out maximum execution time.
onEdit(e)
to something else, say, updateNames(e)
onEdit
trigger for the new function name.make use of Event Objects.
Logger.log(JSON.stringify(e))
immediately after updateNames(e)
.
startPoint(e)
and function startPoint(e){
In startpoint(e)
include a test for sheet, column and row, such as:
if (e.range.getSheet().getName() == "README" && e.range.rowStart == 5 && e.range.columnStart == 14 ){
error in var originalLast = name.substring(fullName.indexOf(' ') + 1);
var originalLast = name.substring(name.indexOf(' ') + 1);
delete startPoint();
from the last line of function findNameMatch(name){
for(var i = 0; i < missingNames.length; i++){
in function findDifference
startPoint()
restarts the entire function rather than allowing the "for" loop to play out.A number of Logger
statements have been included to track the values of variables, and the progress of routines. These slow processing and the OP may wish to display those that appear helpful at any point in time.
It is not clear whether the value in Column M on sheet "365" should be updated with the "difference".
function updateNames(e) {
// Logger.log(JSON.stringify(e)) // DEBUG
startPoint(e);
}
function startPoint(e){
// test for edited sheet and column and row
if (e.range.getSheet().getName() == "README" && e.range.rowStart == 5 && e.range.columnStart == 14 ){ // cell 'N5"
// edit is in correct place, continue processing
// Logger.log("DEBUG: edit was in N5 on Readme - continue processing")
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("README");
var cell = "N5";
var difference = sheet.getRange(cell).getValue().toFixed(2);
if (difference > 0){
yesDifference(difference);
}else{
noDifference(difference);
}
}
else{
// edit is NOT in correct place, stop processing
// Logger.log("DEBUG: edit was NOT N5 on Readme - stop processing")
return
}
}
function yesDifference(num){
const ui = SpreadsheetApp.getUi()
const result = ui.alert(
'There is a difference of: ' +
num
+ '\nWould you like to solve the issue',
ui.ButtonSet.YES_NO)
if (result == ui.Button.YES){
// solve the difference
// Logger.log("DEBUG: solve the difference")
findDifference(num);
}else{
// do nothing
// Logger.log("DEBUG: Do nothing")
return
}
}
function noDifference(num){
const ui = SpreadsheetApp.getUi()
const result = ui.alert(
'Tips are matching!');
return
}
function findDifference(num){
const ui = SpreadsheetApp.getUi();
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("README");
var missingNames = sheet.getRange("Z3:Z20").getValues();
for(var i = 0; i < missingNames.length; i++){
var person = missingNames[i].toString();
if(person.length > 1){
const result = ui.alert(
'I am not able to match:\n' + person + '\nbetween Harri and R365 would you like to try and find them?',
ui.ButtonSet.YES_NO);
if(result == ui.Button.YES){
// Logger.log("DEBUG: the name of the person is "+person)
findNameMatch(person);
}
}
}
return
}
function findNameMatch(name){
const ui = SpreadsheetApp.getUi();
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("README");
var allNames = sheet.getRange("A2:A100").getValues();
var filteredNames = [];
for(var i = 0; i < allNames.length; i++){
var person = allNames[i].toString();
if(!(person.length > 1)){
i = allNames.length;
}else{
if(!(filteredNames.includes(person))){
// Logger.log("DEBUG: filtered names doesn't include "+person+", so person added to filtered names")
filteredNames.push(person);
}
}
}
var prompt = ui.prompt('Out of the following names:\n\n\n' + filteredNames.join('\r\n') + "\n\n\nPlease enter below which name is supposed to be " + name);
var fullName = prompt.getResponseText().toString();
var resp = ui.alert(fullName);
// Logger.log("DEBUG: name chosen for findNameMatch = "+fullName)
var firstName = fullName.substring(0, fullName.indexOf(' '));
var lastName = fullName.substring(fullName.indexOf(' ') + 1);
var originalFirst = name.substring(0, name.indexOf(' '));
var originalLast = name.substring(name.indexOf(' ') + 1);
// Logger.log("DEBUG: findNameMatch: first name = "+firstName+", last name = "+lastName+", Original first = "+originalFirst+", Original last = "+originalLast)
var names = ui.alert(
'First Name: ' + firstName + "\nLast Name: " + lastName
)
changeName(originalFirst, firstName, originalLast, lastName);
}
function changeName(oldF, correctF, oldL, correctL){
// Logger.log("DEBUG: changename: oldF = "+oldF+", correct F = "+correctF+", oldL = "+oldL+", correct L = "+correctL)
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("365");
var allFNames = sheet.getRange("A2:A100").getValues();
var allLNames = sheet.getRange("B2:B100").getValues();
for(var i = 0; i < allFNames.length; i++){
var name = allFNames[i].toString();
var lastName = allLNames[i].toString();
// Logger.log("DEBUG: changeName: i:"+i+", name:"+name+", last name:"+lastName+", name length:"+name.length)
if(!(name.length > 1)){
i = allFNames.length;
}else{
if((name === oldF) &&(lastName === oldL)){
// Logger.log("DEBUG: name <> old F and last name <> oldL")
var newFirst = "A" + (i + 2);
var newLast = "B" + (i + 2);
// Logger.log("DEBUG: cells: newFirst:"+newFirst+", newlast:"+newLast)
var newFNames = sheet.getRange(newFirst).setValue(correctF);
var newLNames = sheet.getRange(newLast).setValue(correctL);
// Logger.log("DEBUG: Updated newfirst and newlast with correctF and correctL")
const ui = SpreadsheetApp.getUi();
const result = ui.alert(
'The names have been changed at ' + newFirst + ", and " + newLast + " to " + correctF + ", and " + correctL);
i = allFNames.length;
// Logger.log("DEBUG: i is set to allFnames length: "+i)
}else{
// Logger.log("DEBUG: if unsuccessful")
}
}
}
return
}
SAMPLE DATA - sheet "Harri" before
SAMPLE DATA - sheet "365: before
SAMPLE DATA - Person 2
SAMPLE DATA - Person 4
SAMPLE DATA - sheet "365" after
SAMPLE - sheet" Readme" after