使用Advanced Drive Services创建电子表格 [英] Creating spreadsheets using Advanced Drive Services
问题描述
我试图弄清楚如何在Google Apps脚本中使用高级云端硬盘服务做一些事情。
1） 创建一个包含x行和列的电子表格：
创建新电子表格时，我让它只有1行6列？现在，我使用＃2中的函数创建电子表格，然后删除行和列。但是，如果我可以让电子表格具有一定数量的行和列，那将会更好。
$ b $ 2）设置电子表格的标题：
如何将createFile函数（）中的电子表格的标题设置为值'name'？现在它创建名为'untitled'的电子表格。
var file = {
title：，
mimeType：MimeType.GOOGLE_SHEETS，
父母：[{id：artistSpreadSheetsFolderId}] //我有文件夹ID
};
函数createFile（名称）
{
var fileJson = Drive.Files.insert（file）;
var fileId = fileJson.id;
//我如何将电子表格的标题设置为'name'值？
}
3） 通过名称获取文件的标识：
如果我将一个名称传入一个函数，我将如何获取使用高级云端硬盘服务的电子表格？这些文件将在一个文件夹中，因此我将拥有该文件夹的ID。
function getFileId（name）
{
//通过名称获取文件
//获取文件ID
}
@Tanaike
对于第一个，使用高级驱动器服务无法做到这一点？如果没有，我将如何去添加一个值的数组到第一行？目前我有这样的： var headers = [[1，2，3，4，5，6]]; 然后我做 sheet.getRange（range）.setValues（headers）; 。有没有办法在spreadsheet.create的属性部分设置这些值？
第二，我将var文件放在函数之外，因此多个函数可以叫它。我想在电子表格创建之后（在var fileId之后）设置电子表格的名称，以传递给它的名称，就像 - .setTitle（name）或类似的东西。所以在一个函数中，我可以做var file = Drive.Files.insert（file）;然后就像var fileName = //在这里设置名称一样。
第三，这只是一个for循环吗？ like for（var i = 0; i< drive.files.list.length; i ++）？
答案？为了使用以下脚本，请为高级云端硬盘服务和Google API控制台启用云端硬盘API和图表API。 $ b
参考文献：
启用Drive API
https://developers.google.com/drive/v3/web/enable-sdk
Google表格API v4
https://developers.google.com/sheets/api/
1。使用x行数和列数创建电子表格
为此，您可以使用Google Sheet API。电子表格可以通过给定行数和列数作为初始属性来创建。但是，在这种方法下，父文件夹创建时不能设置。
Sheets.Spreadsheets.create（{
sheets）创建后，父文件夹需要更改。 ：[
{
properties：{
gridProperties：{
columnCount：6，
rowCount：1
}
properties：{
title：filename
}
}）;
2。设置电子表格的标题
我可能无法理解您的问题。您是否想创建一个名称设置为新的电子表格？或者你想检索创建的电子表格的名称？如果没有，请随时告诉我。
var res = Drive.Files.insert（{
title：test，//这里给出了一个新的电子表格的标题
mimeType：application / vnd.google-apps.spreadsheet，
parents：[ id：#####}]
}）;
Logger.log（res.title）//这里，它检索创建的电子表格的标题。
3。通过名称获取文件的标识符
文件标识符以数组的形式返回。假设有相同名称的文件。
function getFileId（name）{
return [i.id （我在Drive.Files.list（{q：title ='+ name +'and trashed = false}）。items）];
}
对于我可怜的英语，我很遗憾。
对新问题的回答1
A1：第一个，它使用先进的驱动器服务。如果您想使用此API导入数据，如下所示。下面的示例分别导入 1，2，3 至 a1，b1，c1 。在这个脚本的第二次运行中，数据被添加到第一个数据的下面。
var values = [[1,2 ，3]];
Sheets.Spreadsheets.Values.append（
{values：values}，
'## Sheet ID ##'，
'a1'，
{ valueInputOption：'RAW'}
）
A2：您可以向JSON添加数据，如下所示。
var file = {
title：，
mimeType ：MimeType.GOOGLE_SHEETS，
父母：[{id：artistSpreadSheetsFolderId}] //我有文件夹ID
};
函数createFile（名称）
{
file.title = name;
var fileJson = Drive.Files.insert（file）;
var fileId = fileJson.id;
//我如何将电子表格的标题设置为'name'值？
A3：
这是理解。
return [i.id for each（i in Drive.Files。 list（{q：title ='+ name +'and trashed = false}）。items）];
这与以下脚本相同。
var items = Drive.Files.list（{q：title ='+ name +'and trashed = false}）。
var ar = [];
for（var i = 0; i< items.length; i ++）{
ar.push（items [i] .id）;
}
return ar;
对新问题的回答2
A1：创建电子表格时包含数据的方法如下。在以下脚本中， 1，2，3 的数据被导入到 a1，a2，a3 中。有各种参数。所以请在这里查看 https://developers.google.com/sheets/ api / reference / rest / v4 / spreadsheets 。
Sheets.Spreadsheets.create（{
sheet：[
{
properties：{
gridProperties：{
columnCount：6，
rowCount：1
}
，
data：
[
{
rowData：
[
{
values ：
[userEnteredValue：{numberValue：1}}，
{userEnteredValue：{numberValue：2}}，
{userEnteredValue ：{numberValue：3}}
]
}
]
}
]
}
]，
属性：{
title：文件名
}
}）;
A2：您可以使用以下脚本移动文件夹。
Drive.Files.update（
{parents：[{id：## Folder ID ##} ]}，
##文件ID ##
）
I'm trying to figure out how to do a couple of things using Advanced Drive Services in Google Apps Script.
1) Creating a spreadsheet with x amount of rows and columns:
When creating a new spreadsheet, how could I make it only have 1 row and 6 columns? Right now, I create the spreadsheet using the function in #2 and then delete the rows and columns. But it would be better if I could just make the spreadsheet have a certain number of rows and columns instead.
2) Setting the title of the spreadsheet:
How would I set the title of the spreadsheet in the createFile function() to the value 'name'? Right now it creates the spreadsheet with the name 'untitled'.
var file = {
title: "",
mimeType: MimeType.GOOGLE_SHEETS,
parents: [{id: artistSpreadSheetsFolderId}] //I have the folder id
};
function createFile(name)
{
var fileJson = Drive.Files.insert(file);
var fileId = fileJson.id;
//how would I set the title of the spreadsheet to the value 'name'?
}
3) Getting the id of a file by the name:
If I pass a name into a function, how would I get the id of that spreadsheet using Advanced Drive Services? The files would be in a folder so I would have the id of that folder already.
function getFileId(name)
{
//get file by name
//get that files id
}
@Tanaike
For the first one, theres no way to do that using advanced drive services? If not, how would I go about adding an array of values into the 1st row? currently I have this: var headers = [["1", "2", "3", "4,", "5", "6"]]; and then I do sheet.getRange(range).setValues(headers);. Is there a way to set these values in the properties part of the spreadsheet.create?
For the second, I have the var file outside of the function so multiple functions can call it. I wanted to set the name of the spreadsheet after it's created (after the var fileId) to the name thats being passed in, so like - .setTitle(name) or something like that. So in a function I can just do var file = Drive.Files.insert(file); and then do like var fileName = //set name here.
For the third, is that just a for loop? like for(var i = 0; i < drive.files.list.length; i++)?
How about following answers? In order to use following script, please enable Drive API and Sheet API for Advanced Drive Services and Google API Console.
References :
Enable the Drive API https://developers.google.com/drive/v3/web/enable-sdk
Google Sheets API v4 https://developers.google.com/sheets/api/
1. Creating a spreadsheet with x amount of rows and columns
For this, you can use Google Sheet API. Spreadsheet can be created by given the number of rows and columns as an initial properties. However, at this method, parent folder cannot be set when it's created. After created, the parent folder is necessary to be changed.
Sheets.Spreadsheets.create({
"sheets": [
{
"properties": {
"gridProperties": {
"columnCount": 6,
"rowCount": 1
}
}
}
],
"properties": {
"title": "filename"
}
});
2. Setting the title of the spreadsheet
I might be not able to understand your question. Do you want to create a new spreadsheet with a name you set? Or do you want to retrieve name of created spreadsheet? If there are not both, feel free to tell me.
var res = Drive.Files.insert({
"title": "test", // Here, it gives a title of new spreadsheet.
"mimeType": "application/vnd.google-apps.spreadsheet",
"parents": [{"id": "#####"}]
});
Logger.log(res.title) // Here, it retrieves the title of created spreadsheet.
3. Getting the id of a file by the name
File ID is returned as an array. It was assumed that there are files with same names.
function getFileId(name){
return [i.id for each (i in Drive.Files.list({q: "title='" + name + "' and trashed=false"}).items)];
}
I'm sorry about my poor English.
Answes for new questions 1
A1 : For the first one, it uses advanced drive services. If you want to import data using this API, it's as follows. Following sample imports 1, 2, 3 to a1, b1, c1, respectively. At the second run of this script, the data is added to below the first data.
var values = [[1, 2, 3]];
Sheets.Spreadsheets.Values.append(
{"values": values},
'## Sheet ID ##',
'a1',
{valueInputOption: 'RAW'}
)
A2 : You can add data to JSON as follows.
var file = {
title: "",
mimeType: MimeType.GOOGLE_SHEETS,
parents: [{id: artistSpreadSheetsFolderId}] //I have the folder id
};
function createFile(name)
{
file.title = name;
var fileJson = Drive.Files.insert(file);
var fileId = fileJson.id;
//how would I set the title of the spreadsheet to the value 'name'?
}
A3 :
This is comprehension.
return [i.id for each (i in Drive.Files.list({q: "title='" + name + "' and trashed=false"}).items)];
This is the same to following script.
var items = Drive.Files.list({q: "title='" + name + "' and trashed=false"}).items;
var ar = [];
for (var i=0; i<items.length; i++){
ar.push(items[i].id);
}
return ar;
Answes for new questions 2
A1 : The method including data when it creates spreadsheet is as follows. At the following script, data of 1, 2, 3 is imported to a1, a2, a3. There are various parameters. So please check here https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.
Sheets.Spreadsheets.create({
"sheets": [
{
"properties": {
"gridProperties": {
"columnCount": 6,
"rowCount": 1
}
},
"data":
[
{
"rowData":
[
{
"values":
[
{"userEnteredValue": {"numberValue": 1}},
{"userEnteredValue": {"numberValue": 2}},
{"userEnteredValue": {"numberValue": 3}}
]
}
]
}
]
}
],
"properties": {
"title": "filename"
}
});
A2 : You can move folder using following script.
Drive.Files.update(
{"parents": [{"id": "## Folder ID ##"}]},
"## File ID ##"
)
这篇关于使用Advanced Drive Services创建电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
