Office Scripts - dinamically renaming sheet

[catalincirjan]

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

 

[Norie]

Have you tried simply removing the " around 'denumire' on the last line?

 

[catalincirjan]

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]

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.

const denumire = new Date().toISOString().slice(0,10);

That should give you the current date in yyyy-mm-dd format.

 

[Norie]

Actually, this might be what's needed as I think Office Scripts uses TypeScript.

let denumire: Date = new Date();
console.log(denumire.toISOString().slice(0,10));

 

[catalincirjan]

Actually, this might be what's needed as I think Office Scripts uses TypeScript.

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]

Can you post the code you have now?

 

[catalincirjan]

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]

Oops, I should have posted code to actually rename the sheet not show the new name.

Try this.

let denumire: Date = new Date();

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

 

[catalincirjan]

Well, I feel like a noob not managing to put that togheter myself ....

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