I have a spreadsheet with two sheets: "Data Base" and "Adendas". One has changes records and the other works as a database.
"Data Base" has these columns among others:
- Name (Column D)
- Location (Column K)
- Hours (Column L)
- Vehicle (Column M)
"Adendas" has:
- Name (Column D)
- Change Type (Column M)
- Location (Column N)
- Hours (Column O)
- Vehicle (Column P)
- From (Column Q)
- Change Confirmation (Column R)
Observations:
Changes are added to the 'Adendas' sheet with a from date specified. When the date matches today, it is supposed to copy the correspondent information depending the type of change from 'Adendas' to the matching name in 'Data Base'
- The Data Base sheet look like this:
- Adendas Sheet:
For example, today 04/25/2022, it should move the vehicle information from Column P in Adendas to Column M in Data Base for Name2 and then apply a true value to the Confirmation box in Adenda's Column R
Result should look like this:
Data Base
Adendas
After lot of search and investigation, I managed to create the following code which works for the date and checkbox part but I can't manage to apply the change to the matching name value as it should since it's copying it but at a random cell.
function parse_worker(employee) {
/* Given a row from the spreadsheet, parse the data into a javascript object. */
if (typeof(employee) == 'undefined') {
return null;
}
let trabajador = {};
trabajador.nombre = employee[3];
trabajador.tipo = employee[12];
trabajador.location = employee[13];
trabajador.hours = employee[14];
trabajador.vehicle = employee[15];
trabajador.fecha = employee[16];
trabajador.cambio = employee[17];
return trabajador;
};
function are_dates_equal(date1, date2) {
/* Returns true if the two dates are equal, false otherwise.
It only compares the day, the month and the year. Time is not considered.
*/
return date1.getUTCDate() == date2.getUTCDate() && date1.getUTCMonth() == date2.getUTCMonth() && date1.getUTCFullYear() == date2.getUTCFullYear();
}
function try_to_copy_data() {
/* Iterate over the rows in the spreadsheet and make the corresponding change if it's time to do it. */
var app = SpreadsheetApp;
var spreadsheet = app.getActiveSpreadsheet();
var sourceSheet = spreadsheet.getSheetByName('Adendas');
var sourceRows = sourceSheet.getRange(2, 1, sourceSheet.getLastRow() - 1, sourceSheet.getLastColumn()).getValues();
var targetSheet = spreadsheet.getSheetByName('Data Base');
var targetRows = targetSheet.getRange(2, 1, targetSheet.getLastRow() - 1, targetSheet.getLastColumn()).getValues();
sourceRows.forEach(function(row, i) {
/* Copy's the data to the given employee if it's time to do it and the change type matches. */
// Exit the function if the employee has no date.
let is_date_set = row[16] !== '';
if (!is_date_set) {
return;
}
let worker = parse_worker(row);
var now = new Date();
Logger.log(now.toLocaleDateString('es-ES', {timeZone: 'UTC'}));
Logger.log(worker.fecha.toLocaleDateString('es-ES', {timeZone: 'UTC'}));
// Exit the function if is not time to make the change
if (!are_dates_equal(now, worker.fecha)) {
return;
}
// Exit the function if change has already made
if (worker.cambio === true) {
return;
}
if (are_dates_equal(now, worker.fecha) && worker.tipo === "Vehículo"){
targetSheet.getRange(i 2,[13]).setValue(worker.vehicle);
sourceSheet.getRange(i 2,[18]).setValue(true);
} else if (are_dates_equal(now, worker.fecha) && worker.tipo === "Centro de Trabajo"){
targetSheet.getRange(i 2,[11]).setValue(worker.vehicle);
sourceSheet.getRange(i 2,[18]).setValue(true);
} else if (are_dates_equal(now, worker.fecha) && worker.tipo === "Horas Contrato"){
targetSheet.getRange(i 2,[12]).setValue(worker.vehicle);
sourceSheet.getRange(i 2,[18]).setValue(true);
} else {
Logger.log("Error! Not a valid change.");
}
})
}
try_to_copy_data();
The idea was to use the general knowledge on how to copy the value automatically and then adapt it to the positioning thing but I'm not even close to figure out how to do it.
EDIT: Final working code thanks to Yuri's help
function parse_worker(employee) {
/* Given a row from the spreadsheet, parse the data into a javascript object. */
if (typeof(employee) == 'undefined') {
return null;
}
let trabajador = {};
trabajador.nombre = employee[3];
trabajador.tipo = employee[12];
trabajador.location = employee[13];
trabajador.hours = employee[14];
trabajador.vehicle = employee[15];
trabajador.fecha = employee[16];
trabajador.cambio = employee[17];
return trabajador;
};
function are_dates_equal(date1, date2) {
/* Returns true if the two dates are equal, false otherwise.
It only compares the day, the month and the year. Time is not considered.
*/
return date1.getUTCDate() == date2.getUTCDate() && date1.getUTCMonth() == date2.getUTCMonth() && date1.getUTCFullYear() == date2.getUTCFullYear();
}
function try_to_copy_data() {
/* Iterate over the rows in the spreadsheet and make the corresponding change if it's time to do it. */
var app = SpreadsheetApp;
var spreadsheet = app.getActiveSpreadsheet();
var sourceSheet = spreadsheet.getSheetByName('Adendas');
var sourceRows = sourceSheet.getRange(2, 1, sourceSheet.getLastRow() - 1, sourceSheet.getLastColumn()).getValues();
var targetSheet = spreadsheet.getSheetByName('Data Base');
var targetRows = targetSheet.getRange(2, 1, targetSheet.getLastRow() - 1, targetSheet.getLastColumn()).getValues();
var targetNames = targetRows.map(e => e[3]);
sourceRows.forEach(function(row, i) {
/* Copy's the data to the given employee if it's time to do it and the change type matches. */
// Exit the function if the employee has no date.
let is_date_set = row[16] !== '';
if (!is_date_set) {
return;
}
let worker = parse_worker(row);
var now = new Date();
var row_index = targetNames.indexOf(worker.nombre);
Logger.log(now.toLocaleDateString('es-ES', {timeZone: 'UTC'}));
Logger.log(worker.fecha.toLocaleDateString('es-ES', {timeZone: 'UTC'}));
// Exit the function if is not time to make the change
if (!are_dates_equal(now, worker.fecha)) {
return;
}
// Exit the function if change has already made
if (worker.cambio === true) {
return;
}
if (are_dates_equal(now, worker.fecha) && worker.tipo === "Vehículo"){
targetSheet.getRange(row_index 2,13).setValue(worker.vehicle);
sourceSheet.getRange(i 2,[18]).setValue(true);
} else if (are_dates_equal(now, worker.fecha) && worker.tipo === "Centro de Trabajo"){
targetSheet.getRange(row_index 2,11).setValue(worker.location);
sourceSheet.getRange(i 2,[18]).setValue(true);
} else if (are_dates_equal(now, worker.fecha) && worker.tipo === "Horas Contrato"){
targetSheet.getRange(row_index 2,12).setValue(worker.hours);
sourceSheet.getRange(i 2,[18]).setValue(true);
} else {
Logger.log("Error! Not a valid change.");
}
})
}
try_to_copy_data();
CodePudding user response：
Probably it should be something like this:
function try_to_copy_data() {
var now = new Date();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = ss.getSheetByName('Adendas');
var sourceRows = sourceSheet.getDataRange().getValues().slice(1);
var targetSheet = ss.getSheetByName('Data Base');
var [header, ...targetRows] = targetSheet.getDataRange().getValues();
var targetNames = targetRows.map(e => e[3]);
sourceRows.forEach((row, i) => {
let worker = parse_worker(row);
if (worker.ok == true) return;
if (!are_dates_equal(now, worker.date)) return;
var row_index = targetNames.indexOf(worker.name);
if (worker.location != '') targetRows[row_index][10] = worker.location;
if (worker.hours != '') targetRows[row_index][11] = worker.hours;
if (worker.vehicle != '') targetRows[row_index][12] = worker.vehicle;
sourceSheet.getRange('R' (i 2)).check();
})
var table = [header, ...targetRows];
targetSheet.clearContents()
.getRange(1, 1, table.length, table[0].length)
.setValues(table);
}
function parse_worker(row) {
let worker = {
name: row[3],
type: row[12],
location: row[13],
hours: row[14],
vehicle: row[15],
date: row[16],
ok: row[17],
}
return worker;
}
function are_dates_equal(date1, date2) {
return date1.getUTCDate() == date2.getUTCDate() &&
date1.getUTCMonth() == date2.getUTCMonth() &&
date1.getUTCFullYear() == date2.getUTCFullYear();
}