投资组合跟踪器不自动更新

Question

我不是程序员，但我可以阅读和理解代码背后的逻辑。为了跟踪我的投资组合的表现，我使用了一个我在网上找到的脚本，它可以让我自动获得最新的价格。我根据自己的需求调整了脚本:我不需要仪表板和保证金电子邮件通知。此外，我还添加了一个基于时间的触发器，每5分钟自动刷新一次文件和数字。

我的仪表板

但是，由于某些原因，文件没有每5分钟自动更新一次，并且没有返回消息:"rate was last updated at time">

此外，通过将我的表格与原始表格(下面的链接)进行比较，我注意到这可能是由于以下原因:

• 我把BTC从原来的位置移走了。
• 我删除了A列第2行和第3行中包含公式的内容:=MATCH("BTC"， B:B,0)/=MATCH("ETH"， B:B,0)
• 我从D列第27行删除了这个公式:=INDIRECT(ADDRESS(A2+1,9))

原始表和脚本:https://docs.google.com/spreadsheets/d/1t3tX22qEIK0HmgXgwiI0kxCLkBWFSSfusGGvxULWO1I/edit

任何帮助都是感激的:)

代码如下:

function RefreshImports() {
var lock = LockService.getScriptLock();
if (!lock.tryLock(5000)) return; 
var id = "1IVUjHDRxJiWw8H1LYY_IY0_ydwZ4F3qfSHKcKLa78Bw";
var ss = SpreadsheetApp.openById(id);
var sheet = ss.getSheetByName("Profit/Loss");
var rowRef = sheet.getRange(2,1).getValue();
var active = sheet.getRange(8 + rowRef,8).getValue();
if (active == 1) {
var dataRange = sheet.getDataRange();
var formulas = dataRange.getFormulas();
var content = "";
var now = new Date();
var time = now.getTime();
var re = /.*[^a-z0-9]import(?:xml|data|feed|html|range|regex|json)(.*/gi;
var re2 = /((?|&)(update=[0-9]*))/gi;
for (var row=0; row<formulas.length; row++) {
for (var col=0; col<formulas[0].length; col++) {
content = formulas[row][col];
if (content != "") {
var match = content.search(re);
if (match !== -1 ) {
var url = sheet.getRange(row + 1, col).getValue();
if (url != "") {
var updatedContent = url.toString().replace(re2,"?update=" + time);
if (updatedContent == url) {
// No querystring exists yet in url
updatedContent = url.toString() + "?update=" + time;
}
// Update url in formula with querystring param
sheet.getRange(row + 1, col).setValue(updatedContent);
Utilities.sleep(250);
}
}
}
}
}
// Done refresh; release the lock.
lock.releaseLock();     
var dateStr = now.toLocaleDateString("en-GB", { timeZone: 'Lebanon/Beirut' });
var timeStr = now.toLocaleTimeString("en-GB", { timeZone: 'Lebanon/Beirut' });
// Show last updated time on sheet somewhere
sheet.getRange(4 + rowRef,2).setValue("Rates were last updated at " + timeStr);
}
}
function importRegex(url, regexInput) {
var output = '';
var fetchedUrl = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
if (fetchedUrl) {
var html = fetchedUrl.getContentText();
if (html.length && regexInput.length) {
output = html.match(new RegExp(regexInput, 'i'))[1];
}
}
// Grace period to not overload
Utilities.sleep(1000);
return unescapeHTML(output);
}
function importJson(address) {
var output = '';
var fetchedUrl = UrlFetchApp.fetch('https://api.pancakeswap.info/api/v2/tokens/' + address, {muteHttpExceptions: true});
if (fetchedUrl) {
var json = JSON.parse(fetchedUrl.getContentText());
if (json) {
output = json.data.price.substring(0,15);
}
}
// Grace period to not overload
Utilities.sleep(1000);
return unescapeHTML(output);
}
var htmlEntities = {
nbsp:  ' ',
cent:  '¢',
pound: '£',
yen:   '¥',
euro:  '€',
dollar:  '$',
copy:  '©',
reg:   '®',
lt:    '<',
gt:    '>',
mdash: '–',
ndash: '-',
quot:  '"',
amp:   '&',
apos:  '''
};
function unescapeHTML(str) {
return str.replace(/&([^;]+);/g, function (entity, entityCode) {
var match;
if (entityCode in htmlEntities) {
return htmlEntities[entityCode];
} else if (match = entityCode.match(/^#x([da-fA-F]+)$/)) {
return String.fromCharCode(parseInt(match[1], 16));
} else if (match = entityCode.match(/^#(d+)$/)) {
return String.fromCharCode(~~match[1]);
} else {
return entity;
}
});
};

Answer 1

你还有">rowRef";在你的脚本中，它被绑定到BTC价格的相对位置。我清除了你的脚本，你可以试试下面的脚本，让我知道。

function RefreshImports() {
var lock = LockService.getScriptLock();
if (!lock.tryLock(5000)) return; 
var id = "1IVUjHDRxJiWw8H1LYY_IY0_ydwZ4F3qfSHKcKLa78Bw";
var ss = SpreadsheetApp.openById(id);
var sheet = ss.getSheetByName("Profit/Loss");
var dataRange = sheet.getDataRange();
var formulas = dataRange.getFormulas();
var content = "";
var now = new Date();
var time = now.getTime();
var re = /.*[^a-z0-9]import(?:xml|data|feed|html|range|regex|json)(.*/gi;
var re2 = /((?|&)(update=[0-9]*))/gi;
for (var row=0; row<formulas.length; row++) {
for (var col=0; col<formulas[0].length; col++) {
content = formulas[row][col];
if (content != "") {
var match = content.search(re);
if (match !== -1 ) {
var url = sheet.getRange(row + 1, col).getValue();
if (url != "") {
var updatedContent = url.toString().replace(re2,"?update=" + time);
if (updatedContent == url) {
// No querystring exists yet in url
updatedContent = url.toString() + "?update=" + time;
}
// Update url in formula with querystring param
sheet.getRange(row + 1, col).setValue(updatedContent);
Utilities.sleep(250);
}
}
}
}
}
// Done refresh; release the lock.
lock.releaseLock();     
//var dateStr = now.toLocaleDateString("en-GB", { timeZone: 'Lebanon/Beirut' });
var timeStr = now.toLocaleTimeString("en-GB", { timeZone: 'Lebanon/Beirut' });
// Show last updated time on sheet somewhere
sheet.getRange(14,2).setValue("Rates were last updated at " + timeStr);
}
function importRegex(url, regexInput) {
var output = '';
var fetchedUrl = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
if (fetchedUrl) {
var html = fetchedUrl.getContentText();
if (html.length && regexInput.length) {
output = html.match(new RegExp(regexInput, 'i'))[1];
}
}
// Grace period to not overload
Utilities.sleep(1000);
return unescapeHTML(output);
}
function importJson(address) {
var output = '';
var fetchedUrl = UrlFetchApp.fetch('https://api.pancakeswap.info/api/v2/tokens/' + address, {muteHttpExceptions: true});
if (fetchedUrl) {
var json = JSON.parse(fetchedUrl.getContentText());
if (json) {
output = json.data.price.substring(0,15);
}
}
// Grace period to not overload
Utilities.sleep(1000);
return unescapeHTML(output);
}
var htmlEntities = {
nbsp:  ' ',
cent:  '¢',
pound: '£',
yen:   '¥',
euro:  '€',
dollar:  '$',
copy:  '©',
reg:   '®',
lt:    '<',
gt:    '>',
mdash: '–',
ndash: '-',
quot:  '"',
amp:   '&',
apos:  '''
};
function unescapeHTML(str) {
return str.replace(/&([^;]+);/g, function (entity, entityCode) {
var match;
if (entityCode in htmlEntities) {
return htmlEntities[entityCode];
} else if (match = entityCode.match(/^#x([da-fA-F]+)$/)) {
return String.fromCharCode(parseInt(match[1], 16));
} else if (match = entityCode.match(/^#(d+)$/)) {
return String.fromCharCode(~~match[1]);
} else {
return entity;
}
});
};