AppScript - Writing to a specific cell

user_19293897 0
Viewed 47

I am taking my first steps in any sort of coding language, been proficient with Excel/Sheets for a while and looking to start automating a few small things to make mine and my teams' life a little easier. Currently I have successfully written it to copy my Template sheet and rename the template to the selected name from a dropdown menu on a tab called "Create Sheet" (Cell J21) but I am struggling with getting it to rename cell B1 on the new sheet to the selected name as well, here is what I have for copying the template. I tried a few things I found through Google and YouTube last night for also writing to B1 on the copies of Template but was unsuccessful.

function CreateSheet() {
  
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var TemplateSheet = ss.getSheetByName('Template');
  var newSheet = TemplateSheet.copyTo(ss);
  var SheetName = ss.getRange("J21").getValue();

  // Makes the new sheet the active sheet and renames it to the name in cell 'Create Sheet'J21 //
  ss.setActiveSheet(newSheet);
  ss.renameActiveSheet(SheetName);


  // Renames cell B1 on the new sheet to the name in 'Create Sheet'J21 //
  // ** this is where I am needing help **
google-sheets javascript
1 Answers

If you want to modify the value of B1 of the newly created sheet with the value of 'Create Sheet'!J21 on the same spreadsheet, then you can use this:

let createSheetJ21 = ss.getSheetByName('Create Sheet').getRange('J21').getValue();
ss.getSheetByName(newSheet).getRange('B1').setValue(createSheetJ21);

But since you already set the newSheet as the active one, you can use getActiveSheet alternatively.

ss.getActiveSheet().getRange('B1').setValue(createSheetJ21);

You can also use the whole A1 Notation on getRange (including the sheet name), see sample below:

let createSheetJ21 = ss.getRange('Create Sheet!J21').getValue();
// Append `!B1` to newSheet variable   
ss.getRange(newSheet + '!B1').setValue(createSheetJ21);

EDIT:

  • Integrating it with your current script, it should look like the one below. This contains some modifications/optimizations since there are some misunderstandings on your current one. See comments below:
function createSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const templateSheet = ss.getSheetByName('Template');
  const createSheet = ss.getSheetByName('Create Sheet');
  const sheetName = createSheet.getRange('J21').getValue();
  
  // No need to assign since copyTo doesn't return anything
  // By default, this creates a sheet 'Copy of <name of sheet copied>'
  templateSheet.copyTo(ss);

  // setActiveSheet takes Sheet object as input (not sheetName)
  // we need to create Sheet object first using its default name 'Copy of <name of sheet copied>'
  const createdSheet = ss.getSheetByName('Copy of Template');
  ss.setActiveSheet(createdSheet);
  ss.renameActiveSheet(sheetName);
  
  // setValue the cells
  createdSheet.getRange('B1').setValue(sheetName);
}

Sample:

sample

Output:

output

Reference:

user_161186860
Related