0
423
ViewsAppScript - Writing to a specific cell
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 **
1 answers
Answer question0
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:
Output:
