Convert the spreadsheet to CSV and upload it to Cloud Storage with Cloud Functions

Target

After hitting the API endpoint, export the data of a specific spreadsheet to CSV and save it in storage.

• Premise of data that can be published without problems

What to use

• Google Apps Script
• Python
• Cloud Storage
• Cloud Functions

procedure

1. Publish the spreadsheet as a web application

Select Tools-> <> Script Editor "from the spreadsheet toolbar to open the Google Apps Script editor.

Delete the existing code and paste the code below.

code.gs

function doGet(e) {
  var output = ContentService.createTextOutput(createCSV());
  output.setMimeType(ContentService.MimeType.TEXT);
  return output;
}

function createCSV() {
  var sheetName = 'Sheet name';
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  var values = sheet.getDataRange().getValues();
  var csv = values.join('\n');
  return csv;
}

Click "Publish"-> "Introduce as Web Application ..." on the toolbar.

You will be asked for the project name, so give it an appropriate name and click "OK".

You will be asked for the settings. Set "Execute the app as:" to "Me (email address)" and "Who has access to the app:" to "Anyone, even anonymous" and click "Deploy".

The following screen will be displayed. If you access the URL described in "Current web app URL" and the spreadsheet data is displayed in CSV format, it is successful.

Make a note of this URL as you will use it later.

2. Grant Cloud Storage permission to create objects

• Detailed setting explanation is omitted.

Open Service Accounts in IAM and Administration and click Create Service Account to create a service account.

Open Storage and click Create Bucket to create a bucket.

On the bucket list screen, click "Edit bucket permissions"-> "Add member" from the menu to the right of the bucket.

Add the service account you just created to the new member, select the role, select Cloud Storage → Storage Object Administrator, and click Save.

3. Place a script to upload CSV to Cloud Functions

Open Cloud Functions and click Create Function.

Check "Allow unauthorized calls".

Select Python 3.7 for the runtime. Paste the following code into "MAIN.PY" and "REQUIREMENT.TXT".

main.py

import sys
import csv
import codecs
import requests
import os
import tempfile

from google.cloud import storage


def main(request):
    # Spreadsheet Web application URL
    url = '1.URL displayed in'

    # Cloud Storage
    bucket = 'Bucket name'
    dir = 'Direction name'
    fileName = 'csv name'

    # get data
    r = requests.get(url)
    assert r.status_code == requests.codes.ok, "can't read spreadsheet"  # pylint: disable=no-member

    # make file
    _, temp_local_filename = tempfile.mkstemp()
    with codecs.open(temp_local_filename, 'w', 'utf_8') as f:
        f.write(r.text)

    # upload file
    client = storage.Client()
    bucket = client.get_bucket(bucket)
    blob = bucket.blob(f'{dir}/{fileName}')
    blob.upload_from_filename(filename=temp_local_filename)
    return "success"

REQUIREMENT>TXT

astroid==2.4.0
autopep8==1.5.2
cachetools==4.1.0
certifi==2020.4.5.1
chardet==3.0.4
colorama==0.4.3
google-api-core==1.17.0
google-auth==1.14.1
google-cloud==0.34.0
google-cloud-core==1.3.0
google-cloud-storage==1.28.0
google-resumable-media==0.5.0
googleapis-common-protos==1.51.0
idna==2.9
isort==4.3.21
lazy-object-proxy==1.4.3
mccabe==0.6.1
protobuf==3.11.3
pyasn1==0.4.8
pyasn1-modules==0.2.8
pycodestyle==2.5.0
pylint==2.5.0
pytz==2020.1
requests==2.23.0
rsa==4.0
six==1.14.0
toml==0.10.0
urllib3==1.25.9
wrapt==1.12.1

Set "Function to execute" to "main" and click "Create".

After that, click the function name → hit the URL written in the "Trigger" tab and the CSV will be written out and it will be successful.

reference

-How to run Google Spreadsheet as a simple web server and receive WebHook easily -[GAS] Create and save a CSV file from a specific spreadsheet -[Python] Try to work with Cloud Functions and Cloud Storage