Google docs

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

Links

Developer:

• https://console.cloud.google.com/

Also provides API reference documentation.

Wiki

• See also tips on Libre office

References

• ARRAYFORMULA in Google Sheets

See also Array formula intro, and an example

Reference

Get Python API doc in a file

Reference API are listed on google developer site, but not easy to browse. We can get them from Python client:

>>> import contextlib
>>> def write_help(func, out_file):
...     with open(out_file, 'w') as f:
...         with contextlib.redirect_stdout(f):
...             help(func)
... 
>>> write_help(spreadsheet_service.spreadsheets().get, 'get.txt')
>>> write_help(spreadsheet_service.spreadsheets().batchUpdate, 'batchUpdate.txt')

Then we can do things like:

egrep "^          [^ ]" batchUpdate.txt

To get an overview of the batchUpdate commands.

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

Convert string to number

Simply use unary operator +:

var num="1234"
console.log(+num)

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)");
       }
     }
   }

Download a remote file using a cookie

Here an example using royaleapi.com (however it fails because royaleapi refuses connection from google)

function importClanWarsRoyaleAPI() {
    // Provide the full URL of the CSV file.
  var csvUrl = "https://royaleapi.com/clan/YYVQ099G/war/race/csv";
  var options =
    {
      headers : { 
        Cookie: "__royaleapi_session=******************************", 
        "User-Agent": "GoogleSheets (googleappscript)",
        Accept: "*/*",
        "Accept-Encoding": "identity",
        Connection: "Keep-Alive"
      },
      muteHttpExceptions  : true,
      contentType : "text/csv",
      validateHttpsCertificates : false
    };
  var response = UrlFetchApp.getRequest(csvUrl, options);
  Logger.log(response);
  // var csvContent = UrlFetchApp.fetch(csvUrl, options).getContentText();
  var csvContent = UrlFetchApp.fetch(csvUrl, options);
  Logger.log(csvContent);
  // var csvData = Utilities.parseCsv(csvContent);

  // var sheet = SpreadsheetApp.getActive().getSheetByName("test");
  // sheet.getRange(2, 1, csvData.length, csvData[0].length).setValues(csvData);
}

Import a Google Drive CSV as a sheet

✐	this doesn't work well and is quite slow. Best is to use Google sheet API from Python

Method 1 - Worst

• Use =IMPORTDATA("https://drive.google.com/uc?export=download&id=KEY_DRIVE_FILE")
• ... but doesn't work well. Often breaks on update, etc.

Method 2 - Use Google Sheet API

• For instance:

function importDriveCSV() {
  fileId='*********************************' // Put Google Drive file id here
  var data = DriveApp.getFileById(fileId).getBlob().getDataAsString();
  var values = Utilities.parseCsv(data)

  // print the values into the range starting at row, col
  var sheet = SpreadsheetApp.getActive().getSheetByName("test");
  sheet.getRange(2, 1, values.length, values[0].length).setValues(values);
}

Method 3 - Import any file as a sheet

function importClanWarsWeek(day) {
  // Provide the full URL of the CSV file.
  var csvContent = UrlFetchApp.fetch("https://some.server.org/test.csv").getContentText();
  var csvData = Utilities.parseCsv(csvContent);

  var sheet = SpreadsheetApp.getActive().getSheetByName("test");
  sheet.clear();
  sheet.getRange(2, 1, csvData.length, csvData[0].length).setValues(csvData);
  // var info = [ [ "année", yearContent, "semaine", weekContent, "jour", ""+day, "heure", tsContent ] ]
  sheet.getRange(1, 1, info.length, info[0].length).setValues(info)
}

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', [])

    # To read the formulas, do something like:
    # result = spreadsheet_service.spreadsheets().values().batchGet(
    #         spreadsheetId=spreadsheet_id, 
    #         ranges=range_name, 
    #         valueRenderOption='FORMULA').execute()
    # rows = result['valueRanges'][0].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()

Trigger Apps Script from Python

• Source: medium.com

First Get project number and enable Scripts API in Google Cloud Platform

• Go to Google Cloud Platform dashboard for the project, https://console.cloud.google.com/home/dashboard.
• Write down the Project number .
• Go to three-dotted menu on the top left, APIs & Services, then Library and search for script. Select Apps Script API. Click Enable.

Now, go to the Apps script editor containing the script we want to execute.

• Click Project Settings icon, and in Google Cloud Platform Project, click Change project. Enter the project number above.
• Then click the blue button Deploy on the top right, and select New deployment.
• Click Select type, and choose API Executable, set a description and access rights, then click Deploy.
• Write down the Deployment ID. This will be the API ID to use in Python script below
• If necessary, go to Overview icon, and write down the Project OAuth Scopes.

# Here, do authorisation for the given scopes.
# ...
service = build('script', 'v1', credentials=creds)

API_ID = "YOUR_DEPLOYMENT_ID___NOT_SCRIPT_ID" 
body = {"function": "macro_test"} 

try:
    response = service.scripts().run(body=body, scriptId=API_ID).execute()
except errors.HttpError as error:
    # The API encountered a problem.
    print(error.content)

Return the last match in LOOKUP

By default LOOKUP and alike (VLOOKUP, HLOOKUP) only returns the first match.

To return the LAST match, we need to sort the array first [3]:

=LOOKUP(E2, SORT(B2:B11), SORT(A2:A11,B2:B11,TRUE))

Highlights formulas / non-formulas

We can use the conditional formatting to highlight which cells are a formula or not a formula with test =ISFORMULA(A1) or =NOT(ISFORMULA(A1)).

Advanced criteria in countif, sumif...

COUNTIF(A1:A10,"Pass")             // Match with a string
COUNTIF(A1:A10,B1)                 // Match with a cell
COUNTIF(A1:A10,"<>*Pass*")         // Test with a string
COUNTIF(A1:A10,"<"&50)             // Test with a numeric value
COUNTIF(A1:A10,"<"&B1)             // Test with another cell

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
• formatDate
• To set date in a cell (with date format): sheet.getRange(1,2).setValue(new Date());
• To set date as a string [4]: var date = Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy")
• We can get date value from python, with delta = datetime.datetime.now() - datetime.datetime.strptime('1899-12-31','%Y-%m-%d'); delta.days