ARRAYFORMULA 不适用于包含 IF 和 COUNTIF 的自定义公式 [英] ARRAYFORMULA not working with custom formula containing IF and COUNTIF
问题描述
我正在运行带有自定义公式 (=getRedirect()
) 的 =IF()
的 =ARRAYFORMULA()
,即检索单元格范围内重定向缩短的 URL 的原始 URL,以获取原始 URL 中的用户 ID.如果单元格 B:B
中的 URL 是重定向缩短的 URL,它会运行自定义公式并在运行 =MID()
到自定义公式的输出.如果单元格 B:B
中的 URL 是原始 URL,则在运行 =MID()
后将用户 ID 输出到 C:C
它.=IF()
根据 B:B
中的单元格值输出特定的错误消息.
I'm running a =ARRAYFORMULA()
of =IF()
with a custom formula (=getRedirect()
), that retrieves original URLs of redirect shortened URLs that are in a cell range, to get the user ID in the original URLs. If URL in cell B:B
is a redirect shortened URL, it runs the custom formula and outputs the user ID into C:C
after running a =MID()
to the output of the custom formula. If URL in cell B:B
is an original URL, it outputs the user ID into C:C
after running a =MID()
on it. The =IF()
outputs specific error message depending on the cell value in B:B
.
这是自定义公式 -
var response = UrlFetchApp.fetch(url, {'followRedirects': false, 'muteHttpExceptions': false});
var redirectUrl = response.getHeaders()['Location']; // undefined if no redirect, so...
var responseCode = response.getResponseCode();
if (redirectUrl) { // ...if redirected...
var nextRedirectUrl = getRedirect(redirectUrl); // ...it calls itself recursively...
Logger.log(url + " is redirecting to " + redirectUrl + ". (" + responseCode + ")");
return nextRedirectUrl;
}
else { // ...until it's not
Logger.log(url + " is canonical. (" + responseCode + ")");
return url;
}
}
这是我在 C:C 中输入的内容 -=ARRAYFORMULA(IF($B2:B="","<---请插入抖音视频的网址",IF(COUNTIF($B2:B,"*/video/*")),MID($B2:B,FIND("~",SUBSTITUTE($B2:B,"/","~",5))+1,19),IFERROR(MID(getRedirect)($B2:B),FIND("~",SUBSTITUTE(getRedirect($B2:B),"/","~",6))+1,19),"<--- URL 错误"))))
Here's what I input in C:C -
=ARRAYFORMULA(IF($B2:B="","<--- Please insert TikTok video's URL",IF(COUNTIF($B2:B,"*/video/*"),MID($B2:B,FIND("~",SUBSTITUTE($B2:B,"/","~",5))+1,19),IFERROR(MID(getRedirect($B2:B),FIND("~",SUBSTITUTE(getRedirect($B2:B),"/","~",6))+1,19),"<--- error in URL"))))
公式正常输出,没有=ARRAYFORMULA()
.不确定是否是由于 =ARRAYFORMULA()
和 =COUNTIF()
在公式中 C:C
之间的冲突造成的.
The formula outputs normally without =ARRAYFORMULA()
. Not sure if it's due to the clash between =ARRAYFORMULA()
and =COUNTIF()
in the formula in C:C
.
具有预期结果的样本表 - https://docs.google.com/spreadsheets/d/1rJKm5KIT8itZs2AC0wdDZ-Cgar4mERu4jzJ3mN7KMb8
Sample sheet with expected outcome - https://docs.google.com/spreadsheets/d/1rJKm5KIT8itZs2AC0wdDZ-CGar4mERu4jzJ3mN7KMb8
推荐答案
要在 ARRAYFORMULA
中使用自定义函数,您需要修改您的脚本.请参阅此示例
To use custom function with ARRAYFORMULA
, you need to modify you script.
See this example
这篇关于ARRAYFORMULA 不适用于包含 IF 和 COUNTIF 的自定义公式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!