gspreadsheet-importer

| ⚠️ This repository has been archived and will no longer be maintained. Thanks for all the stars, help and brainstorms! |

Import csv files to specified google spreadsheet automatically

Lisence

MIT

REQUIREMENT

• node version 14 or later

INSTALL

Clone this script

git clone git@github.com:codeforjapan/gspreadsheet-importer.git
cd gspreadsheet-importer

Clone and install clasp, login

clasp login will open browser and ask your permissions.

yarn install
clasp login
clasp create [YOUR_PROJECT_NAME] --type standalone  # select `standalone` if you need to select

If you don't have permission of using App Script API, you will see below message.

Error: Permission denied. Enable the Apps Script API:
https://script.google.com/home/usersettings

Open the URL and allow the permission.

Set script ID and push latest code

Open .clasp.json and copy scriptID value, then delete appscript.json. Copy .clasp.json.sample to .clasp.json and replace the YOUR_SCRIPT_ID by the copied scriptId.

rm appsscript.json
cp .clasp.json.sample .clasp.json # overwrite file
vi .clasp.json

Push latest code. Please answer y to confirmation of overwriting manifest.

% clasp push
? Manifest file has been updated. Do you want to push and overwrite? Yes
└─ src/Code.ts
└─ src/appsscript.json
Pushed 2 files.

Create Google Cloud Project and set the ID

Goto Google Cloud Platform Console and create new project.

Enable Google Drive API on the project

Please find Google Drive API from search box and enable it.

Copy project ID and set it to the script

Then copy Project number and Project ID. (You will use Project ID later).

Open GAS page by executing clasp open and set the copied Project number by selecting the menu Resources -> Cloud Platform Project

Paste the copied project ID and press Set Project button.

Create agreement page

If you do this first time the system shows link to creating OAuth consent screen. Please create it from the shown link. (Internal user type is OK. Just set application name and save.)

After that, back to the google apps script page and press Set Project button again.

You will see below message. Now you can close this window.

Set project ID

Then, please set the previously copied Project ID by running clasp apis list.

% clasp apis list
Open this link: https://script.google.com/d/.../edit

Go to *Resource > Cloud Platform Project...* and copy your projectId
(including "project-id-")
? What is your GCP projectId? [ENTER YOUR ID]

Create credential and set

Then create credential

clasp open --creds

The command will open new browser, then please create new credential of OAuth client ID by＋CREATE CREDENTIALS link, The application type should be Desktop App, any name is OK.

After creating the credential, please download the credential data, then locate it as .cred.json at this project root holder.

mv ~/Downloads/client_secret_xxxxxx.apps.googleusercontent.com.json ./.cred.json

Then login using the credential file. Below command will open browser to allow permissions again.

clasp login --creds .creds.json

Deploy Web API

Open GAS window and publish the script as a web api from publish -> Deploy as web app.

clasp open

setup deploymant ID

Get deployment ID by running clasp deployments and copy the ID of web app meta-version.

% clasp deployments
2 Deployments.
- AKfycbw8ryblmM3eNxe0_...... @HEAD
- AKfycbw3395mg1mABA1YS...... @1 - web app meta-version # ← Copy this ID

Then craete .env file for the deployment

echo 'DEPLOYMENT_ID="[THE_DEPLOYMENT_ID]"' > .env

initialize

You can initialize this script by running init function.

% clasp run init # it will require
Running in dev mode.
No response.

It will create new drive named gspreadsheet-importer on your root drive.

test

You can run GAS function by using clasp run. Please run test function by running this command.

clasp run test

This will import csv file from this site: https://mynumbercard.code4japan.org/ .

Please open your drive and confirm the files are imported. You can get Google Drive URL by blow command:

% clasp run getInitialDriveID
Running in dev mode.
https://drive.google.com/drive/folders/1WdDDE.....

Write your own script

Please implement sync() method in the Code.ts file. You can run this function by running clasp run sync.

Publish the source from next time

If you changed the script and wants to apply the changes, please run:

make push