Tetap teratur dengan koleksi
Simpan dan kategorikan konten berdasarkan preferensi Anda.
Layanan BigQuery memungkinkan Anda menggunakan
Google BigQuery API di Apps Script. API ini
memberi pengguna kemampuan untuk mengelola project BigQuery, mengupload data baru,
dan menjalankan kueri.
Referensi
Untuk mengetahui informasi mendetail tentang layanan ini, lihat
dokumentasi referensi untuk BigQuery API.
Seperti semua layanan lanjutan di Apps Script, layanan BigQuery menggunakan
objek, metode, dan parameter yang sama dengan API publik. Untuk mengetahui informasi selengkapnya, lihat Cara menentukan tanda tangan metode.
/** * Runs a BigQuery query and logs the results in a spreadsheet. */functionrunQuery(){// Replace this value with the project ID listed in the Google// Cloud Platform project.constprojectId='XXXXXXXX';constrequest={// TODO (developer) - Replace query with yoursquery:'SELECT refresh_date AS Day, term AS Top_Term, rank '+'FROM `bigquery-public-data.google_trends.top_terms` '+'WHERE rank = 1 '+'AND refresh_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 2 WEEK) '+'GROUP BY Day, Top_Term, rank '+'ORDER BY Day DESC;',useLegacySql:false};letqueryResults=BigQuery.Jobs.query(request,projectId);constjobId=queryResults.jobReference.jobId;// Check on status of the Query Job.letsleepTimeMs=500;while(!queryResults.jobComplete){Utilities.sleep(sleepTimeMs);sleepTimeMs*=2;queryResults=BigQuery.Jobs.getQueryResults(projectId,jobId);}// Get all the rows of results.letrows=queryResults.rows;while(queryResults.pageToken){queryResults=BigQuery.Jobs.getQueryResults(projectId,jobId,{pageToken:queryResults.pageToken});rows=rows.concat(queryResults.rows);}if(!rows){console.log('No rows returned.');return;}constspreadsheet=SpreadsheetApp.create('BigQuery Results');constsheet=spreadsheet.getActiveSheet();// Append the headers.constheaders=queryResults.schema.fields.map(function(field){returnfield.name;});sheet.appendRow(headers);// Append the results.constdata=newArray(rows.length);for(leti=0;i < rows.length;i++){constcols=rows[i].f;data[i]=newArray(cols.length);for(letj=0;j < cols.length;j++){data[i][j]=cols[j].v;}}sheet.getRange(2,1,rows.length,headers.length).setValues(data);console.log('Results spreadsheet created: %s',spreadsheet.getUrl());}
Memuat data CSV
Contoh ini membuat tabel baru dan memuat file CSV dari Google Drive ke dalamnya.
/** * Loads a CSV into BigQuery */functionloadCsv(){// Replace this value with the project ID listed in the Google// Cloud Platform project.constprojectId='XXXXXXXX';// Create a dataset in the BigQuery UI (https://bigquery.cloud.google.com)// and enter its ID below.constdatasetId='YYYYYYYY';// Sample CSV file of Google Trends data conforming to the schema below.// https://docs.google.com/file/d/0BwzA1Orbvy5WMXFLaTR1Z1p2UDg/editconstcsvFileId='0BwzA1Orbvy5WMXFLaTR1Z1p2UDg';// Create the table.consttableId='pets_'+newDate().getTime();lettable={tableReference:{projectId:projectId,datasetId:datasetId,tableId:tableId},schema:{fields:[{name:'week',type:'STRING'},{name:'cat',type:'INTEGER'},{name:'dog',type:'INTEGER'},{name:'bird',type:'INTEGER'}]}};try{table=BigQuery.Tables.insert(table,projectId,datasetId);console.log('Table created: %s',table.id);}catch(err){console.log('unable to create table');}// Load CSV data from Drive and convert to the correct format for upload.constfile=DriveApp.getFileById(csvFileId);constdata=file.getBlob().setContentType('application/octet-stream');// Create the data upload job.constjob={configuration:{load:{destinationTable:{projectId:projectId,datasetId:datasetId,tableId:tableId},skipLeadingRows:1}}};try{constjobResult=BigQuery.Jobs.insert(job,projectId,data);console.log(`Load job started. Status: ${jobResult.status.state}`);}catch(err){console.log('unable to insert job');}}
[[["Mudah dipahami","easyToUnderstand","thumb-up"],["Memecahkan masalah saya","solvedMyProblem","thumb-up"],["Lainnya","otherUp","thumb-up"]],[["Informasi yang saya butuhkan tidak ada","missingTheInformationINeed","thumb-down"],["Terlalu rumit/langkahnya terlalu banyak","tooComplicatedTooManySteps","thumb-down"],["Sudah usang","outOfDate","thumb-down"],["Masalah terjemahan","translationIssue","thumb-down"],["Masalah kode / contoh","samplesCodeIssue","thumb-down"],["Lainnya","otherDown","thumb-down"]],["Terakhir diperbarui pada 2025-08-31 UTC."],[[["\u003cp\u003eThe BigQuery service in Apps Script enables management of BigQuery projects, data uploads, and query execution using the Google BigQuery API.\u003c/p\u003e\n"],["\u003cp\u003eThis advanced service requires prior enabling before use and leverages the same structure as the public API.\u003c/p\u003e\n"],["\u003cp\u003eSample code is provided demonstrating how to run a query to retrieve Google Search terms and load CSV data into BigQuery.\u003c/p\u003e\n"],["\u003cp\u003eUsers can consult the Google Cloud support guide for troubleshooting and support related to the BigQuery service.\u003c/p\u003e\n"]]],[],null,[]]