Referentie: CCDS-scriptvoorbeeld Google Spreadsheets

Prerequisites

Navigeer naar uw Google Drive.
Selecteer
Nieuw
en kies
Bestand uploaden
om een CSV-bestand naar uw Google Drive te uploaden. Voorbeeld: CCDS Demo.csv
Klik met de rechtermuisknop op het geüploade bestand en selecteer
Delen.
Stel uw instellingen voor delen in op
Iedereen met de koppeling
in het
deelvenster Delen met mensen en groepen
.
Haal de Google-document-ID op via de gedeelde koppeling. Voorbeeld: in de voorbeeld-URL
https://drive.google.com/file/d/1QZYX2345ri6789/view?usp=sharing
, bestands-ID is
1QZYX2345ri6789
Gebruik de bestands-ID uw script. Voorbeeld:
var url = 'https://drive.google.com/uc?export=downloade&id=ENTER_FILE_ID_HERE';

Voorbeeldscript voor Google Spreadsheets

var tableName = 'Csv Example';
//Make sure you adjust the settings to allow people to download this file from your google drive
var url = 'https://drive.google.com/uc?export=downloade&id=ENTER_FILE_ID_HERE';

/* Example cvs file:
----------------------------------------------
Heading A,Heading B,Heading C
A1,B1,C1
A2,B2,C2
A3,B3,C3
----------------------------------------------
*/


//Create an HTTPS request to send to the Google Drive API using the callApi function. 

function callApi() {
    var method = 'GET';
    var body = '';
    var headers = null;
    //Construct and send an HTTPS request to get all of the tables.
    return ai.https.request(url, method, body, headers);
}


function testConnection(context)
{       
    var response = callApi();
    var responseCode = response.getHttpCode();
    // Interrogate the response to check if it succeeded and that HTTP communication was successful.
    if (responseCode == 200)
    {
        ai.log.logInfo('Test Connection Succeeded');        
        return true;
    }
    else
    {
        ai.log.logError('Test Connection Failed','Response code was ' + responseCode);
        return false;
    }
}    
    
function importStructure(context)
{
    //Use the callAPI function to construct and send an HTTPS request to get all the tables 
    var response = callApi();
    //Get the structure builder object used to create tables, columns and other needed structures.
    var builder = context.getStructureBuilder();
    var table = createTable(builder, tableName);
    var lines = extractLines(response);
    var headingLine = lines[0];
    var headings = extractCells(headingLine);
    //parse the HTTPS response to construct a table
    for(var i = 0; i < headings.length; i++) {
        var heading = headings[i];
        createColumn(table, heading);
    }
}

function importData(context)
{
    var response = callApi();
    //Use the passed-in contextual information to create a rowset object.
    var rowset = context.getRowset();   
    var lines = extractLines(response);
    //Skip the heading row and process each row to extract cell values for each column.
    for(var i = 1; i < lines.length; i++) {
        var line = lines[i];
        if(line === '') continue;
        var cells = extractCells(line);   
        //Add each row array to the rowset.
        rowset.addRow(cells);
    }
}
    
function createColumn(table, heading) {
    var columnId = escape(heading);
    var column = table.addColumn(columnId);
    column.setDisplayName(heading);
    column.setTextColumnType(1000);
}

function extractLines(response) {
    var content = response.getBody();
    return content.split('\n');
}

function extractCells(line) {
    return line.split(',');
}

function createTable(builder, name) {
    var tableId = escape(name);
    var table = builder.addTable(tableId);
    table.setDisplayName(name);
    return table;
}

//In this example, reuse the import data function with context for previewing the data. 
//A separate function might be needed to limit the amount of data preview gets back.
function previewData(context)
{
    importData(context);
}