how to put a message board on google sites

5/10/24 - I can't see the embedded spreadsheets on a smartphone screen anymore, just a black box, unless I tap the box first. I did some research and it is because I have the forms and sheets stored on and accessed from my Google Drive account, and in an update last year or the start of this year that capability was lost. I dunno, I'll probably get rid of all this eventually, since phones are over 50% of views. It isn't for profit and it doesn't cost me anything to maintain. So, I guess I could try having the forms and sheets on a different host site to see if the sheet doesn't appear by default as a black box on a phone screen. I still mean to sort how to just host my recipes on my own computer for family and whoever else stumbles over it, but my internet service provider blocks ports unless you cough up for a business plan, and I haven't gotten around to trying port mirroring and other alternatives. I guess I could try writing something to hide the black box as a "click here to view comments" and have that be interpreted as a screen tap on the sheet, but I dunno if I could.

For you guys coming in off the google sites message board link (and everybody else), below is a link to a video giving a good step by step of how to use google forms and sheets to have a message board. It doesn't autorefresh, you have to manually refresh to see a new submission. 

https://www.youtube.com/watch?v=DoXNDY7VIgY

Back in 2014 suddenly the form submissions didn't support formatting. 

The form submissions were coming onto the spreadsheet unformatted. They were missing word wrap, text color, horizontal and vertical alignment, all that. That is because in March 2014 google rolled out an update to sheets which didn't support that. All new sheets since that time lacked that capacity. Additionally, google intended to convert all old sheets to the new sheet version at an undetermined time. I checked November 2023 and it looks like the formatting is supported again, the below script I had written to fix it, leaving it in here for now. I also cleared out a bad link, and replaced it with the above video, skimmed it with no sound and it shows the right procedure. 

Credit to this guy for showing how to do it. It provides rich text formatting for form submissions on sheets. You can adjust it as you want to suit your needs. Here:

function richText() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var s = ss.getSheetByName('Form Responses 1');

  var r = s.getDataRange();

  r.setWrap(true);

  var blueHandleColumn = s.getRange(2, 2, 100, 1);

  blueHandleColumn.setFontColor('#0000FF');

  var bothColumns = s.getRange(2, 2, 100, 2);

  bothColumns.setBackground('#FFFFFF');

  bothColumns.setVerticalAlignment('top');

}

I wrote this with the assumption that you already have a google account, a site up that you want to put a message board on, and you've already created the linked sheets and forms. 

How to get this on a Google Sheet (2015)

1. Open up your spreadsheet.

2. Click 'tools' < 'script editor'

3. A new tab will pop up entitled 'Untitled project'. Go to that tab.

4. In the tab will be a box entitled 'Google Apps Script'. Click on 'Blank Project'.

5. Up will come a page with some stuff on it, an example code they provide. Delete it all out.

6. Paste in the code you are using.

7. Stop and double check that the name of the sheet on line three, in the example above 'Form Responses 1', matches the name of the sheet you are targeting.

8. Click 'File'.

9. On the drop down menu that comes up hit 'Save'.

10. A window will come up entitled 'Rename Project'. Name your project and press the "OK' button.

11. Now click 'Resources' < 'Current project's triggers'.

12. A new box will pop up called 'Current project's triggers.' Click on 'No triggers set up. Click here to add one now'.

13. There will be three different options to specify. Select the name of your project, 'From spreadsheet', and 'On form submit'. Click 'Save'.

14. A box will pop up saying 'Authorization required'. Click 'Continue'.

15. Another box will pop up entitled 'Request for Permission'. Click 'Accept'.

There you go, you should have the form submissions coming up on your spreadsheets as desired. If you want to set notification, then on the spreadsheet click 'Tools' < 'Notification Rules'. Then select the desired notification and click 'Save' and then 'Done'.

Hope this helps.

How to Adjust the Script

Here's a link to the Google Apps Script, Spreadsheet Service. It provides all the codes you can use with google sheets:

https://developers.google.com/apps-script/reference/spreadsheet/

I've linked all of the different bits to the specific location in the above site that you can find the relevant tutorial at. I've also included an explanation line by line of what the code does. This way you can see how to change the stuff in the example to make it how you want it.

function richText() {  //This is the start of the function you are writing. You usually give your function an explanatory name

var ss = SpreadsheetApp.getActiveSpreadsheet(); // I'm declaring the specific spreadsheet I'm targeting

var s = ss.getSheetByName('Form Responses 1'); // I'm declaring the specific sheet in the spreadsheet I'm targeting

var r = s.getDataRange(); // Declaring the specific part of the sheet being targeted (in this case none, so the whole sheet)

r.setWrap(true); // setting up word wrap

var blueHandleColumn = s.getRange(2, 2, 100, 1); // Declaring another specific part of the sheet I am targeting

blueHandleColumn.setFontColor('#0000FF'); // Setting font color

var bothColumns = s.getRange(2, 2, 100, 2); // Declaring another specific part of the sheet I am targetting

bothColumns.setBackground('#FFFFFF'); // Setting background color

bothColumns.setVerticalAlignment('top'); // Setting vertical alignment

} // This closes the function

** 5/7/17 I noticed that the google sheets script had stopped working. Turned out that permissions had to be run again was all. I opened the script up, hit 'run' to run the script, and redid the permissions. You have to do that it seems around once a year. 

Comments