来自多个Google电子表格的导入范围 [英] Importrange from multiple google spread sheets
问题描述
我知道如何从一张电子表格导入数据到另一张:
= IMPORTRANGE(spreadsheet_key,range_string)
资料来源: https://support.google.com/docs/answer/3093340?hl=zh-CN&ref_topic=3105411
但是,如何将多张电子表格导入一张主电子表格?
类似这样的内容:
= IMPORTRANGE(spreadsheet_key,range_string),IMPORTRANGE(spreadsheet_key,range_string),IMPORTRANGE(spreadsheet_key,range_string)
所有电子表格都具有完全相同的结构。
我希望同一列中的所有数据位于同一列。
$ b
$ b
$ b
$ b
mula
您可以连接值和范围。 请尝试以下操作: 自动导入文件夹中所有工作表的一种粗略方法。 这不会处理变小的范围,因此不会覆盖旧数据或任何幻想。 I know how to import data from ONE spread sheet to another: Source: https://support.google.com/docs/answer/3093340?hl=en&ref_topic=3105411 But how do I import MULTIPLE spread sheets into one "master spread sheet"? Something like this: All spread sheets have the exact same structure. I want all data in the same sheet in the same columns. Preferebly I would like to import all spread sheets automaticly that are placed in one folder into the "master spread sheat". You can concatenate values and ranges. Try the following:
A rough method to automatically import all sheets in a folder. This doesn't handle ranges that get smaller and thus not overwrite the old data or anything fancy. 这篇关于来自多个Google电子表格的导入范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
在英文语言环境中,您可以使用连接列,
和;
并将它们放入} $ c
= {IMPORTRANGE (spreadsheet_key1,range_string1);
IMPORTRANGE(spreadsheet_key2,range_string2);
IMPORTRANGE(spreadsheet_key3,range_string3)}
函数
函数import(){
var spreadsheets = DriveApp。
getFolderById(YOURFOLDERID)。
getFilesByType(MimeType.GOOGLE_SHEETS);
var data = []; (spreadsheets.hasNext()){
var currentSpreadsheet = SpreadsheetApp.openById(spreadsheets.next()。getId());
data = data.concat(currentSpreadsheet。
getSheetByName(YOURSTANDARDSHEETNAME)。
getRange(YOURSTANDARDDATARANGE)。
getValues());
}
SpreadsheetApp。
getActiveSheet()。
getRange(1,1,data.length,data [0] .length)。
setValues(data);
}
它仍然需要手动触发。=IMPORTRANGE(spreadsheet_key, range_string)
=IMPORTRANGE(spreadsheet_key, range_string),IMPORTRANGE(spreadsheet_key, range_string),IMPORTRANGE(spreadsheet_key, range_string)
Formula
In the English locale you concatenate columns with ,
and rows with ;
annd putting them in curlies {
and }
.={IMPORTRANGE(spreadsheet_key1, range_string1);
IMPORTRANGE(spreadsheet_key2, range_string2);
IMPORTRANGE(spreadsheet_key3, range_string3)}
Function
function import() {
var spreadsheets = DriveApp.
getFolderById("YOURFOLDERID").
getFilesByType(MimeType.GOOGLE_SHEETS);
var data = [];
while (spreadsheets.hasNext()) {
var currentSpreadsheet = SpreadsheetApp.openById(spreadsheets.next().getId());
data = data.concat(currentSpreadsheet.
getSheetByName("YOURSTANDARDSHEETNAME").
getRange("YOURSTANDARDDATARANGE").
getValues());
}
SpreadsheetApp.
getActiveSheet().
getRange(1, 1, data.length, data[0].length).
setValues(data);
}
It also still needs to be manually triggered.