Profile

Cover photo
Ashley Moat
10,729 views
AboutPostsPhotosVideos

Stream

Ashley Moat

Problem/Coding Help  - 
 
Help needed!

I have added a menu button on my spreadsheet that when pressed, the active cell and its row of data will move to my other sheet. 

I cannot get this to work. Please can someone help me. 

Many thanks
3
1
Ashley Moat's profile photozoome i.am.'s profile photoJoris Steurs's profile photo
10 comments
 
sheet.deleteRow(activeRowNumber)
Add a comment...

Ashley Moat

Discussion  - 
 
 
Can someone point me in the right direction. Im after a step by step guide to build a sidebar add-on that displays certain cell values from a sheet I have. 

I have a workbook that has 6 sheets. I want to display data from sheet1 on the sidebar so I can see it even when Im working on the other sheets. 

I am new to Script, so could really do with a step by step guide. 
6 comments on original post
1
Steve Webster's profile photo
 
Not sure if you checked out these resources:
https://developers.google.com/apps-script/add-ons/

https://sites.google.com/site/scriptsexamples/learn-by-example/add-on-example-mail-importer-for-docs

Here is some code to get you started:
//@OnlyCurrentDoc
function onInstall() { onOpen(); } 
function onOpen() {
  SpreadsheetApp.getUi()
  .createAddonMenu()
  .addItem('Open the Sidebar','showSidebar')
  .addToUi();
}

function showSidebar() {
  var userInterface = HtmlService.createTemplateFromFile('Client-Side')
  .evaluate()
  .setSandboxMode(HtmlService.SandboxMode.IFRAME)
  .setTitle('Remove Blank Rows');
  SpreadsheetApp.getUi().showSidebar(userInterface);
}
Add a comment...

Ashley Moat

Problem/Coding Help  - 
 
I have a onformsubmit script

I have a formula (var ref = "=B2-1+ROW()-0") which is added to a cell in the row thats just been added

sheet.getRange(lastRow, getColIndexByName("Ref")).setValue(ref);

This gives the cell a unique value. 

The onformsubmit script then sends an email. I want the value of the cell after the formula has done its bit to put in email. I cant get it to work. Can I not put a formula in a cell and get the formula result in the same script.  Hope this makes sense
1
Andrew Roberts's profile photoA. Luca B's profile photo
2 comments
 
Consider how many rows will your sheet end with. Thousands of formulae can be heavy
Add a comment...

Ashley Moat

Problem/Coding Help  - 
 
Thank you community for all your support. Im learning so much here alone. 

My next issue

I have created a function that does the following;
MailApp.sendEmail(userEmail, subject, body, {name:"Help Desk"});

I have defined the variables in this function aswell, just not copied in this post.  

What I want is to have an IF function that will run the above if the selected cell contains the word 'CLOSED' otherwise to display a msgbox . 

I think Im close but cant get it to work.

Thanks
1
Tárcio Sales's profile photoAshley Moat's profile photoAndrew Roberts's profile photo
6 comments
 
The one gotcha with using Browser.msgBox() is that is although it clears the execution transcript in the editor, making debug a little trickier
Add a comment...

Ashley Moat

Problem/Coding Help  - 
 
i want to create a sidebar app in google sheets that shows data from my spreadsheet. I have a summary sheet which brings in data from other sheets in the workbook. I want the sidebar to replace the summary sheet so I can see the info which ever sheet i am using. 

Does this make sense?
Is it possible?

I have never used Google Script before, and really want to create something. 
1
Bryan Patterson's profile photoSpencer Easton's profile photojason peel's profile photoAshley Moat's profile photo
4 comments
 
Hi Jason

Are you able to provide me with some links to website that can point me in right direction. I am completely new to this and really want to learn but have no idea where to start.

Thanks again. It sounds like you have done exactly what I want 
Add a comment...

Ashley Moat

Tips & Tricks  - 
 
I have a html sidebar in Google Sheets that I want to display values from a cell in my workbook. 

I have some script that gets the value from the worksheet. I have no idea how to display this in the html table. 

The code.gs script to get value from Cell D4 is;
function returnCellValue() {
    var ss = SpreadsheetApp.getActiveSpreadsheet(); 
    var sheet = ss.getSheetByName("Home");
    var value = sheet.getRange(4,3).getValue()
]

Can someone help me with html code to get the value on my html sidebar.. Thanks
1
Jesse Spevack's profile photoAshley Moat's profile photoBjorn Behrendt (EdListen)'s profile photo
3 comments
Add a comment...

Ashley Moat

Problem/Coding Help  - 
 
Can someone point me in the right direction. Im after a step by step guide to build a sidebar add-on that displays certain cell values from a sheet I have. 

I have a workbook that has 6 sheets. I want to display data from sheet1 on the sidebar so I can see it even when Im working on the other sheets. 

I am new to Script, so could really do with a step by step guide. 
1
1
Steve Webster's profile photojason peel's profile photoAshley Moat's profile photo
6 comments
 
appreciate that +jason peel 
Add a comment...

Ashley Moat

Problem/Coding Help  - 
 
I have a form that enters data onto a spreadsheet. I then have a column in the spreadsheet that I want to put a unique number in each time a form is submitted. Anyone help me with a script to do this onformsubmit. 

I have manually entered the 1st 5 entries as 00001,00002,00003,00004,00005. I want to automatically carry this on. 

I cant do it!
1
Stephen Gale's profile photoA. Luca B's profile photo
3 comments
 
I think the problem is unicity of the id not padding. OnFormSumits randomically is unreliable so i try to avoid to calculate ids on events.
Add a comment...

Ashley Moat

Problem/Coding Help  - 
 
I am currently writing a script to send me an email when a user submits a form. I have got it working but want to add a hyperlink to my spreadsheet in the email. this is what I have so far. Can someone help me with the last line of code to add a hyperlink to my spreadsheet?

function formsubmitconfirm(e) {
  var userEmail = "myemailaddress";
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
 sheet.getRange(lastRow,getColIndexByName("Status")).setValue("New");
var numNew = 0;
  for (var i = 2; i < lastRow; i++) {
    if (sheet.getRange(i, getColIndexByName("Status")).getValue() == "New") {
      numNew++;
    }
  }
  MailApp.sendEmail(userEmail,
                    "Line1",
                    "Line2. \n\nClick " +
                    "below to see the entry." +
                    "/n/n/HYPERLINK ADDRESS ",
                    {name:"myname"}); 
}
1
1
Mike Treacy's profile photoAshley Moat's profile photoKathryn van Nieuwkerk's profile photoAndrew Hyland's profile photo
7 comments
 
Indeed you do +Ashley Moat​ :)
Add a comment...