Google docs

Page dedicated to Google office apps (Docs, Sheets...)

Google Sheets

• Example of range: A1:B2, Sheet3!A1:B2, B:B (one column), Sheet3!2:2 (one row on another sheet)
• Conditional formatting: cannot use data from another sheet!

Tips

Create buttons in Android

• [1], [2],
• Button doesn't work.
• Instead create cells with dropdown list (Data -> Validation -> Select from list of items), then add a onEdit event in Script:

   /**
    * Trigger to simulator button actions using a dropdown list on Android.
    */
   function onEdit(e) {
     if (e.range.getA1Notation() == 'A1') {
       if (e.value == 'Reset') {
         resetWarnings();
         e.range.setValue("(action)");
       }
       if (e.value == 'Clean') {
         cleanWarnings();
         e.range.setValue("(action)");
       }
     }
   }

Create sheets from Python

Link: https://blog.coupler.io/python-to-google-sheets/

Install the following modules (eg. pip install -r requirements.txt):

google-api-python-client==1.7.9
google-auth-httplib2==0.0.3
google-auth-oauthlib==0.4.0

Create a file auth.py

# auth.py
from __future__ import print_function
from googleapiclient.discovery import build 
from google.oauth2 import service_account
SCOPES = [
'https://www.googleapis.com/auth/spreadsheets',
'https://www.googleapis.com/auth/drive'
]
credentials = service_account.Credentials.from_service_account_file('credentials.json', scopes=SCOPES)
spreadsheet_service = build('sheets', 'v4', credentials=credentials)
drive_service = build('drive', 'v3', credentials=credentials)

Example of use:

from __future__ import print_function
from auth import spreadsheet_service
from auth import drive_service
def create():
    spreadsheet_details = {
    'properties': {
        'title': 'Python-google-sheets-demo'
        }
    }
    sheet = spreadsheet_service.spreadsheets().create(body=spreadsheet_details,
                                    fields='spreadsheetId').execute()
    sheetId = sheet.get('spreadsheetId')
    print('Spreadsheet ID: {0}'.format(sheetId))
    permission1 = {
    'type': 'user',
    'role': 'writer',
    'emailAddress': 'godwinekuma@gmail.com'
    }
    drive_service.permissions().create(fileId=sheetId, body=permission1).execute()
    return sheetId
def read_range():
    range_name = 'Sheet1!A1:H1'
    spreadsheet_id = '1JCEHwIa4ZzwAiKGmAnWGfbjeVCH_tWZF6MkIU0zICwM'
    result = spreadsheet_service.spreadsheets().values().get(
    spreadsheetId=spreadsheet_id, range=range_name).execute()
    rows = result.get('values', [])
    print('{0} rows retrieved.'.format(len(rows)))
    print('{0} rows retrieved.'.format(rows))
    return rows
def write_range():
    spreadsheet_id = create()
    range_name = 'Sheet1!A1:H1'
    values = read_range()
    value_input_option = 'USER_ENTERED'
    body = {
        'values': values
    }
    result = spreadsheet_service.spreadsheets().values().update(
        spreadsheetId=spreadsheet_id, range=range_name,
        valueInputOption=value_input_option, body=body).execute()
    print('{0} cells updated.'.format(result.get('updatedCells')))
write_range()

Google Apps Script

• Google Apps Script Reference
• Beginner guides:

• Extending Google Sheets (Google)
• Custom functions (Google)
• Custom Menu (Google)
• BenCollins, Ben Collins, Google Sheet buttons

• Date / Number

• Date and Number Formats
• To set date in a cell (with date format): sheet.getRange(1,2).setValue(new Date());
• To set date as a string [3]: var date = Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy")