英文:
Comparing scheduled time with current time in Apps Script
问题
以下是您的代码的翻译部分：
我有一个 Apps Script 片段，用于计算时间并与当前时间进行比较。但是，当计划的时间晚于当前时间时，比较总是结果为 "YES"。我需要帮助修改代码，以找到可靠的计划时间格式，并与当前时间逻辑比较。我认为问题可能与日期和时间单元格的格式有关，尽管我尝试使用 Utilities.formatDate 强制执行日期和时间格式化。
**convertToTehranTime** 函数将转换后的时间存储为所需列中的 **scheduledTime**。
代码片段：
for (var i = 2; i <= lastRowA; i++) {
var row = sheet.getRange(i, 1, 1, sheet.getLastColumn()).getValues()[0];
var scheduledTime = row[scheduledTimeColumnIndex];
convertToTehranTime(i);
var now = new Date();
var scheduledDateTime = new Date(scheduledTime);
scheduledDateTime = Utilities.formatDate(scheduledDateTime, "Asia/Tehran", "dd/MM/yyyy HH:mm");
if (scheduledDateTime <= now) {
sheet.getRange(i, 18).setValue("YES");
}
}
这是时间转换的函数：
function convertToTehranTime(i) {
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var data = sheet.getDataRange().getValues();
// 使用 i 值检索行数据
var rowData = data[i - 1];
var timeString = rowData[12]; // 列 M
var timeZoneOffset = 3.5 - rowData[9]; // 列 J
// 将时间字符串转换为 Date 对象
var date = parseTimeString(timeString);
// 计算德黑兰时间的新日期和时间
var newDate = new Date(date.getTime() + timeZoneOffset * 60 * 60 * 1000); // 转换为德黑兰时间
// 检查计划日期是否落在周末（星期六或星期日）
if (newDate.getDay() === 6 || newDate.getDay() === 0) { // 星期六或星期日
// 将日期增加到下一个工作日
newDate.setDate(newDate.getDate() + getNextWorkingDayOffset(newDate));
}
rowData[13] = newDate; // 列 N
// 使用新数据更新表格
sheet.getDataRange().setValues(data);
}
希望这可以帮助您理解代码的翻译部分。如果您有任何其他问题，请随时提出。
英文:
I have an Apps Script snippet that calculates time and compares it with the current time. However, when the scheduled time is later than the current time, the comparison always results in "YES." I need help modifying the code to find a reliable format for the scheduled time and logically compare it with the current time. I think the issue may related to the formatting of the cell for date and time, although I tried to enforce the date and time formatting with Utilities.formatDate.
convertToTehranTime function stores the converted time as scheduledTime in desired column.
Code Snippet:
for (var i = 2; i <= lastRowA; i++) {
var row = sheet.getRange(i, 1, 1, sheet.getLastColumn()).getValues()[0];
var scheduledTime = row[scheduledTimeColumnIndex];
convertToTehranTime(i);
var now = new Date();
var scheduledDateTime = new Date(scheduledTime);
scheduledDateTime = Utilities.formatDate(scheduledDateTime, "Asia/Tehran", "dd/MM/yyyy HH:mm");
if (scheduledDateTime <= now) {
sheet.getRange(i, 18).setValue("YES");
}
}
and this is the function of time conversion:
function convertToTehranTime(i) {
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var data = sheet.getDataRange().getValues();
// Retrieve the row data using the i value
var rowData = data[i - 1];
var timeString = rowData[12]; // Column M
var timeZoneOffset = 3.5 - rowData[9]; // Column J
// Convert the time string to a Date object
var date = parseTimeString(timeString);
// Calculate the new date and time in Tehran time
var newDate = new Date(date.getTime() + timeZoneOffset * 60 * 60 * 1000); // Convert to Tehran time
// Check if the scheduled date falls on a weekend (Saturday or Sunday)
if (newDate.getDay() === 6 || newDate.getDay() === 0) { // Saturday or Sunday
// Increment the date to the next working day
newDate.setDate(newDate.getDate() + getNextWorkingDayOffset(newDate));
}
rowData[13] = newDate; // Column N
// Update the sheet with the new data
sheet.getDataRange().setValues(data);
}
答案1
得分: 2
function myfunk() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName("Sheet0");
const vtv = new Date(sh.getRange("A1").getValue()).valueOf();
if (vtv) {
if (vtv < new Date().valueOf()) {
SpreadsheetApp.getUi().alert("时间在过去");
} else {
SpreadsheetApp.getUi().alert("时间在未来");
}
}
}
英文:
function myfunk() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName("Sheet0");
const vtv = new Date(sh.getRange("A1").getValue()).valueOf();
if (vtv) {
if (vtv < new Date().valueOf()) {
SpreadsheetApp.getUi().alert("Time is in past");
} else {
SpreadsheetApp.getUi().alert("Time is in future")
}
}
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论