Creating spreadsheets using Advanced Drive Services

1
votes

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++)?

1
javascriptgoogle-apps-script

1 Answers

2
votes

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)];
}

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 ##"
)