Creating Spreadsheets Using Advanced Drive Services

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 crea

Solution 1:

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 foreach (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
};

functioncreateFile(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 foreach (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 ##"
)