Google Apps Script (GAS) & Google Sheets: how to get spreadsheetId?

0
votes

I'm noob in Google Apps Script (GAS) & intensively reading & trying out appropriate docs. I'm writing here in hope that someone already knows the answers to my question. Below GAS returns error on "ReferenceError: spreadsheetId is not defined".

Question: anyone knows how to get spreadsheetId in the GAS?

function myFunction() {

  var sheet = Sheets.newSpreadsheet();
  var title = 'title';
  sheet.properties = Sheets.newSpreadsheetProperties();
  sheet.properties.title = title;
  var spreadsheet = Sheets.Spreadsheets.create(sheet);
}



function writeToSheet() {

var values = [
  [
    // Cell values ...
  ]
  // Additional rows ...
];
var valueRange = Sheets.newValueRange();
valueRange.values = values;
var result = Sheets.Spreadsheets.Values.update(valueRange, spreadsheetId, range, {
  valueInputOption: valueInputOption
});


}
1
google-apps-scriptgoogle-sheets

1 Answers

1
votes

From your script, when you want to put the values with Sheets.Spreadsheets.Values.update, after the new Google Spreadsheet was created with Sheets.Spreadsheets.create, how about the following modification?

Modified script:

function myFunction() {
  // This is your script of "myFunction".
  var sheet = Sheets.newSpreadsheet();
  var title = 'title';
  sheet.properties = Sheets.newSpreadsheetProperties();
  sheet.properties.title = title;
  var spreadsheet = Sheets.Spreadsheets.create(sheet);

  // --- I added the following script.
  var spreadsheetId = spreadsheet.spreadsheetId;
  var valueInputOption = "USER_ENTERED";
  var range = "A1";
  var values = [["a1", "b1", "c1"], ["a2", "b2", "c2"]];
  // ---

  // This is your script of "writeToSheet".
  var valueRange = Sheets.newValueRange();
  valueRange.values = values;
  var result = Sheets.Spreadsheets.Values.update(valueRange, spreadsheetId, range, {
    valueInputOption: valueInputOption
  });
}
  • When above script is run, a new Spreadsheet created and the values of [["a1", "b1", "c1"], ["a2", "b2", "c2"]] are put to the 1st tab.
  • spreadsheetId can be retrieved from var spreadsheet = Sheets.Spreadsheets.create(sheet) like spreadsheet.spreadsheetId.
  • In this case, valueInputOption and range are required to be set. In this modification, as the sample values, USER_ENTERED and A1 are used, respectively.
    • For range, when the sheet name is not used, the value is put to the 1st tab. In your case, the created new Spreadsheet has only one tab. So I thought that this might be suitable.

Note:

  • In this modification, it supposes that Sheets API has already been enabled at Advanced Google services. Please be careful this.

References: