CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Colin Grant
Colin Grant

Reputation: 7

Create a new sheet within an existing spreadsheet google sheets

Here is my third (and hopefully last) question for my current project.

The IF ELSE statement doesn't seem to be working properly in the following code:

function onFormSubmit() {
  // onFormSubmit
  // get submitted data
  var ss = 
SpreadsheetApp.openById("1UMSTyjYz2DMubBT54Q7a0V5ie5TAPYFZ2riqV5p93gE");
  var sheet = ss.getSheetByName("Submissions");
  var row = sheet.getLastRow();
  var Col = sheet.getLastColumn();
  var headings = sheet.getRange(1,1,1,Col).getValues();
  var lastRow = sheet.getRange(row, 1, 1, Col);
  var UnitNumber = sheet.getRange(row,3,Col).getValue(); 
  var newSheet = sheet.getRange(row,4,Col).getValue();
  // check if username has sheet

  if(ss.getSheetByName(UnitNumber)){
    var DrillSheet = ss.insertSheet(UnitNumber);
    // if not make
  } else {
  var DrillSheet = SpreadsheetApp.create(UnitNumber); 
DrillSheet.getSheetByName('Sheet1').getRange(1,1,1,Col).setValues(headings);
  }
  // Rename sheet to submission date
  DrillSheet.renameActiveSheet(newSheet);
  // copy submitted data to Drill sheet
  DrillSheet.appendRow(lastRow.getValues()[0]);
  DrillSheet.appendRow(['=CONCATENATE(B6," ",B5)']);
  DrillSheet.appendRow(['=TRANSPOSE(B1:2)']);
  //Hide top rows with raw data
  var hiderange = DrillSheet.getRange("A1:A3");
  DrillSheet.hideRow(hiderange);
  //Widen columns
  DrillSheet.setColumnWidth(1,390);
  DrillSheet.setColumnWidth(2,700);
}

The goal here is to create a new spreadsheet with the title 'UnitNumber' and sheet title as the submission date IF that spreadsheet doesn't already exist. If the 'UnitNumber' spreadsheet DOES exist, I would like to create a new sheet within that spreadsheet with the title as the submission date.

Currently it is creating a new spreadsheet every time.

Upvotes: 0

Views: 277

Answers (3)

Colin Grant
Colin Grant

Reputation: 7

Here is the fixed code that works as I intended. Much thanks to @JSmith for helping me through - I could not have done this without you! 

function onFormSubmit() {
  // onFormSubmit
  // get submitted data and set variables
  var ss = SpreadsheetApp.openById("some id");
  var sheet = ss.getSheetByName("Submissions");
  var row = sheet.getLastRow();
  var Col = sheet.getLastColumn();
  var headings = sheet.getRange(1, 1, 1, Col).getValues();
  var lastRow = sheet.getRange(row, 1, 1, Col);
  var UnitNumber = sheet.getRange(row, 3).getValue(); 
  var newSheet = sheet.getRange(row, 4, Col).getValue();
  var fileExist = false;
  var drillSheet = null;
  var folder = DriveApp.getFoldersByName("Fraser Drill Inspections").next();
  var files = folder.getFilesByName(UnitNumber);
  var file = null;
  // check if Drill has sheet
  while (files.hasNext()) {
    fileExist = true;
    file = files.next();
    break;
  }
  if (fileExist) //If spreadsheet exists, insert new sheet
  {
    drillSheet = SpreadsheetApp.openById(file.getId());
    drillSheet.insertSheet("" + newSheet);
  }
  else //create new spreadsheet if one doesn't exist
  {
    drillSheet = SpreadsheetApp.create(UnitNumber);
    var ssID = drillSheet.getId();
    file = DriveApp.getFileById(ssID).makeCopy(UnitNumber, folder);
    DriveApp.getFileById(ssID).setTrashed(true);
    drillSheet = SpreadsheetApp.openById(file.getId());
    drillSheet.renameActiveSheet(newSheet);
  }
  // copy submitted data to Drill sheet
  drillSheet.getSheetByName(newSheet).getRange(1, 1, 1, Col).setValues(headings);
  drillSheet.appendRow(lastRow.getValues()[0]);
  drillSheet.appendRow(['=CONCATENATE(B6, " ", B5)']);
  drillSheet.appendRow(['=TRANSPOSE(B1:2)']);
  //Hide top rows with raw data
  var hiderange = drillSheet.getRange("A1:A3");
  drillSheet.hideRow(hiderange);
  //Widen columns
  drillSheet.setColumnWidth(1, 390);
  drillSheet.setColumnWidth(2, 700);
}

Upvotes: 0

JSmith
JSmith

Reputation: 4808

After seeing the problem with you try this code:

function onFormSubmit() {
  var ss = SpreadsheetApp.openById("1UMSTyjYz2DMubBT54Q7a0V5ie5TAPYFZ2riqV5p93gE");
  var sheet = ss.getSheetByName("Submissions");
  var row = sheet.getLastRow();
  var Col = sheet.getLastColumn();
  var headings = sheet.getRange(1,1,1,Col).getValues();
  var lastRow = sheet.getRange(row, 1, 1, Col);
  var UnitNumber = sheet.getRange(row,3).getValue(); 

  var fileExist = false;
  var drillSheet = null;
  var folder = DriveApp.getFoldersByName("nameOfTheFolder").next();
  var files = folder.getFilesByName(UnitNumber);
  var file = null;
  while (files.hasNext())
  {
    fileExist = true;
    file = files.next();
    break;
  }
  if (fileExist)
  {
    drillSheet = SpreadsheetApp.openById(file.getId());
    drillSheet.renameActiveSheet("randomString") ;
  }
  else
  {
    drillSheet = SpreadsheetApp.create(UnitNumber);
    var ssID = drillSheet.getId();
    file = DriveApp.getFileById(ssID);
    file = file.makeCopy(UnitNumber, folder);
    DriveApp.getFileById(ssID).setTrashed(true);
    drillSheet = SpreadsheetApp.openById(file.getId());
    drillSheet.renameActiveSheet("randomString");
  }
}

Upvotes: 0

Incerto13
Incerto13

Reputation: 1

To answer your last question, once you create a folder and share (the entire folder) with a list of people, any file that you subsequently place in that folder will automatically be shared with those people.

Upvotes: 0

Related Questions