Retrieving the name of leftmost sheet via google scripts

0
votes

I am creating a script to automatically add data to the first sheet/tab of a spreadsheet. I need to do this through a script.google.com as there are various spreadsheets and I want to keep this code in a single script. Also, the name of the first tab changes every week.

I can add data to cells if I know the sheets name using range: "Sheet1!A2". However I want to do this without knowing "Sheet1". Something like newestSheet+"!A2".

function myFunction(){
 updateGoogleSheet("My Sheet ID");
}

function updateGoogleSheet(spreadsheetId) {
 var newestSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getName();

 var data = [
   {  
     range: newestSheet+"!A2",   // Update single cell
     values: [
       ["2"]
     ]
   },
 ];

 var resource = {
   valueInputOption: "USER_ENTERED",
   data: data
 };

 Sheets.Spreadsheets.Values.batchUpdate(resource, spreadsheetId);
}

SpreadsheetApp doesn't work with script.google.com. Is there an alternative to "getSheets()[0]" to return the name of the first sheet?

1
google-apps-script
SpreadsheetApp works with standalone scripts but not getActive(). Try openById() – Cooper
@user2078674 At Sheets API, the sheet of the index 0 is used as the default, when the sheet name is not used to a1Notation. So when you want to use the Values.batchUpdate method for the sheet of the index 0, please modify from range: newestSheet+"!A2" to range: "A2". And remove the line of var newestSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getName(). – Tanaike
Thanks for the help everyone! All of these suggestions work! – user2078674

1 Answers

0
votes

Creating a new Spreadsheet and naming the first sheet

Here's a function that creates a new spreadsheet and names the first sheet in it with "Week:" following by the number of the week based upon the format string "ww".

Currently, it uses prompts for getting folder Id and Spreadsheet Name. You will probably want to change that to something that fits your needs more precisely.

Don't forget to enable Drive API V2.

function createSpreadsheetInFolder(){
  var ss=SpreadsheetApp.getActive();
  var ui=SpreadsheetApp.getUi();
  var resp1=ui.prompt('Parent Folder Id','Enter Parent Folder Id', ui.ButtonSet.OK_CANCEL);
  if(resp1.getSelectedButton()==ui.Button.OK && resp1.getResponseText()){
    var parentFolderId=resp1.getResponseText();
    var pfObj=Drive.Files.get(parentFolderId);
    var pfName=pfObj.title;
    var pfType=pfObj.mimeType;
    var pfId=pfObj.id;
    var resp2=ui.prompt('File Name', 'Enter File Name', ui.ButtonSet.OK_CANCEL);
    if(resp2.getSelectedButton()==ui.Button.OK && resp2.getResponseText()){
      var fileName=resp2.getResponseText();
      var res={
        "mimeType":"application/vnd.google-apps.spreadsheet" ,
        "title":fileName, 
        "ownedByMe": true,
        "parents":[{"id":parentFolderId}] 
      };
      try{
        var file=Drive.Files.insert(res);
      }
      catch(e){
        ui.alert(e);
      }
      var ss=SpreadsheetApp.openById(file.id);
      var sh=ss.getSheets()[0].setName(Utilities.formatString('Week: %s', Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "ww")));
    } 
  }
}