应用脚本在主表项目中返回array_literal错误 [英] App script returns array_literal error in master sheet project

问题描述

我正在使用本教程中显示的指导方针来处理母版表项目: https://youtu. be/SyB4MVkWV3c

I´m working on a Master sheet project, using the guidelines that appear in this tutorial: https://youtu.be/SyB4MVkWV3c

基本上，该脚本对列"E"进行过滤.在出现在我的文档中的每张工作表(标签)中，以及每当它被"Pendiente"一词击中时，都会在主文件"上列出一些值.标签.目的是在我的主表中列出所有待处理的订单，并且每当订单满了，就删除"Pendiente". (待定)任何选项卡中的单元格，这样它就不会显示在我的主选项卡上.

Basically, this script filters column "E" in every sheet (tab) that appears in my document and every time it hits with the word "Pendiente", lists some values on the "Master" tab. The goal is to list all the pending orders in my master sheet, and whenever the order is fullfilled, erase the "Pendiente" (pending) cell from any of the tabs so that it doesen´t show on my master tab.

const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ignoreSheets =  ["Master"];
  
  const allSheets = ss.getSheets();
  
  const filteredListOfSheets = allSheets.filter(s => ignoreSheets.indexOf(s.getSheetName()) == -1 )
  
  let formulaArray = filteredListOfSheets.map(s => `filter({'${s.getSheetName()}'!B2:E, "${s.getSheetName()} / Fila "&ROW('${s.getSheetName()}'!C2:C)},(ISNUMBER('${s.getSheetName()}'!C2:C)),'${s.getSheetName()}'!E2:E="Pendiente")`)
  
  let formulaText = "={" + formulaArray.join(";") + "}";
  //console.log(formulaText);
  ss.getSheetByName("Master").getRange("A2").setFormula(formulaText);

在直到我回到某个特定点并删除单词"pendiente"之前，这都可以正常工作.从任何选项卡(与主选项卡不同).从那里开始，公式开始显示此错误:

This works fine until a certain point where I go back for example and erase the word "pendiente" from any tab (different from master tab). From there, the formula start showing this error:

在array_literal中，数组文字缺少一个或多个行的值"

"in array_literal, an array literal was missing values for one or more rows"

我知道，例如当有合并的单元格时，会出现这种错误，但这不是我的情况.另外，我检查了所有选项卡的列数.结果公式显示如下:

I´m aware that this kind of error appears when there are merged cells for example but that´s not my case. Also, I´ve checked and all the tabs have the same number of columns. The resulting formula shows this:

= {filter({'Ventas 17-9'！B2:E，"Ventas 17-9/Fila"& ROW('Ventas 17-9'！C2:C)}，(ISNUMBER(' Ventas 17-9'！C2:C))，'Ventas 17-9'！E2:E ='Pendiente'); ...

={filter({'Ventas 17-9'!B2:E, "Ventas 17-9 / Fila "&ROW('Ventas 17-9'!C2:C)},(ISNUMBER('Ventas 17-9'!C2:C)),'Ventas 17-9'!E2:E="Pendiente");...

奇怪的是，在我开始删除此"Pendiente"之前，单元格，脚本工作正常.也许与我的共享配置有关?该脚本是从Gsuite帐户编写的，并且电子表格已与非Gsuite用户共享.

The strange thing is that until I start deleting this "Pendiente" cells, the script works just fine. Perhaps it has something to do with my sharing configuration? This script has been written from a Gsuite account and the spreadsheet is shared with non Gsuite users.

屏幕截图: 主标签 示例选项卡，其中信息是从中提取的

Screenshots: Master Tab Example tab, where the infromation is pulled from}

推荐答案

解决方案

您会收到此错误，因为公式filter在其中一张工作表中找不到匹配的情况(如果您的工作表中不包含Pendiente，则会遇到此错误)，因此它返回单个占据一个单元格值.

Solution

You are getting that error because the formula filter is not finding the matching situation in one of your sheets (if your sheet does not contain Pendiente you will encounter this error) and therefore it is returning a single N/A occupying a single cell value.

这是格式错误的数组，因为在filter与搜索匹配的工作表上，您正在打印5列(要提取的数据)，而在没有找到Pendiente的工作表上，它试图返回的只是一个单个N/A值.

This is a malformed array because on the sheets where filter matches the search you are printing 5 columns (the data you are pulling) while on the ones that it does not find Pendiente it is trying to return just a single N/A value.

要解决此问题，您可以将filter公式包装在iferror中，这会将值N/A更改为5个值，这些值将与实际上与过滤器匹配的其余图纸相匹配.重要的是，您的所有行都具有相同的列数，以避免发生此错误.

To solve this, you can wrap your filter formulas in an iferror that will change the value N/A by 5 values that will match the rest of sheets that do actually match the filter. The important thing is that all your rows have the same number of columns to avoid this error.

因此，要解决此问题，请以此更改脚本的行(当然，您可以打印任意内容，而不是0，但必须在所有5列中打印):

So, to solve it,change the line of your script by this (of course instead of 0 you can print whatever you want but it must be printed in all 5 columns):

let formulaArray = filteredListOfSheets.map(s => `iferror(filter({'${s.getSheetName()}'!B2:E, "${s.getSheetName()} / Fila "&ROW('${s.getSheetName()}'!C2:C)},(ISNUMBER('${s.getSheetName()}'!C2:C)),'${s.getSheetName()}'!E2:E="Pendiente"),{"","","","",""})`)

参考:

IFERROR

过滤器

我希望这对您有所帮助.让我知道您是否需要其他任何东西，或者您是否不了解某些内容. :)

I hope this has helped you. Let me know if you need anything else or if you did not understood something. :)

这篇关于应用脚本在主表项目中返回array_literal错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！