Office Scripts - dinamically renaming sheet

catalincirjan

New Member
Joined
Aug 28, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
Hello,

On a sharepoint excel I have used the record option to create small script that will copy the content of a specific sheet -> create a new one -> paste -> rename the sheet
On the online version, only Office Scrips work. Even if I upload a file that has a VBA macro, once uploaded it 'dissapears'.


I have tried to make the renaming of the sheet dinamically, the name being the date ( "December 9th" would have been ideal, but any format is better then none). No success so for.

Can anybody make any suggestions, please? Currently it just puts the string 'denumire' in there
----------------------------------------------------------------------------------------------------------------------------

function main(workbook: ExcelScript.Workbook) {

// Add a new worksheet

let sheet1 = workbook.addWorksheet();

let tracks = workbook.getWorksheet("Tracks");

// Paste range at Sheet1!A1 from Tracks!1:1048576

sheet1.getRange("A1")

.copyFrom(tracks.getRange("1:1048576"), ExcelScript.RangeCopyType.all, false, false);

// Rename worksheet dinamically?!

let denumire: string;

denumire: Date;

sheet1.setName("denumire");
}
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,278
Office Version
  1. 365
Platform
  1. Windows
Have you tried simply removing the " around 'denumire' on the last line?
 

catalincirjan

New Member
Joined
Aug 28, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
Line 11: Worksheet setName: The argument is invalid or missing or has an incorrect format

When I hover over the command (method) ExcelScript.Worksheet.setName(name: string): void
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,278
Office Version
  1. 365
Platform
  1. Windows
Have you checked what the value of denumire is?

If you want it to be the current date I would expect to see something more like this.
VBA Code:
const denumire = new Date().toISOString().slice(0,10);
That should give you the current date in yyyy-mm-dd format.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,278
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Actually, this might be what's needed as I think Office Scripts uses TypeScript.
VBA Code:
let denumire: Date = new Date();
console.log(denumire.toISOString().slice(0,10));
 

catalincirjan

New Member
Joined
Aug 28, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
Actually, this might be what's needed as I think Office Scripts uses TypeScript.
VBA Code:
let denumire: Date = new Date();
console.log(denumire.toISOString().slice(0,10));
Yes, this i can see in the output the current date.
The thing that still doesn't work is the naming of the new sheet. Only when I put something between ' ' does it accept the input as being the correct format.

Is ther another command that might be used for the renaming of the sheet?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,278
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Can you post the code you have now?
 

catalincirjan

New Member
Joined
Aug 28, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
It's the one above, with the changes suggested above:
------------------------------------------------------------------------

function main(workbook: ExcelScript.Workbook) {

// Add a new worksheet

let sheet1 = workbook.addWorksheet();

let tracks = workbook.getWorksheet("Tracks");

// Paste range at Sheet1!A1 from Tracks!1:1048576

sheet1.getRange("A1")

.copyFrom(tracks.getRange("1:1048576"), ExcelScript.RangeCopyType.all, false, false);

// Rename worksheet to "November 5th"

let denumire: Date = new Date();

console.log(denumire.toISOString().slice(0,10));

sheet1.setName(denumire);

}
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,278
Office Version
  1. 365
Platform
  1. Windows
Oops, I should have posted code to actually rename the sheet not show the new name.

Try this.
VBA Code:
let denumire: Date = new Date();

sheet1.setName(denumire.toISOString().slice(0,10));
 
Solution

catalincirjan

New Member
Joined
Aug 28, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
Well, I feel like a noob not managing to put that togheter myself ....

Thank you very much, Norie! It works perfect :D
 

Watch MrExcel Video

Forum statistics

Threads
1,129,370
Messages
5,635,864
Members
416,886
Latest member
coreyalaurence37

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top