Google电子表格中的Google驱动器中的参考文件 [英] Reference file in Google drive in Google Spreadsheet
问题描述
我在Google云端硬盘中创建了一个文件夹,例如测试并在其中放置3个文件
a.jpg,b.jpg,c.jpg
我想在我的Google Spreadsheet下的同一个帐户下访问这些文件,例如生成图像文件的链接,是否有可能?
例如
ColA ColB
a.jpg [在驱动器中生成链接到a.jpg]
a.jpg [在驱动器中生成链接到b.jpg]
a.jpg [生成链接到c。 jpg in drive]
使用GScripts,您可以使用以下函数创建/修改文件:
这一个创建一个新的电子表格,包含所有的文件夹名称和所有对应的URL(无标题)
函数mapAllFiles(){
var values = [];
var folders = DriveApp.getFoldersByName(FOLDER_NAME);
while(folders.hasNext()){
var folder = folders.next();
var files = folder.getFiles();
//使用给定名称在所有文件夹中循环所有文件
while(files.hasNext()){
var file = files.next();
Logger.log(file.getName());
var fileInfo = [file.getName(),file.getDownloadUrl()];
Logger.log(fileInfo);
values.push(fileInfo);
// values.push(file.getDownloadUrl());
}
}
//使用正在退出的电子表格
// var sFile = DriveApp.getFilesByName(YOUR_SPREADSHEET_NAME);
// var as = SpreadsheetApp.openById(sFile.next()。getId());
//创建一个新的电子表格
var as = SpreadsheetApp.create(YOUR_SPREADSHEET_NAME);
var sheet = as.getActiveSheet();
//填写所有值
var range = sheet.getRange(1,1,values.length,2).setValues(values);
$ / code>
这个列出了电子表格中给定文件名的URL,该文件夹
函数mapListedFiles(){
var values = [];
var folders = DriveApp.getFoldersByName(FOLDER_NAME);
if(folders.hasNext()){
var folder = folders.next();
var sFile = DriveApp.getFilesByName(YOUR_SPREADSHEET_NAME);
var as = SpreadsheetApp.openById(sFile.next()。getId());
// var as = SpreadsheetApp.create(YOUR_SPREADSHEET_NAME);
var sheet = as.getActiveSheet();
var range = sheet.getRange(2,1,sheet.getDataRange()。getLastRow(),1).getValues();
for(var i = 0; i< range.length-1; i ++){
var files = folder.getFilesByName(range [i]);
Logger.log(文件);
if(files.hasNext()){
var file = files.next();
Logger.log(file.getName());
Logger.log(file.getDownloadUrl());
values.push([file.getDownloadUrl()]);
} else {
values.push([MISSING]);
}
}
var range = sheet.getRange(2,2,values.length,1).setValues(values);
}
}
I have created a folder in Google Drive, e.g. test, and put 3 files inside it
a.jpg, b.jpg, c.jpg
I want in my Google Spreadsheet under the same account, to access the files, e.g. generate the link to image file, is it possible?
e.g.
ColA ColB
a.jpg [generate link to a.jpg in drive]
a.jpg [generate link to b.jpg in drive]
a.jpg [generate link to c.jpg in drive]
With GScripts you can use the following functions to create/modify the files:
This one creates a new Spreadsheet with all Folder names and all corresponding URLS (no header)
function mapAllFiles() {
var values = [];
var folders = DriveApp.getFoldersByName("FOLDER_NAME");
while (folders.hasNext()) {
var folder = folders.next();
var files = folder.getFiles();
// Looping over all files in all folders with the given name
while (files.hasNext()) {
var file = files.next();
Logger.log(file.getName());
var fileInfo = [file.getName(), file.getDownloadUrl()];
Logger.log(fileInfo);
values.push(fileInfo);
// values.push(file.getDownloadUrl());
}
}
// To use an exiting spreadsheet
// var sFile = DriveApp.getFilesByName("YOUR_SPREADSHEET_NAME");
// var as = SpreadsheetApp.openById(sFile.next().getId());
// Create a new spreadsheet
var as = SpreadsheetApp.create("YOUR_SPREADSHEET_NAME");
var sheet = as.getActiveSheet();
// Fill in all values
var range = sheet.getRange(1, 1, values.length, 2).setValues(values);
}
This one list the URLs for the given fileNames in the spreadsheet which are present in the folder
function mapListedFiles() {
var values = [];
var folders = DriveApp.getFoldersByName("FOLDER_NAME");
if (folders.hasNext()) {
var folder = folders.next();
var sFile = DriveApp.getFilesByName("YOUR_SPREADSHEET_NAME");
var as = SpreadsheetApp.openById(sFile.next().getId());
// var as = SpreadsheetApp.create("YOUR_SPREADSHEET_NAME");
var sheet = as.getActiveSheet();
var range = sheet.getRange(2,1,sheet.getDataRange().getLastRow(), 1).getValues();
for (var i = 0; i < range.length-1; i++) {
var files = folder.getFilesByName(range[i]);
Logger.log(files);
if (files.hasNext()) {
var file = files.next();
Logger.log(file.getName());
Logger.log(file.getDownloadUrl());
values.push([file.getDownloadUrl()]);
} else {
values.push(["MISSING"]);
}
}
var range = sheet.getRange(2, 2, values.length, 1).setValues(values);
}
}
这篇关于Google电子表格中的Google驱动器中的参考文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!