英文:
IMPORTRANGE in Google Sheets preserving the structure of the destination sheet
问题
假设我们有以下的谷歌表格（称为 File_1）：
我们通过 IMPORTRANGE("https://docs.google.com/spreadsheets/...", "Sheet1!A:C") 导入所有列（从A到C）到另一个工作表（称为 File_2），该工作表还包含了一个名为 New_col 的附加列，并带有一些数据：
现在，假设源工作表发生了变化，就像这样，也就是说，在现有行之间添加了一行：
目标工作表将变成这样，本质上保留了列D的先前状态，并且破坏了单元格D2中“测试”值与A1-B1-C1行的关系。
我想要的是以下目标工作表：
在谷歌表格内是否有实现这一点的方法？
英文:
Suppose that we have the following Google Sheet (called File_1):
And we import all the columns (A to C) via IMPORTRANGE("https://docs.google.com/spreadsheets/...", "Sheet1!A:C") into another sheet (called File_2), which also contains an additional column New_col with some data in it:
Now, suppose that the source sheet changes like this, i.e., a new row is added in-between the existing rows:
The destination sheet will become like this, in essence keeping Column D in its previous state and 'breaking' the relation of the 'test' value in cell D2 with the A1-B1-C1 row.
What I would like to have instead is the following destination sheet:
Is there a way to do that from within Google Sheets?
答案1
得分: 2
你描述了公式结果与手动输入数据不对齐的情况。目前没有一键解决方案可解决此问题。Lance已经对行不对齐问题进行了详细处理，并说明在某些情况下可以如何处理。
英文:
You are describing how formula results get misaligned with manually entered data. There is no turn-key solution to work around the issue. Lance has given a thorough treatment of the row misalignment issue and how it can be dealt with in some cases.
答案2
得分: 0
我为我们的用例创建了一些自定义代码来解决这个问题：
https://github.com/giantswarm/gapps-automation/tree/main/sticky-rows
它相当于在两个范围（动态和粘性）上执行内连接，并相应地对粘性范围（带有手动数据）进行排序。
英文:
I created some custom code to solve this problem for our use-case:
https://github.com/giantswarm/gapps-automation/tree/main/sticky-rows
It amounts to doing an INNER JOIN on the two ranges (dynamic and sticky) and sorting the sticky range (with manual data) accordingly.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论