Creating a new sheet in Google Sheets and renaming new sheet

Good day,

I've been trying to get my app to create a new sheet in a Google sheet, but for the life of me I can't seem to get it to run/trigger. Not really sure what I'm doing wrong but I think that it's not triggering correctly from the AI2. I've tried so many different way to implement this that I'm really not sure which direction to go.

OK, so here is what I would like to happen:

I'm currently using TIMAI2's fix for HK's CRUD app. Now I would like to add additional information to a selected ID. The selection is made from the listview containing the different IDs. So my idea is/was that the user selects the ID they want to add the additional info to and clicks on a button Add ADD. info - this opens to a new arrangement where additional info can be entered. Upon opening the new arrangement a new sheet needs to be created in the spreadsheet so that this additional info can be re-called at a later stage. The new sheet, I thought, could then also be renamed to the selected ID. eg. selected ID 001 - new sheet name 001.

Seeing as this might be way above my level I decided to first only have a new sheet be created, however unfortunately it is not.

I would really appreciate any assistance with this.

I apologize in advance for the simplistic nature of my script, after trying all of the "more advanced" methods I decided to try the simplest method as shown in the Google apps script references.

HKFix_KRP2NYT.aia (10.6 KB)
HKFixData.csv (944 Bytes)

Just to be clear, do you want to create a new sheet(tab/grid) in your existing spreadsheet, or do you want to create an entirely new spreadsheet ?

Just want a new sheet with in the spreadsheet. So yes a new tab in the existing spreadsheet.

1 Like

This apps script code I have previously used to create a new sheet(tab/grid) in an existing spreadsheet:

//insert a new grid, with name, to a specified sheet
function insertGridToSheet(name,sheetID) {
  var ss = SpreadsheetApp.openById(sheetID);
  var shs = ss.getSheets();
  ss.insertSheet(name,shs.length + 1);
  return ContentService.createTextOutput(name + ' added');
}

This function is called as follows:

//add new grid to sheet
  if ( e.parameter.func == "newGridForSheet" ) {
  return insertGridToSheet(e.parameter.name,e.parameter.sheetID);
  }

you will need to supply:

  1. The "func" selector
  2. The spreadsheet ID
  3. The name for the new sheet(tab/grid)

The new sheet(tab/grid) is placed as the last sheet(tab/grid)

Thank you very much I'll give this a try.

Just a sort of follow up question. As explained above, I would like to have the new sheet/tab named after the selected ID. Using your HKFix app as an example, if I add a global variable SHEETNAME2 which has an empty text block, and I then set global SHEETNAME2 to ListView1 selection, and then reference SHEETNAME2 in the script code you just provided instead of 'name'. Would that change the new sheet/tab name to the selected list ID?

Just to be clear, do you want to change the name of the sheet(tab/grid) after you have created it, or only on creation ?

The rename/naming should take place upon creation.

I've added the suggested script code. It does however not seem to work. I'm thinking that FN/func selector/trigger is incorrectly setup on AI2.
TIMAI2 Script add

You do not show your web component blocks for the addSheet function ?

The actual function needs to be outside of the doGet(e) / doPost(e) (whichever you are using), but the if statement does need to be in it.

Ooops didn't realize I'd need web blocks. Then again in hindsight it makes sense. (Sorry still learning)

So I fiddled about with it hoping I might be doing the right thing or at least heading in the right direction, but it seems not.

Blocks add2

This is what I've added to the current blocks in the hopes that this might in some way work. Not sure really if I've done this correctly.

image

needs to be &SHEETNAME2= swap the ? for an &

and of course you will need to add a Web1.GET block to actually send the data :wink:

Thanks for the assistance, I've made your suggested changes. But unfortunately it is still not creating the new sheet. I suspect I'm still missing something somewhere.

Hi Seniors,
Also want a new sheet with in the spreadsheet. followed above steps but not success.
kindly guide.

Either an issue with your script, blocks or spreadsheet. Show everything, and check all of this first:

Hi TIMAI2,
good day...!

can you make block for me. using this script

SCRIPT

function doGet(e) {
  var spreadsheet = SpreadsheetApp.openById('<YOUR SHEET ID HERE>');
  var newSheet = spreadsheet.insertSheet();
  return ContentService.createTextOutput('Created a new sheet: ' + newSheet.getName());
}

BLOCKS

image

thanks, need blocks for below mentioned script
image

What have you tried ?

I have created 3 buttons.

Button1: insert new sheet + copy paste data from other sheet to new created sheet (range B2:D33)

Button2: post text in new created sheet (range A33) from Textbox1

And last Button3:
Get / read data from new created sheet.

All is done. But data read from active sheet not from new created sheet. Example active sheet is sheet3 and new created sheet is sheet4.
When I pressed button3 data read from active sheet (sheet3) instead of new created sheet (sheet4)

Please show your relevant blocks and google apps script...

It sounds like you need to be setting the correct sheetname in your get url