我在 Google 表格中使用这个公式:
=SORT({SORT({'Discord-D'!B2:B, 'Discord-D'!A2:A,
ARRAYFORMULA(IF('Discord-D'!G2:G = "", "", IF('Discord-D'!C2:C <> "", "Removed", "Processing"))), 'Discord-D'!H2:H,
ARRAYFORMULA(IF('Discord-D'!C2:C <> "", 'Discord-D'!G2:G, IFERROR(REPLACE('Discord-D'!G2:G, LEN('Discord-D'!G2:G)-3, 4, "****")))),
'Discord-D'!I2:I}, ROW('Discord-D'!A2:A), FALSE); SORT({'Facebook-D'!B2:B, 'Facebook-D'!A2:A,
ARRAYFORMULA(IF('Facebook-D'!E2:E = "", "", IF('Facebook-D'!C2:C <> "", "Removed", "Processing"))), 'Facebook-D'!F2:F,
ARRAYFORMULA(IF('Facebook-D'!C2:C <> "", 'Facebook-D'!E2:E, IFERROR(REPLACE('Facebook-D'!E2:E, LEN('Facebook-D'!E2:E)-3, 4, "****")))),
'Facebook-D'!G2:G}, ROW('Facebook-D'!A2:A), FALSE); SORT({'Instagram-D'!B2:B, 'Instagram-D'!A2:A,
ARRAYFORMULA(IF('Instagram-D'!E2:E = "", "", IF('Instagram-D'!C2:C <> "", "Removed", "Processing"))), 'Instagram-D'!F2:F,
ARRAYFORMULA(IF('Instagram-D'!C2:C <> "", 'Instagram-D'!E2:E, IFERROR(IF(LEN(REGEXEXTRACT('Instagram-D'!E2:E, "com/(.+)")) > 4,
REPLACE('Instagram-D'!E2:E, LEN('Instagram-D'!E2:E)-3,4, "****"),
REPLACE('Instagram-D'!E2:E, LEN('Instagram-D'!E2:E)-1,2, "**"))))), 'Instagram-D'!G2:G}, ROW('Instagram-D'!A2:A), FALSE); SORT({'TikTok-D'!B2:B, 'TikTok-D'!A2:A,
ARRAYFORMULA(IF('TikTok-D'!E2:E = "", "", IF('TikTok-D'!C2:C <> "", "Removed", "Processing"))), 'TikTok-D'!F2:F,
ARRAYFORMULA(IF('TikTok-D'!C2:C <> "", IFERROR(IF(LEN(REGEXEXTRACT('TikTok-D'!E2:E, "https://www.tiktok.com/@(.*?)/")) = 4,
REPLACE('TikTok-D'!E2:E, LEN("https://www.tiktok.com/@" & REGEXEXTRACT('TikTok-D'!E2:E, "https://www.tiktok.com/@(.*?)/"))-1, 2, "**"),
REPLACE('TikTok-D'!E2:E, LEN("https://www.tiktok.com/@" & REGEXEXTRACT('TikTok-D'!E2:E, "https://www.tiktok.com/@(.*?)/"))-3, 4, "****"))),
IFERROR(IF(LEN(REGEXEXTRACT('TikTok-D'!E2:E, "https://www.tiktok.com/@(.*?)/")) = 4,
REPLACE(REPLACE('TikTok-D'!E2:E, LEN("https://www.tiktok.com/@" &
REGEXEXTRACT('TikTok-D'!E2:E, "https://www.tiktok.com/@(.*?)/"))-1, 2, "**"), LEN('TikTok-D'!E2:E)-3, 4, "****"),
REPLACE(REPLACE('TikTok-D'!E2:E, LEN("https://www.tiktok.com/@" & REGEXEXTRACT('TikTok-D'!E2:E, "https://www.tiktok.com/@(.*?)/"))-3, 4, "****"), LEN('TikTok-D'!E2:E)-3, 4, "****"))))), 'TikTok-D'!H2:H}, ROW('TikTok-D'!A2:A), FALSE);
SORT({'YouTube-D'!B2:B, 'YouTube-D'!A2:A, ARRAYFORMULA(IF('YouTube-D'!E2:E = "", "", IF('YouTube-D'!C2:C <> "", "Removed", "Processing"))), 'YouTube-D'!G2:G,
ARRAYFORMULA(IF('YouTube-D'!C2:C <> "", 'YouTube-D'!E2:E, IFERROR(REPLACE('YouTube-D'!E2:E, LEN('YouTube-D'!E2:E)-3, 4, "****")))),
ARRAYFORMULA(IFERROR(REPLACE('YouTube-D'!F2:F, LEN('YouTube-D'!F2:F)-3, 4, "****")))}, ROW('YouTube-D'!A2:A), FALSE)}, 1, FALSE)
与其余列相比,YouTube 数组包含一个额外的列。最初,它会失败,因为并非所有数组都具有相同数量的列。我通过在每个工作表中插入一个空白列并引用该列(除 YouTube 之外的每个数组中的最后一列引用,因此 'Discord-D'!I2:I, 'Facebook-D'!G2:G, ' Instagram-D'!G2:G 和 'TikTok-D'!H2:H)。有没有更好的方法可以实现这一目标(例如通过创建一个几乎空白的列而不是实际上需要拥有一个)?