8wDlpd.png
8wDFp9.png
8wDEOx.png
8wDMfH.png
8wDKte.png

IMPORTXML Google 表格公式不起作用

sancho.s ReinstateMonicaCellio 2月前

22 0

我尝试在 Google 电子表格中使用 =IMPORTXML(),但即使使用了正确的 xpath_query 参数,我仍收到“导入的内容为空”。我在这里使用了 xPath 生成器工具。我得到了这个...

我尝试 在 Google 电子表格中 =IMPORTXML() 即使使用了正确的 “导入的内容为空”。 xpath_query 在这里 使用了 xPath 生成器工具 .

我在使用此工具时出现此错误:

 ROOT CAUSE IS : java.lang.Exception: Error in input=org.xml.sax.SAXParseException; lineNumber: 1; columnNumber: 1; Content is not allowed in prolog. |  
 <div class="errorLabel">Failed With Following Error message:</div> <div class="bar error"> Error:Error in input=org.xml.sax.SAXParseException; lineNumber: 1; columnNumber: 1; Content is not allowed in prolog. || Description:java.lang.Exception: Error in input=org.xml.sax.SAXParseException; lineNumber: 1; columnNumber: 1; Content is not allowed in prolog. |*Please check input.</div>[Ljava.lang.StackTraceElement;@34325e99

我不是开发人员,但如果这是唯一的解决方案,我对 Appscript 有一点了解。有人能帮帮我吗?我更愿意只用公式来解决,但考虑到这个 XML 文件的条件,这个要求可能太高了。 对于资源非常稀缺的巴西一家公立医院来说,这是一项非常重要的资产。 抱歉 XML 缩进,这是一个棘手的问题。

根据以下评论添加了公式: =IMPORTXML("https://www.dropbox.com/scl/fi/ozjw0tkjwais45f6do0ai/35240896382429000160550010004358881248052188.xml?rlkey=ddztt2vqxzr3ch1kwyk4enve5&st=k0ps6ehm&dl=1","/nfeProc/@versao")

此 XML 文件似乎没有任何与之关联的样式信息。文档树如下所示。

<nfeProc xmlns="http://www.portalfiscal.inf.br/nfe" versao="4.00">
        <protNFe>
        <infProt>
        <nProt>135241709295739</nProt>
        <digVal>mm/WysVUfmCGPjR1DGK6vdKgefQ=</digVal>
        <dhRecbto>2024-08-07T12:12:03-03:00</dhRecbto>
        <Id>Id135241709295739</Id>
        <chNFe>35240896382429000160550010004358881248052188</chNFe>
        <xMotivo>Autorizado o uso da NF-e</xMotivo>
        <cStat>100</cStat>
        </infProt>
        </protNFe>
        <NFe>
        <infNFe Id="NFe35240896382429000160550010004358881248052188">
        <infAdic>
        <infCpl>Pedido No: 291625 EMPENHO 2024NE04264 - PREGAO 10520/02 - PROCESSO SEI-260008/010121/2022 - BB Ag. 3336-7 CC. 2289-6 End.Entrega: BOULEVARD VINTE E OITO DE SETEMBRO, 77 - VILA ISABEL - RIO DE JANEIRO - RJ Cep:20551900 - Valor Aproximado do(s) Tributo(s): R$ 3314.30 (15.45%) Federal e R$ 3861.00 (18.00%) Estadual Fonte: IBPT .Valor do ICMS relativo ao Fundo de Combate a Pobreza - FCP da UF de destino: R$ 429.00. Valor do ICMS Interestadual para a UF de destino: R$ 1716.00. Valor do ICMS Interestadual para a UF do remetente: R$ 0.</infCpl>
        <infAdFisco>Lei 10865 2004 art 28 XVI Ficam reduzidas a ZERO para LENTES INTRAOCULAR as aliquotas da contribuicao para o PIS e da COFINS incidentes sobre a receita bruta decorrente da venda. Retencao de IR aliquota de 1.2 por cento IN RFB 2145 de 26 de junho de 2023 alterando IN RFB 1234 de 11 de janeiro de 2012.</infAdFisco>
        </infAdic>
        <infRespTec>
        <fone>1128593904</fone>
        <CNPJ>53113791000122</CNPJ>
        <xContato>Rodrigo de Almeida Sartorio</xContato>
        <email>[email protected]</email>
        </infRespTec>
        <det>
        <prod>
        <cEAN>04547480451831</cEAN>
        <cProd>B0321077116002I</cProd>
        <qCom>1.0000</qCom>
        <cEANTrib>04547480451831</cEANTrib>
        <vUnTrib>195.00000000</vUnTrib>
        <cBenef/>
        <qTrib>1.0000</qTrib>
        <vProd>195.00</vProd>
        <xProd>ISERT 151 ESFERICA D 21.5</xProd>
        <vUnCom>195.00000000</vUnCom>
        <indTot>1</indTot>
        <uTrib>UN</uTrib>
        <NCM>90213920</NCM>
        <uCom>UN</uCom>
        <CFOP>6108</CFOP>
        </prod>
        <imposto>
        <vTotTrib>65.23</vTotTrib>
        <ICMS>
        <ICMS00>
        <modBC>3</modBC>
        <orig>6</orig>
        <CST>00</CST>
        <vBC>195.00</vBC>
        <vICMS>23.40</vICMS>
        <pICMS>12.0000</pICMS>
        </ICMS00>
        </ICMS>
        <IPI>
        <IPINT>
        <CST>51</CST>
        </IPINT>
        <cEnq>999</cEnq>
        </IPI>
        <ICMSUFDest>
        <vBCFCPUFDest>195.00</vBCFCPUFDest>
        <pICMSInter>12.00</pICMSInter>
        <vICMSUFDest>15.60</vICMSUFDest>
        <vICMSUFRemet>0</vICMSUFRemet>
        <pICMSInterPart>100</pICMSInterPart>
        <vFCPUFDest>3.90</vFCPUFDest>
        <pFCPUFDest>2.0000</pFCPUFDest>
        <vBCUFDest>195.00</vBCUFDest>
        <pICMSUFDest>20.0000</pICMSUFDest>
        </ICMSUFDest>
        <COFINS>
        <COFINSNT>
        <CST>06</CST>
        </COFINSNT>
        </COFINS>
        <PIS>
        <PISNT>
        <CST>06</CST>
        </PISNT>
        </PIS>
        </imposto>
        <infAdProd>Lote(s):TFP50GW9,</infAdProd>
        </det>
        <det>
        <prod>
        <cEAN>04547480451862</cEAN>
        <cProd>B0321077116002L</cProd>
        <qCom>1.0000</qCom>
        <cEANTrib>04547480451862</cEANTrib>
        <vUnTrib>195.00000000</vUnTrib>
        <cBenef/>
        <qTrib>1.0000</qTrib>
        <vProd>195.00</vProd>
        <xProd>ISERT 151 ESFERICA D 23.0</xProd>
        <vUnCom>195.00000000</vUnCom>
        <indTot>1</indTot>
        <uTrib>UN</uTrib>
        <NCM>90213920</NCM>
        <uCom>UN</uCom>
        <CFOP>6108</CFOP>
        </prod>
        <imposto>
        <vTotTrib>65.23</vTotTrib>
        <ICMS>
        <ICMS00>
        <modBC>3</modBC>
        <orig>6</orig>
        <CST>00</CST>
        <vBC>195.00</vBC>
        <vICMS>23.40</vICMS>
        <pICMS>12.0000</pICMS>
        </ICMS00>
        </ICMS>
        <IPI>
        <IPINT>
        <CST>51</CST>
        </IPINT>
        <cEnq>999</cEnq>
        </IPI>
        <ICMSUFDest>
        <vBCFCPUFDest>195.00</vBCFCPUFDest>
        <pICMSInter>12.00</pICMSInter>
        <vICMSUFDest>15.60</vICMSUFDest>
        <vICMSUFRemet>0</vICMSUFRemet>
        <pICMSInterPart>100</pICMSInterPart>
        <vFCPUFDest>3.90</vFCPUFDest>
        <pFCPUFDest>2.0000</pFCPUFDest>
        <vBCUFDest>195.00</vBCUFDest>
        <pICMSUFDest>20.0000</pICMSUFDest>
        </ICMSUFDest>
        <COFINS>
        <COFINSNT>
        <CST>06</CST>
        </COFINSNT>
        </COFINS>
        <PIS>
        <PISNT>
        <CST>06</CST>
        </PISNT>
        </PIS>
        </imposto>
        <infAdProd>Lote(s):TFP50L61,</infAdProd>
        </det>
        <det>
        <nItem>107</nItem>
        <prod>
        <cEAN>04547480451862</cEAN>
        <cProd>B0321077116002L</cProd>
        <qCom>1.0000</qCom>
        <cEANTrib>04547480451862</cEANTrib>
        <vUnTrib>195.00000000</vUnTrib>
        <cBenef/>
        <qTrib>1.0000</qTrib>
        <vProd>195.00</vProd>
        <xProd>ISERT 151 ESFERICA D 23.0</xProd>
        <vUnCom>195.00000000</vUnCom>
        <indTot>1</indTot>
        <uTrib>UN</uTrib>
        <NCM>90213920</NCM>
        <uCom>UN</uCom>
        <CFOP>6108</CFOP>
        </prod>
        <imposto>
        <vTotTrib>65.23</vTotTrib>
        <ICMS>
        <ICMS00>
        <modBC>3</modBC>
        <orig>6</orig>
        <CST>00</CST>
        <vBC>195.00</vBC>
        <vICMS>23.40</vICMS>
        <pICMS>12.0000</pICMS>
        </ICMS00>
        </ICMS>
        <IPI>
        <IPINT>
        <CST>51</CST>
        </IPINT>
        <cEnq>999</cEnq>
        </IPI>
        <ICMSUFDest>
        <vBCFCPUFDest>195.00</vBCFCPUFDest>
        <pICMSInter>12.00</pICMSInter>
        <vICMSUFDest>15.60</vICMSUFDest>
        <vICMSUFRemet>0</vICMSUFRemet>
        <pICMSInterPart>100</pICMSInterPart>
        <vFCPUFDest>3.90</vFCPUFDest>
        <pFCPUFDest>2.0000</pFCPUFDest>
        <vBCUFDest>195.00</vBCUFDest>
        <pICMSUFDest>20.0000</pICMSUFDest>
        </ICMSUFDest>
        <COFINS>
        <COFINSNT>
        <CST>06</CST>
        </COFINSNT>
        </COFINS>
        <PIS>
        <PISNT>
        <CST>06</CST>
        </PISNT>
        </PIS>
        </imposto>
        <infAdProd>Lote(s):TFP50L62,</infAdProd>
        </det>
        <det>
        <nItem>108</nItem>
        <prod>
        <cEAN>04547480451862</cEAN>
        <cProd>B0321077116002L</cProd>
        <qCom>1.0000</qCom>
        <cEANTrib>04547480451862</cEANTrib>
        <vUnTrib>195.00000000</vUnTrib>
        <cBenef/>
        <qTrib>1.0000</qTrib>
        <vProd>195.00</vProd>
        <xProd>ISERT 151 ESFERICA D 23.0</xProd>
        <vUnCom>195.00000000</vUnCom>
        <indTot>1</indTot>
        <uTrib>UN</uTrib>
        <NCM>90213920</NCM>
        <uCom>UN</uCom>
        <CFOP>6108</CFOP>
        </prod>
        <imposto>
        <vTotTrib>65.23</vTotTrib>
        <ICMS>
        <ICMS00>
        <modBC>3</modBC>
        <orig>6</orig>
        <CST>00</CST>
        <vBC>195.00</vBC>
        <vICMS>23.40</vICMS>
        <pICMS>12.0000</pICMS>
        </ICMS00>
        </ICMS>
        <IPI>
        <IPINT>
        <CST>51</CST>
        </IPINT>
        <cEnq>999</cEnq>
        </IPI>
        <ICMSUFDest>
        <vBCFCPUFDest>195.00</vBCFCPUFDest>
        <pICMSInter>12.00</pICMSInter>
        <vICMSUFDest>15.60</vICMSUFDest>
        <vICMSUFRemet>0</vICMSUFRemet>
        <pICMSInterPart>100</pICMSInterPart>
        <vFCPUFDest>3.90</vFCPUFDest>
        <pFCPUFDest>2.0000</pFCPUFDest>
        <vBCUFDest>195.00</vBCUFDest>
        <pICMSUFDest>20.0000</pICMSUFDest>
        </ICMSUFDest>
        <COFINS>
        <COFINSNT>
        <CST>06</CST>
        </COFINSNT>
        </COFINS>
        <PIS>
        <PISNT>
        <CST>06</CST>
        </PISNT>
        </PIS>
        </imposto>
        <infAdProd>Lote(s):TFP50L63,</infAdProd>
        </det>
        <det>
        <nItem>109</nItem>
        <prod>
        <cEAN>04547480451862</cEAN>
        <cProd>B0321077116002L</cProd>
        <qCom>1.0000</qCom>
        <cEANTrib>04547480451862</cEANTrib>
        <vUnTrib>195.00000000</vUnTrib>
        <cBenef/>
        <qTrib>1.0000</qTrib>
        <vProd>195.00</vProd>
        <xProd>ISERT 151 ESFERICA D 23.0</xProd>
        <vUnCom>195.00000000</vUnCom>
        <indTot>1</indTot>
        <uTrib>UN</uTrib>
        <NCM>90213920</NCM>
        <uCom>UN</uCom>
        <CFOP>6108</CFOP>
        </prod>
        <imposto>
        <vTotTrib>65.23</vTotTrib>
        <ICMS>
        <ICMS00>
        <modBC>3</modBC>
        <orig>6</orig>
        <CST>00</CST>
        <vBC>195.00</vBC>
        <vICMS>23.40</vICMS>
        <pICMS>12.0000</pICMS>
        </ICMS00>
        </ICMS>
        <IPI>
        <IPINT>
        <CST>51</CST>
        </IPINT>
        <cEnq>999</cEnq>
        </IPI>
        <ICMSUFDest>
        <vBCFCPUFDest>195.00</vBCFCPUFDest>
        <pICMSInter>12.00</pICMSInter>
        <vICMSUFDest>15.60</vICMSUFDest>
        <vICMSUFRemet>0</vICMSUFRemet>
        <pICMSInterPart>100</pICMSInterPart>
        <vFCPUFDest>3.90</vFCPUFDest>
        <pFCPUFDest>2.0000</pFCPUFDest>
        <vBCUFDest>195.00</vBCUFDest>
        <pICMSUFDest>20.0000</pICMSUFDest>
        </ICMSUFDest>
        <COFINS>
        <COFINSNT>
        <CST>06</CST>
        </COFINSNT>
        </COFINS>
        <PIS>
        <PISNT>
        <CST>06</CST>
        </PISNT>
        </PIS>
        </imposto>
        <infAdProd>Lote(s):TFP50L64,</infAdProd>
        </det>
        <det>
        <nItem>110</nItem>
        <prod>
        <cEAN>04547480451862</cEAN>
        <cProd>B0321077116002L</cProd>
        <qCom>1.0000</qCom>
        <cEANTrib>04547480451862</cEANTrib>
        <vUnTrib>195.00000000</vUnTrib>
        <cBenef/>
        <qTrib>1.0000</qTrib>
        <vProd>195.00</vProd>
        <xProd>ISERT 151 ESFERICA D 23.0</xProd>
        <vUnCom>195.00000000</vUnCom>
        <indTot>1</indTot>
        <uTrib>UN</uTrib>
        <NCM>90213920</NCM>
        <uCom>UN</uCom>
        <CFOP>6108</CFOP>
        </prod>
        <imposto>
        <vTotTrib>65.23</vTotTrib>
        <ICMS>
        <ICMS00>
        <modBC>3</modBC>
        <orig>6</orig>
        <CST>00</CST>
        <vBC>195.00</vBC>
        <vICMS>23.40</vICMS>
        <pICMS>12.0000</pICMS>
        </ICMS00>
        </ICMS>
        <IPI>
        <IPINT>
        <CST>51</CST>
        </IPINT>
        <cEnq>999</cEnq>
        </IPI>
        <ICMSUFDest>
        <vBCFCPUFDest>195.00</vBCFCPUFDest>
        <pICMSInter>12.00</pICMSInter>
        <vICMSUFDest>15.60</vICMSUFDest>
        <vICMSUFRemet>0</vICMSUFRemet>
        <pICMSInterPart>100</pICMSInterPart>
        <vFCPUFDest>3.90</vFCPUFDest>
        <pFCPUFDest>2.0000</pFCPUFDest>
        <vBCUFDest>195.00</vBCUFDest>
        <pICMSUFDest>20.0000</pICMSUFDest>
        </ICMSUFDest>
        <COFINS>
        <COFINSNT>
        <CST>06</CST>
        </COFINSNT>
        </COFINS>
        <PIS>
        <PISNT>
        <CST>06</CST>
        </PISNT>
        </PIS>
        </imposto>
        <infAdProd>Lote(s):TFP50L65,</infAdProd>
        </det>
        <total>
        <retTrib>
        <vIRRF>257.40</vIRRF>
        <vBCIRRF>21450.00</vBCIRRF>
        </retTrib>
        <ICMSTot>
        <vICMSUFDest>1716.00</vICMSUFDest>
        <vICMSUFRemet>0</vICMSUFRemet>
        <vCOFINS>0</vCOFINS>
        <vBCST>0</vBCST>
        <vICMSDeson>0</vICMSDeson>
        <vFCPUFDest>429.00</vFCPUFDest>
        <vProd>21450.00</vProd>
        <vSeg>0</vSeg>
        <vFCP>0</vFCP>
        <vFCPST>0</vFCPST>
        <vNF>21450.00</vNF>
        <vTotTrib>7175.30</vTotTrib>
        <vPIS>0</vPIS>
        <vIPIDevol>0</vIPIDevol>
        <vBC>21450.00</vBC>
        <vST>0</vST>
        <vICMS>2574.00</vICMS>
        <vII>0</vII>
        <vFCPSTRet>0</vFCPSTRet>
        <vDesc>0</vDesc>
        <vOutro>0</vOutro>
        <vIPI>0</vIPI>
        <vFrete>0</vFrete>
        </ICMSTot>
        </total>
        <cobr>
        <fat>
        <vOrig>21192.60</vOrig>
        <nFat>1000435888</nFat>
        <vDesc>0</vDesc>
        <vLiq>21192.60</vLiq>
        </fat>
        <dup>
        <dVenc>2024-09-06</dVenc>
        <nDup>001</nDup>
        <vDup>21192.60</vDup>
        </dup>
        </cobr>
        <pag>
        <detPag>
        <vPag>21450.00</vPag>
        <tPag>01</tPag>
        <indPag>1</indPag>
        </detPag>
        </pag>
        <entrega>
        <cPais>1058</cPais>
        <xLgr>BOULEVARD VINTE E OITO DE SETEMBRO</xLgr>
        <nro>77</nro>
        <cMun>3304557</cMun>
        <xBairro>VILA ISABEL</xBairro>
        <CEP>20551900</CEP>
        <fone>2123342240</fone>
        <xNome>UNIVERSIDADE DO ESTADO DO RIO DE JANEIRO</xNome>
        <UF>RJ</UF>
        <xPais>BRASIL</xPais>
        <xMun>RIO DE JANEIRO</xMun>
        <CNPJ>33540014000157</CNPJ>
        <email>[email protected]</email>
        </entrega>
        <ide>
        <tpNF>1</tpNF>
        <mod>55</mod>
        <indPres>9</indPres>
        <tpImp>1</tpImp>
        <nNF>435888</nNF>
        <cMunFG>3505708</cMunFG>
        <procEmi>0</procEmi>
        <finNFe>1</finNFe>
        <dhEmi>2024-08-07T11:08:00-03:00</dhEmi>
        <tpAmb>1</tpAmb>
        <indFinal>1</indFinal>
        <dhSaiEnt>2024-08-07T11:08:00-03:00</dhSaiEnt>
        <idDest>2</idDest>
        <tpEmis>1</tpEmis>
        <cDV>8</cDV>
        <cUF>35</cUF>
        <serie>1</serie>
        <natOp>VENDA DE MERCADORIA ADQUIRIDA OU RECEBIDA DE TERCEIROS, DEST</natOp>
        <cNF>24805218</cNF>
        <verProc>12.1.2310 | 3.0</verProc>
        <indIntermed>0</indIntermed>
        </ide>
        <Id>NFe35240896382429000160550010004358881248052188</Id>
        <dest>
        <xNome>UNIVERSIDADE DO ESTADO DO RIO DE JANEIRO</xNome>
        <CNPJ>33540014000157</CNPJ>
        <enderDest>
        <fone>02123342240</fone>
        <UF>RJ</UF>
        <xPais>BRASIL</xPais>
        <cPais>1058</cPais>
        <xLgr>R SAO FRANCISCO XAVIER</xLgr>
        <xMun>RIO DE JANEIRO</xMun>
        <nro>524</nro>
        <cMun>3304557</cMun>
        <xBairro>MARACANA</xBairro>
        <CEP>20550013</CEP>
        </enderDest>
        <IE>76003939</IE>
        <indIEDest>9</indIEDest>
        <email>[email protected]</email>
        </dest>
        <emit>
        <CNAE>4645101</CNAE>
        <xNome>ADAPT PRODUTOS OFTALMOLOGICOS LTDA</xNome>
        <IM>5AP3754</IM>
        <CRT>3</CRT>
        <xFant>ADAPT-FIL/BARUERI</xFant>
        <CNPJ>96382429000160</CNPJ>
        <enderEmit>
        <xCpl>GALPAO 5</xCpl>
        <fone>1150991900</fone>
        <UF>SP</UF>
        <xPais>BRASIL</xPais>
        <cPais>1058</cPais>
        <xLgr>RUA VICTORINO</xLgr>
        <xMun>BARUERI</xMun>
        <nro>207</nro>
        <cMun>3505708</cMun>
        <xBairro>JARDIM MUTINGA</xBairro>
        <CEP>06463290</CEP>
        </enderEmit>
        <IE>206810441111</IE>
        <IEST>92015840</IEST>
        </emit>
        <transp>
        <modFrete>0</modFrete>
        <vol>
        <pesoL>7.700</pesoL>
        <esp>CAIXA</esp>
        <qVol>1</qVol>
        <pesoB>8.536</pesoB>
        </vol>
        <transporta>
        <xNome>CORREIOS SEDEX COMUM</xNome>
        <UF>SP</UF>
        <xEnder>RUA GUARARAPES, 1832</xEnder>
        <xMun>SAO PAULO</xMun>
        <CNPJ>34028316000103</CNPJ>
        <IE>ISENTO</IE>
        </transporta>
        </transp>
        </infNFe>
        </NFe>
        <versao>4.00</versao>
帖子版权声明 1、本帖标题:IMPORTXML Google 表格公式不起作用
    本站网址:http://xjnalaquan.com/
2、本网站的资源部分来源于网络,如有侵权,请联系站长进行删除处理。
3、会员发帖仅代表会员个人观点,并不代表本站赞同其观点和对其真实性负责。
4、本站一律禁止以任何方式发布或转载任何违法的相关信息,访客发现请向站长举报
5、站长邮箱:yeweds@126.com 除非注明,本帖由sancho.s ReinstateMonicaCellio在本站《xml》版块原创发布, 转载请注明出处!
最新回复 (0)
  • 缺少公式。此外,这个问题对于本网站来说太宽泛了。请将问题范围缩小到编程特有的特定问题。

  • 作为 XML 解析的替代方法,您可以在下面的函数中使用 \'regex\',并像普通公式一样在工作表上使用它,例如 =getData()

    function getData() {
      const url = 'https://www.dropbox.com/scl/fi/ozjw0tkjwais45f6do0ai/35240896382429000160550010004358881248052188.xml?rlkey=ddztt2vqxzr3ch1kwyk4enve5&st=k0ps6ehm&dl=1';
      
      const response = UrlFetchApp.fetch(url);
      const content = response.getContentText();
      
      const results = content.match(/versao=(".+?")/g);
      return results[0];
    }
    

    您也可以使用以下功能;

    function getVersion() {
      const url = 'https://www.dropbox.com/scl/fi/ozjw0tkjwais45f6do0ai/35240896382429000160550010004358881248052188.xml?rlkey=ddztt2vqxzr3ch1kwyk4enve5&st=k0ps6ehm&dl=1';
      
      const response = UrlFetchApp.fetch(url);
      const content = response.getContentText();
      
      var regExp = new RegExp('(versao=")(.+?)"',"g");
      var version = parseFloat(regExp.exec(content)[2]);
      Logger.log(version);
      return version;
    }
    
返回
作者最近主题: