4

I'm trying to get a script that I've made for a google sheet to trigger when the sheet is opened (since I've found you can't trigger them manually through the mobile app). Somehow, I've managed to get half the script to work, while the other half does not. The script is intended for use on both desktop and mobile, and I want it to clear a predetermined set of cell ranges while also hiding a predetermined set of rows and columns (for formatting purposes). The script runs perfectly on desktop, but the clearRanges portion of the script doesn't run on mobile even though the hideRows/Columns portion of the script does. Any ideas on why this might be? The function I'm using can be seen below:

function onOpen() {
//Create custom menu options
SpreadsheetApp.getUi().createMenu('Invoicing')
    .addItem("Clear Data", "clearRange")
    .addItem("Submit", "sendEmailWithPdfAttach")
    .addToUi()

//Clear ranges
var source = SpreadsheetApp.getActiveSpreadsheet();
source.getRange('A23:A40').clearContent();
source.getRange('H6:I6').clearContent();
source.getRange('H8:I8').clearContent();
source.getRange('F13:F13').setValue('FALSE');
source.getRange('F15:F15').setValue('TRUE');
source.getRange('D19:I19').clearContent();
source.getRange('D23:G23').clearContent();
source.getRange('D31:G31').clearContent();
source.getRange('A42:I44').clearContent();
source.getRange('B24:G25').clearContent();
source.getRange('B28:G29').clearContent();
source.getRange('B32:G33').clearContent();
source.getRange('B35:G40').clearContent();
source.getRange('H24:H29').clearContent();
source.getRange('H32:H33').clearContent();
source.getRange('H35:H39').clearContent();

//Hide rows/columns
var sheets = source.getSheets();
sheets.forEach(function (s, i) {
    if (i == sheetNum) {
        s.hideRows(45, 400);
        s.hideColumns(10, 17);
    } else {
        s.hideSheet();
    }
});    
}

The custom menu options are for desktop use and can be ignored. I will also say this is my first time really using Apps Script, and most of this code was taken from elsewhere and modified. If you guys have any clue as to what I can do, I'm all ears. Thanks

Kos
  • 4,890
  • 9
  • 38
  • 42
Zach S
  • 41
  • 1
  • 1
  • 2
  • Google Apps Scripts run on server side, so should work the same way on desktop/mobile/etc. I may assume problem is in something else – Kos Jun 17 '18 at 07:14
  • How are you triggering it from mobile. mobile only supports `onEdit()` – TheMaster Jun 17 '18 at 09:46
  • I guess it's possible that it isn't actually running at all, and that the rows and columns are hidden from previous use on desktop. What would be the best way to implement this using `onEdit()`? – Zach S Jun 17 '18 at 16:07
  • Please check [this](https://webapps.stackexchange.com/questions/87346/add-a-script-trigger-to-google-sheet-that-will-work-in-android-mobile-app). I guess it's solve the problem. – Péter Baráth Oct 08 '19 at 13:26
  • https://webapps.stackexchange.com/a/87361/27487 was very helpful for me. – Ryan May 11 '21 at 21:56
  • Does this answer your question? [Executing Google Apps Script Functions from Mobile App](https://stackoverflow.com/questions/33373826/executing-google-apps-script-functions-from-mobile-app) – Ryan Jul 09 '22 at 16:19

2 Answers2

4

SpreadsheetApp.getUi() doesn't work on Google Sheets mobile apps. The alternative is to create Android add-ons for Google Sheets. Unfortunately at this time there isn't support for other mobile platform add-ons for Google Sheets.

Related Q&A

Stack Overflow

Web Applications SE

Rubén
  • 34,714
  • 9
  • 70
  • 166
4

  • There is a simple solution to it, You can create a button on your main spreadsheet and link it with the script!

  • Open the same sheet on your Mobile Google Chrome

  • On the extreme Right Top corner, click on the three dots. A drop-down menu Opens
  • Select "View in Desktop Mode".
  • Notice, the button your Spreadsheet is now clickable, as the sheet interprets the user to be logged on to a PC
  • Now, Just Click on the button and let the script do its magic
Anshul Bansal
  • 41
  • 1
  • 2
  • 1
    It worked for me once but next time it only was opened by google drive app and as I have Firefox as the default browser on mobile.. Oh well..this other method helped me https://code.luasoftware.com/tutorials/google-apps-script/google-apps-script-click-event-trigger-on-mobile/ – miodf Feb 14 '20 at 09:50
  • 1
    @miodf, That Document though impressive is quite tedious and not exactly user-friendly. A Simple hack could be to just Open the sheet on your Default Browser (firefox), Click on three dots, Then Select Page Option. A new Menu Opens. Now select "Add Page to Shortcut". This options adds the link like an App on your phone. So you no longer need to Open the same App on Drive – Anshul Bansal Feb 29 '20 at 05:36
  • Thanks for your help. ;) I have tried your solution. Alas it didn't work for me (as I can't edit cells or run GAS from butons) maybe because I have implemented my suggestion with functions?? Here are a few tips that may help others (nothing guaranteed!) : Copy/paste the Google Drive url (https://drive.google.com/drive/my-drive ) and find your Google sheet from it (or copy the direct Google sheet url into a new gmail email from my pc and then paste it into Firefox (or Chrome)) ... – miodf Feb 29 '20 at 09:50
  • ...Force resheshing the browser cache : Press reload button (or long pressing on the reload button) in Firefox. Or in Chrome https://android.stackexchange.com/a/207511/144093 Hope this helps someone. ;) – miodf Feb 29 '20 at 09:50