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

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)

Google Apps Script

Google Apps Script Reference
Beginner guides:

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 [3]: 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

Google docs

Contents

Links

Reference

Get Python API doc in a file

Google Sheets

Tips

Convert string to number

Create buttons in Android

Download a remote file using a cookie

Import a Google Drive CSV as a sheet

Create sheets from Python

Trigger Apps Script from Python

Google Apps Script

Navigation menu

Google docs

Links

Reference

Get Python API doc in a file

Google Sheets

Tips

Convert string to number

Create buttons in Android

Download a remote file using a cookie

Import a Google Drive CSV as a sheet

Create sheets from Python

Trigger Apps Script from Python

Google Apps Script

Navigation menu

Search