Creating a new sheet in Google Sheets and renaming new sheet

MIT App Inventor Help
, ,
1

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)

2

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 ?

3

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

1 Like
4

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)

5

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?

6

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 ?

7

The rename/naming should take place upon creation.

8

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

Blocks add
Blocks add555×702 41.3 KB

9

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.

10

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.

11

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:

12

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.