Create a new sheet within an existing spreadsheet google sheets

Colin Grant

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.

JSmith

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Copy value and format from a sheet to a new google Spreadsheet document?

use onedit() trigger on a specific sheet within google scripts for google sheets

How To Use Google Sheets API v4 To Create New Sheet or Tab in Spreadsheet with PHP

Google Sheets API: Create or move Spreadsheet into Folder

How to dynamically reference another sheet within a cell in Google sheets

Add (Create) new sheet in spreadsheet using Google Sheet API

setActiveCell on all sheets within a spreadsheet. Google Apps Script

How to call a specific sheet within a spreadsheet via the Google Sheets API v4 in Python

How to add a new Sheet into existing SpreadSheet? Google Sheets API with Node.js

How to create a new row at the end of an existing sheet in Google Spreadsheets after completing a Google Form?

Google Sheets - Sending email based on specific sheet in a spreadsheet

How to copy spreadsheet's sheet into another spreadsheet without creating a new sheet copy when using google sheet script?

Coldfusion with spreadsheet to clone an existing sheet as a template for a new sheet

Creating new sheet using Apache POI removes all the existing sheets

Unable to create a new spreadsheet with the Google Sheets API

Auto Sort not working on Multiple Sheets within one Google Sheet

How to append to a Sheet within a Spreadsheet

create new sheet in excel using existing macro

Google Sheets onEdit - trying to copy a row to a new sheet by changing the value of a column within that same row

Google Sheets Hyperlink to Dynamic Field Within a Given Sheet

Google Sheet (Script) copy Sheet to another SpreadSheet and name new sheet in target SS

Google App Script: Google sheets addon is not enabled for the new spreadsheet by default

Function to search for a sheet in another spreadsheet in Google Sheets Scripts

Google App Script Copy a single sheet and create a new spreadsheet in specific folder

Google Script to copy all csv files in a GDrive Folder as new sheets in existing 'master' Google sheet

How do I reference a sheet in google sheets by using name of the sheet name from a cell within the main sheet?

How to upload a CSV file to a new tab(sheet) in the google spreadsheet

GOOGLE SHEET FORMULA spreadsheet

Create new spreadsheet in Google Drive Picker