Referenz: CCDS-Skriptbeispiel für Google Sheets

Prerequisites

Navigieren Sie zu Google Drive.
Wählen Sie
Neu
und dann
Datei-Upload
, um eine CSV-Datei in Ihr Google Drive hochzuladen. Beispiel: CCDS Demo.csv
Klicken Sie mit der rechten Maustaste auf die hochgeladene Datei und wählen Sie
Teilen.
Setzen Sie Ihre Teilen-Einstellungen im Bereich
Mit Personen und Gruppen teilen
auf "
Jeder mit dem Link
".
Rufen Sie die Google Doc-ID über den geteilten Link ab. Beispiel: In der Beispiel-URL
https://drive.google.com/file/d/1QZYX2345ri6789/view?usp=sharing
, Datei-ID ist
1QZYX2345ri6789
Verwenden Sie die Datei-ID Ihres Skripts. Beispiel:
var url = 'https://drive.google.com/uc?export=downloade&id=ENTER_FILE_ID_HERE';

Beispielskript für Google Sheets

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