0
422
VisualizaçõesAppScript - 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 Respostas
Responde à pergunta0
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:
