gpt4 book ai didi

javascript - Google 脚本用于编译多个不同电子表格中的数据,这些电子表格是同一文件的副本

转载 作者:行者123 更新时间:2023-12-03 02:11:39 26 4
gpt4 key购买 nike

我有一个电子表格,我制作了多个副本,以便为不同的人输入信息。我试图找到一种方法来编译数据,而不必逐个调用每个电子表格,因为它们都是从同一个文件复制的并且具有相似的标题。有没有办法搜索该标题并从 Google 表格中包含该标题的任何电子表格中编译数据?

如果有帮助,这是我正在尝试从中编译数据的电子表格。我试图全面比较优点/缺点,而不必一一查看每个人的电子表格: https://docs.google.com/spreadsheets/d/1hiw3Z_BVpJY6J0osn6-XRS9IpieotH8CRIsiacmW068/edit?usp=sharing

这是我到目前为止所拥有的:

function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var searchMenuEntries = [ {name: "Search in all files", functionName: "search"}];
ss.addMenu("Search Google Drive", searchMenuEntries);
}

function search() {
// Prompt the user for a search term
var searchTerm = Browser.inputBox("Enter the String to Search For:");

// Get the active spreadsheet and the active sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

// Set up the spreadsheet to display the results
var headers = [["Unit 8 Project Data", "KU", "MI", "SE", "CO", "File Type", "URL"]];
sheet.clear();
sheet.getRange("A1:G1").setValues(headers);
// Search the files in the user's Google Drive for the search term
// See documentation for search parameters you can use
// https://developers.google.com/apps-script/reference/drive/drive-app#searchFiles(String)
var files = DriveApp.searchFiles("title contains
'"+searchTerm.replace("'","\'")+"'");

// create an array to store our data to be written to the sheet
var output = [];
// Loop through the results and get the file name, file type, and URL
while (files.hasNext()) {
var file = files.next();
var name = file.getName();
var startRow = 9; // First row of data to process
var numRows = 10; // Number of rows to process
var type = file.getMimeType();
var url = file.getUrl();
var id = file.getId();
var sheet = SpreadsheetApp.openById(id).getActiveSheet();
var dataRange = sheet.getRange(startRow, 6, numRows, 1)
var data = dataRange.getValues();
var KU = data[0][0];
var MI = data[3][0];
var SE = data[6][0];
var CO = data[9][0];
// push the file details to our output array (essentially pushing a row of data)
output.push([name, KU, MI, SE, CO, type, url]);
}
// write data to the sheet
sheet.getRange(2, 1, output.length, 7).setValues(output);
}

它正在查找电子表格,但没有获取电子表格内的数据(这是我自己编写的部分)。这些点显示为空白。下车后我会尝试挖掘更多,但这就是我到目前为止所拥有的。

最佳答案

想通了!感谢您的帮助!

function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var searchMenuEntries = [ {name: "Search in all files", functionName: "search"}];
ss.addMenu("Search Google Drive", searchMenuEntries);
}

function search() {
// Prompt the user for a search term
var searchTerm = Browser.inputBox("Enter the String to Search For:");

// Get the active spreadsheet and the active sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheeta = ss.getActiveSheet();

// Set up the spreadsheet to display the results
var headers = [["Unit 8 Project Data", "KU", "MI", "SE", "CO", "File Type", "URL"]];
sheeta.clear();
sheeta.getRange("A1:G1").setValues(headers);
// Search the files in the user's Google Drive for the search term
// See documentation for search parameters you can use
// https://developers.google.com/apps-script/reference/drive/drive-app#searchFiles(String)
var files = DriveApp.searchFiles("title contains
'"+searchTerm.replace("'","\'")+"'");

// create an array to store our data to be written to the sheet
var output = [];
// Loop through the results and get the file name, file type, and URL
while (files.hasNext()) {
var file = files.next();
var name = file.getName();
var startRow = 9; // First row of data to process
var numRows = 10; // Number of rows to process
var type = file.getMimeType();
var url = file.getUrl();
var id = file.getId();
var sheet = SpreadsheetApp.openById(id).getActiveSheet();
var dataRange = sheet.getRange(startRow, 6, numRows, 1)
var data = dataRange.getValues();
var KU = data[0][0];
var MI = data[3][0];
var SE = data[6][0];
var CO = data[9][0];
// push the file details to our output array (essentially pushing a row of data)
output.push([name, KU, MI, SE, CO, type, url]);
}
// write data to the sheet
sheeta.getRange(2, 1, output.length, 7).setValues(output);
}

关于javascript - Google 脚本用于编译多个不同电子表格中的数据,这些电子表格是同一文件的副本,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/49535623/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com