GOOGLE SHEET CUSTOM CODE EDIT
$10-30 USD
Completato
Pubblicato 4 mesi fa
$10-30 USD
Pagato al completamento
I have a custom code that copies cell values in to other sheets and the other part does a lookup from "customer contact list" column h (if NOT ACTIVE YET) then it takes column A in the same sheet and then looks at the "Main" sheet column a and appends to the next cell which doesnt have data ONLY if it doesnt exist in the "Main" column a. So this second part is working fine however only when MANUALLY TRIGGERED. The onedit function is a simple trigger which doesnt work with dynamic updates.
The requirement is to edit my code to be simpler and to get on screen share with me while we solve it.
function onEdit(e) {
var ss = [login to view URL]();
var ssName = [login to view URL]();
if (ssName == "Main") {
copyCompany();
} else if (ssName == "Corporate Tax" || ssName == "LLC Tax" || ssName == "SalesTax" || ssName == "PayrollTax" || ssName == "Bookkeeping" || ssName == "MonthlyDeposits") {
updateModifiedDate();
} else if (ssName == "Customer Contact List" && [login to view URL]() == 8 && [login to view URL] == "NOT ACTIVE YET") {
[login to view URL]("Editing in Customer Contact List. Column: " + [login to view URL]() + ", Value: " + [login to view URL]);
appendToMain(e); // Call a new function to handle appending to Main
}
}
function copyCompany() {
var mainSheet = [login to view URL]().getSheetByName("Main");
var mainSheetColA = [login to view URL]("A1:A1000").getValues();
var salesTaxSheet = [login to view URL]().getSheetByName("SalesTax");
[login to view URL]("A1:A1000").setValues(mainSheetColA);
var payrollTaxSheet = [login to view URL]().getSheetByName("PayrollTax");
[login to view URL]("A1:A1000").setValues(mainSheetColA);
var corporateTaxSheet = [login to view URL]().getSheetByName("Corporate Tax");
[login to view URL]("A1:A1000").setValues(mainSheetColA);
var llcTaxSheet = [login to view URL]().getSheetByName("LLC Tax");
[login to view URL]("A1:A1000").setValues(mainSheetColA);
var bookkeeping = [login to view URL]().getSheetByName("Bookkeeping");
[login to view URL]("A1:A1000").setValues(mainSheetColA);
var mdeposits = [login to view URL]().getSheetByName("MonthlyDeposits");
[login to view URL]("A1:A1000").setValues(mainSheetColA);
}
function updateModifiedDate() {
var ss = [login to view URL]();
var r = [login to view URL]();
var columnNumber = [login to view URL]();
var rowNumber = [login to view URL]();
if (((columnNumber == 2 || columnNumber == 4 || columnNumber == 6 || columnNumber == 8 || columnNumber == 10 || columnNumber == 12 || columnNumber == 14 || columnNumber == 16 || columnNumber == 18 || columnNumber == 20 || columnNumber == 22 || columnNumber == 24) && ( [login to view URL]()=="SalesTax" || [login to view URL]()=="MonthlyDeposits" ))) {
[login to view URL](rowNumber, columnNumber + 1).setValue(new Date()).setNumberFormat("MM/dd/yyyy hh:mm");
} else if ((columnNumber == 2 || columnNumber == 4 || columnNumber == 6 || columnNumber == 8) && ( [login to view URL]()=="PayrollTax" || [login to view URL]()=="LLC TAX" )) {
[login to view URL](rowNumber, columnNumber + 1).setValue(new Date()).setNumberFormat("MM/dd/yyyy hh:mm");
}
}
function appendToMain(e) {
[login to view URL]("Editing in Customer Contact List. Column: " + [login to view URL]() + ", Value: " + [login to view URL]);
if ([login to view URL] != "NOT ACTIVE YET") {
return; // Exit if the edited value is not "NOT ACTIVE YET"
}
var mainSheet = [login to view URL]().getSheetByName("Main");
var sourceValue = [login to view URL](0, -7).getValue(); // Get value from column A in the same row
// Check if the value already exists in Main sheet
var columnAValues = [login to view URL]('A:A').getValues();
for (var i = 0; i < [login to view URL]; i++) {
if (columnAValues[i][0] === sourceValue) {
[login to view URL]("Value already exists in Main sheet.");
return; // Exit if value is found
}
}
// Append to the first empty cell in column A of Main
var firstEmptyRow = findFirstEmptyRow(mainSheet);
if (firstEmptyRow !== -1) {
[login to view URL](firstEmptyRow, 1).setValue(sourceValue);
[login to view URL]("Data appended successfully at row " + firstEmptyRow);
} else {
[login to view URL]("No empty cell found in Main column A.");
}
}
function findFirstEmptyRow(sheet) {
var columnA = [login to view URL]('A:A').getValues();
for (var i = 0; i < [login to view URL]; i++) {
if (columnA[i][0] == "" || columnA[i][0] == null) {
return i + 1;
}
}
return -1;
}
function processHourlyUpdates() {
var customerContactSheet = [login to view URL]().getSheetByName("Customer Contact List");
var dataRange = [login to view URL]();
var data = [login to view URL]();
for (var i = 0; i < [login to view URL]; i++) {
var row = data[i];
var status = row[7]; // Assuming status is in column H
if (status == "NOT ACTIVE YET") {
// Simulate an edit event object
var simulatedEvent = {
range: [login to view URL](i + 1, 8),
value: status
};
appendToMain(simulatedEvent);
}
}
// Call copyCompany() if necessary
copyCompany();
}
function createHourlyTrigger() {
[login to view URL]('processHourlyUpdates')
.timeBased()
.everyMinutes(1)
.create();
}
Rif. progetto: 37657682
Info sul progetto
25 proposte
Progetto a distanza
Attivo 4 mesi fa
Hai voglia di guadagnare un po'?
I vantaggi delle offerte su Freelancer
Imposta il tuo budget e le scadenze
Fatti pagare per il lavoro svolto
Delinea la tua proposta
La registrazione e le offerte sui lavori sono gratuite
6,5
6,5
25 freelance hanno fatto un'offerta media di $26 USD
5,0
5,0
4,4
4,4
4,1
4,1
4,0
4,0
4,0
4,0
3,1
3,1
3,1
3,1
2,2
2,2
2,1
2,1
0,7
0,7
0,0
0,0
0,0
0,0
0,0
0,0
0,0
0,0
0,0
0,0
0,0
0,0
0,0
0,0
0,0
0,0
0,0
0,0
0,0
0,0
Info sul cliente
New City, United States
1
Metodo di pagamento verificato
Membro dal giu 18, 2013
Verifica del cliente
Altri lavori del cliente
$10-30 USD
$10-30 USD
$10-30 USD
$10-30 USD
$10-30 USD
Lavori simili
₹1500-12500 INR
₹750-1250 INR / hour
$10-30 USD
₹750-1250 INR / hour
$10-30 USD
€1500-3000 EUR
$250-750 USD
$2-8 USD / hour
€30-250 EUR
$250-750 USD
$50-125 USD
$8-15 USD / hour
$750-1500 USD
$15-25 USD / hour
$12-30 SGD
£20-250 GBP
$250-750 USD
$750-1500 USD
₹12500-37500 INR
$15-25 USD / hour
Grazie! Ti abbiamo inviato tramite email il link per richiedere il tuo bonus gratuito.
Non è stato possibile inviarti l'email. Riprova per piacere.
Impossibile copiare negli appunti. Riprova dopo aver modificato le tue autorizzazioni.
Copiato negli appunti.
Caricamento anteprima
Autorizzazione per la geolocalizzazione concessa.
La tua sessione è scaduta ed è stato effettuato il log out. Accedi nuovamente per piacere.