我有一個包含兩張紙的電子表格：“資料庫”和“附錄”。一個有更改記錄，另一個用作資料庫。
“資料庫”具有以下列：
- 名稱（D 列）
- 位置（K 欄）
- 小時（L 列）
- 車輛（M 欄）
“附錄”有：
- 名稱（D 列）
- 更改型別（M 列）
- 位置（N 列）
- 小時（O 列）
- 車輛（P 欄）
- 從（Q 列）
- 更改確認（R 列）
觀察：
更改將添加到“附錄”表中，并指定起始日期。當日期與今天匹配時，應根據從“附錄”到“資料庫”中匹配名稱的更改型別復制對應資訊
- 資料庫表如下所示：
- 附錄表：
例如，今天 04/25/2022，它應該將車輛資訊從Adendas 中的P 列移動到Name2的資料庫中的M 列，然后將真值應用于 Adenda列 R中的確認框
結果應如下所示：
資料庫
附錄
經過大量搜索和調查，我設法創建了以下適用于日期和復選框部分的代碼，但我無法將更改應用到匹配的名稱值，因為它正在復制它，但在隨機單元格中。
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();
這個想法是使用關于如何自動復制值的一般知識，然后將其調整到定位的東西，但我什至不知道如何去做。
編輯：由于 Yuri 的幫助，最終的作業代碼
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();
uj5u.com熱心網友回復：
大概應該是這樣的：
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();
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/464922.html