Di postingan sebelumnya di seri Pola Skrip Google Apps, kami melihat pembaruan baris data secara selektif di Google Sheet. Untuk posting ini kita akan melihat beberapa pola untuk memasukkan beberapa baris data ke dalam Google Sheets. Kami akan mulai dengan di situs Google Apps Script
function addProduct() {
var sheet = SpreadsheetApp.getActiveSheet();
sheet.appendRow(['Cotton Sweatshirt XL', 'css004']);
}
Ini akan menambahkan baris di bagian bawah lembar Anda dengan data
Kaus Katun XLcss004
Ini berguna untuk satu baris data karena menggabungkan penyisipan baris dan penambahan data. Jika Anda memiliki banyak baris maka untuk kinerja Anda harus menggunakan .setValues(). Untuk menggunakan .setValues()_ harus menyediakan array nilai 2D. Pola yang umum adalah mengulang data yang dikembalikan dan mendorong nilai ke dalam array lalu menulisnya ke sheet. Contoh menggunakan acara kalender ditunjukkan di bawah ini
function writeCalToSheet() {
var sheet = SpreadsheetApp.getActiveSheet();
var cal = CalendarApp.getDefaultCalendar();
var evnts = cal.getEvents(new Date("March 8, 2017"), new Date("March 14, 2018"));
var values = [];
// loop over the return events
for (var i=0; i < evnts.length; i++) {
var evnt = evnts[i];
// push a row of data as 2d array
values.push([evnt.getStartTime(), evnt.getEndTime(), evnt.getTitle(), evnt.getDescription()])
}
// sheet.insertRowsAfter(1, values.length); <- uncomment this if you want to insert blank rows at the top every time
// write data to sheet
sheet.getRange(sheet.getLastRow()+1, 1, values.length, values[0].length).setValues(values);
}
_
Untuk menulis data, kita perlu menentukan rentang ke .setValues(). Dalam contoh di atas menggunakan
function writeCalToSheet() {
var sheet = SpreadsheetApp.getActiveSheet();
var cal = CalendarApp.getDefaultCalendar();
var evnts = cal.getEvents(new Date("March 8, 2017"), new Date("March 14, 2018"));
var values = [];
// loop over the return events
for (var i=0; i < evnts.length; i++) {
var evnt = evnts[i];
// push a row of data as 2d array
values.push([evnt.getStartTime(), evnt.getEndTime(), evnt.getTitle(), evnt.getDescription()])
}
// sheet.insertRowsAfter(1, values.length); <- uncomment this if you want to insert blank rows at the top every time
// write data to sheet
sheet.getRange(sheet.getLastRow()+1, 1, values.length, values[0].length).setValues(values);
}
_0 untuk menambahkan data setelah baris terakhir dalam lembar. Dalam contoh parameter berikut digunakan
function writeCalToSheet() {
var sheet = SpreadsheetApp.getActiveSheet();
var cal = CalendarApp.getDefaultCalendar();
var evnts = cal.getEvents(new Date("March 8, 2017"), new Date("March 14, 2018"));
var values = [];
// loop over the return events
for (var i=0; i < evnts.length; i++) {
var evnt = evnts[i];
// push a row of data as 2d array
values.push([evnt.getStartTime(), evnt.getEndTime(), evnt.getTitle(), evnt.getDescription()])
}
// sheet.insertRowsAfter(1, values.length); <- uncomment this if you want to insert blank rows at the top every time
// write data to sheet
sheet.getRange(sheet.getLastRow()+1, 1, values.length, values[0].length).setValues(values);
}
1 = function writeCalToSheet() {
var sheet = SpreadsheetApp.getActiveSheet();
var cal = CalendarApp.getDefaultCalendar();
var evnts = cal.getEvents(new Date("March 8, 2017"), new Date("March 14, 2018"));
var values = [];
// loop over the return events
for (var i=0; i < evnts.length; i++) {
var evnt = evnts[i];
// push a row of data as 2d array
values.push([evnt.getStartTime(), evnt.getEndTime(), evnt.getTitle(), evnt.getDescription()])
}
// sheet.insertRowsAfter(1, values.length); <- uncomment this if you want to insert blank rows at the top every time
// write data to sheet
sheet.getRange(sheet.getLastRow()+1, 1, values.length, values[0].length).setValues(values);
}
2 – untuk mendapatkan baris terakhir dari data yang ada dan menambahkan satu untuk mendapatkan baris berikutnyafunction writeCalToSheet() {
var sheet = SpreadsheetApp.getActiveSheet();
var cal = CalendarApp.getDefaultCalendar();
var evnts = cal.getEvents(new Date("March 8, 2017"), new Date("March 14, 2018"));
var values = [];
// loop over the return events
for (var i=0; i < evnts.length; i++) {
var evnt = evnts[i];
// push a row of data as 2d array
values.push([evnt.getStartTime(), evnt.getEndTime(), evnt.getTitle(), evnt.getDescription()])
}
// sheet.insertRowsAfter(1, values.length); <- uncomment this if you want to insert blank rows at the top every time
// write data to sheet
sheet.getRange(sheet.getLastRow()+1, 1, values.length, values[0].length).setValues(values);
}
3 = function writeCalToSheet() {
var sheet = SpreadsheetApp.getActiveSheet();
var cal = CalendarApp.getDefaultCalendar();
var evnts = cal.getEvents(new Date("March 8, 2017"), new Date("March 14, 2018"));
var values = [];
// loop over the return events
for (var i=0; i < evnts.length; i++) {
var evnt = evnts[i];
// push a row of data as 2d array
values.push([evnt.getStartTime(), evnt.getEndTime(), evnt.getTitle(), evnt.getDescription()])
}
// sheet.insertRowsAfter(1, values.length); <- uncomment this if you want to insert blank rows at the top every time
// write data to sheet
sheet.getRange(sheet.getLastRow()+1, 1, values.length, values[0].length).setValues(values);
}
4 – kolom awal yang dalam hal ini 1 sama dengan kolom Afunction writeCalToSheet() {
var sheet = SpreadsheetApp.getActiveSheet();
var cal = CalendarApp.getDefaultCalendar();
var evnts = cal.getEvents(new Date("March 8, 2017"), new Date("March 14, 2018"));
var values = [];
// loop over the return events
for (var i=0; i < evnts.length; i++) {
var evnt = evnts[i];
// push a row of data as 2d array
values.push([evnt.getStartTime(), evnt.getEndTime(), evnt.getTitle(), evnt.getDescription()])
}
// sheet.insertRowsAfter(1, values.length); <- uncomment this if you want to insert blank rows at the top every time
// write data to sheet
sheet.getRange(sheet.getLastRow()+1, 1, values.length, values[0].length).setValues(values);
}
5 = function writeCalToSheet() {
var sheet = SpreadsheetApp.getActiveSheet();
var cal = CalendarApp.getDefaultCalendar();
var evnts = cal.getEvents(new Date("March 8, 2017"), new Date("March 14, 2018"));
var values = [];
// loop over the return events
for (var i=0; i < evnts.length; i++) {
var evnt = evnts[i];
// push a row of data as 2d array
values.push([evnt.getStartTime(), evnt.getEndTime(), evnt.getTitle(), evnt.getDescription()])
}
// sheet.insertRowsAfter(1, values.length); <- uncomment this if you want to insert blank rows at the top every time
// write data to sheet
sheet.getRange(sheet.getLastRow()+1, 1, values.length, values[0].length).setValues(values);
}
6 – panjang array nilai yang telah kita bangun untuk mendapatkan jumlah barisfunction writeCalToSheet() {
var sheet = SpreadsheetApp.getActiveSheet();
var cal = CalendarApp.getDefaultCalendar();
var evnts = cal.getEvents(new Date("March 8, 2017"), new Date("March 14, 2018"));
var values = [];
// loop over the return events
for (var i=0; i < evnts.length; i++) {
var evnt = evnts[i];
// push a row of data as 2d array
values.push([evnt.getStartTime(), evnt.getEndTime(), evnt.getTitle(), evnt.getDescription()])
}
// sheet.insertRowsAfter(1, values.length); <- uncomment this if you want to insert blank rows at the top every time
// write data to sheet
sheet.getRange(sheet.getLastRow()+1, 1, values.length, values[0].length).setValues(values);
}
7 = function writeCalToSheet() {
var sheet = SpreadsheetApp.getActiveSheet();
var cal = CalendarApp.getDefaultCalendar();
var evnts = cal.getEvents(new Date("March 8, 2017"), new Date("March 14, 2018"));
var values = [];
// loop over the return events
for (var i=0; i < evnts.length; i++) {
var evnt = evnts[i];
// push a row of data as 2d array
values.push([evnt.getStartTime(), evnt.getEndTime(), evnt.getTitle(), evnt.getDescription()])
}
// sheet.insertRowsAfter(1, values.length); <- uncomment this if you want to insert blank rows at the top every time
// write data to sheet
sheet.getRange(sheet.getLastRow()+1, 1, values.length, values[0].length).setValues(values);
}
8 – jumlah item di baris pertama nilai untuk mendapatkan jumlah kolom
Saat menggunakan .setValues() untuk menambahkan data setelah baris data terakhir, Anda tidak perlu khawatir menyisipkan baris kosong karena metode .setValues() akan menangani ini untuk kita. Jika Anda ingin memasukkan data baru setelah baris pertama, Anda dapat menambahkan/membatalkan komentar pada baris berikut
sheet.insertRowsAfter(1, values.length);
Ini menyisipkan jumlah baris yang setara dengan jumlah data yang kita dapatkan
Catatan. Cuplikan di atas menggunakan
sheet.insertRowsAfter(1, values.length);
1. Metode lain adalah
sheet.insertRowsAfter(1, values.length);
_2 tetapi ini akan menimbulkan kesalahan jika baris yang Anda coba tidak ada. Ini menjadi masalah jika Anda hanya memiliki baris tajuk dan Anda ingin menyisipkan baris setelahnya
Untuk menambahkan data setelah baris header, dengan asumsi ini adalah baris 1, Anda juga perlu memodifikasi
sheet.insertRowsAfter(1, values.length);
3 untuk memulai rentang di baris 2
sheet.getRange(2, 1, values.length, values[0].length).setValues(values);
Cukup sering saat memasukkan data Anda ingin memasukkan rumus sebagai bagian dari perhitungan baris. Ada beberapa cara untuk melakukannya dan jika Anda menulis data maka Anda dapat menyertakan rumus saat menggunakan .setValues(). Misalnya, jika Anda ingin menambahkan rumus yang menghitung selisih antara dua tanggal kalender, Anda dapat menggunakan garis
values.push([evnt.getStartTime(),
evnt.getEndTime(),
evnt.getTitle(),
evnt.getDescription(),
'=B'+(i+2)+' - A'+(i+2)]);
Sebagai bagian dari ini, kami menggunakan loop iterator untuk membuat referensi R1C1, yang bisa menjadi berantakan jika Anda menambahkan baris di akhir data Anda. Cara lain untuk melakukan ini adalah menggunakan
sheet.insertRowsAfter(1, values.length);
5 bawaan Google Sheets. Ada banyak tutorial untuk menggunakan ini, seperti yang ini dari Ben Collins, di luar sana dan saya tidak akan membahas lebih detail di sini, tetapi satu contoh untuk contoh kode pertama kita adalah menyertakan
sheet.insertRowsAfter(1, values.length);
6 di sel E2
Pola untuk membangun array .setValues()_ membuat beberapa asumsi termasuk urutan dan posisi kolom yang kita gunakan tetap tidak berubah. Dalam hal data dari layanan seperti
sheet.insertRowsAfter(1, values.length);
8 saya belum menemukan pola yang berguna, jadi jika Anda mengetahuinya, silakan bagikan
Bekerja dengan data terstruktur seperti JSON menciptakan peluang yang lebih baik untuk menangani data secara fleksibel. Banyak API menyediakan data dalam format JSON dan di bawah ini adalah contoh tipikal dari FAA Airport Delays Services API yang dapat Anda lihat di https. //jasa. faa. gov/airport/delays?format=json
[
{
"IATA":"DAL",
"name":"Dallas Love Field",
"delays":[
{
"reason":"TM Initiatives:MIT:WX",
"type":"General Arrival/Departure Delay"
}
],
"uri":"http://services.faa.gov/airport/DAL/status"
},
...
]
Pada contoh di atas terdapat nilai data yang kedalamannya lebih dari satu level. Karena sheet adalah 2D, kita perlu meratakan data. Bruce McPherson memiliki pola yang bagus untuk meratakan objek dengan sintaks dot, yang telah dibungkusnya dengan pustaka Flattener (kode sumber di Github). Bruce juga telah mengembangkan fungsionalitas skrip ini lebih lanjut dengan kelas Fiddler sebagai bagian dari perpustakaan cUseful ini dan memberikan beberapa contoh 'Fiddling API ke Google Sheets' (Saya tidak dapat melihat metode 'ratakan' di Fiddler sehingga Anda mungkin harus menggunakan . Jika Anda mencari pendekatan yang lebih atomik, pola di bawah ini adalah yang sering saya gunakan dalam proyek
Memperbarui. Skrip Google Apps memiliki runtime V8 baru yang memungkinkan penggunaan sintaksis JavaScript modern. Versi kode yang diperbarui dimuat dalam posting V8 yang direvisi
function writeDelays(){
var sheet = SpreadsheetApp.getActiveSheet();
// getting some data
var data_obj = UrlFetchApp.fetch('https://services.faa.gov/airport/delays?format=json');
var data = JSON.parse(data_obj.getContentText());
// flatten the json
var rows = data.map(function(d){
return flatten(d);
});
// insert new rows
sheet.insertRowsAfter(1, rows.length);
// getting our headers
var heads = sheet.getDataRange()
.offset(0, 0, 1)
.getValues()[0];
// convert object data into a 2d array
var tr = rows.map (function (row) {
return heads.map(function(cell){
return row[cell] || "";
});
});
// write result
sheet.getRange(2, 1, tr.length, tr[0].length).setValues(tr);
}
/**
* Flatten an object.
* https://stackoverflow.com/a/19101235/1027723
*
* @param {Object} data object to flatten
* @return {Object} the flattened result
*/
function flatten(data) {
var result = {};
function recurse (cur, prop) {
if (Object(cur) !== cur) {
result[prop] = cur;
} else if (Array.isArray(cur)) {
for(var i=0, l=cur.length; iDimulai dengan lembaran dengan tajuk seperti ini
IATAnamedelays_0_reasondelays_0_typeuri
Hasil dalam data yang ditulis seperti ini
Tidak seperti sintaks titik Bruce, saya lebih suka menggunakan garis bawah… itu hanya preferensi saya. Perlu juga dicatat bahwa karena API ini menyertakan penundaan sebagai larik objek saat diratakan, indeks larik digunakan untuk menunjukkan item sehingga untuk mencatat penundaan tambahan di bandara, saya perlu menyertakan kolom
sheet.insertRowsAfter(1, values.length);
9, sheet.getRange(2, 1, values.length, values[0].length).setValues(values);
0, dan seterusnya. Dalam contoh ini urutan kolom tidak penting selama baris header cocok dengan kunci JSON yang diratakan. Jika Anda memiliki pola sendiri untuk mencapai sesuatu yang serupa dengan cara yang lebih baik, silakan bagikan 🙂
Bagaimana cara menambahkan data secara otomatis di Google Sheets?
Gunakan isi otomatis untuk menyelesaikan rangkaian .
Di komputer Anda, buka spreadsheet di Google Spreadsheet
Di kolom atau baris, masukkan teks, angka, atau tanggal di setidaknya dua sel yang bersebelahan
Sorot sel. Anda akan melihat kotak biru kecil di pojok kanan bawah
Seret kotak biru sejumlah sel ke bawah atau ke seberang
Bagaimana cara memasukkan data ke Google Sheets menggunakan JavaScript?
$. ajax({ url. 'https. //spreadsheet. google. com/feeds/spreadsheets/private/full?alt=json-in-script&access_token=' + access_token, dataType. 'JSONP', sukses. function(data){ // gunakan spreadsheet } });
Bagaimana cara menambahkan data ke spreadsheet Google menggunakan API?
Contoh 1. Menghubungkan Google Spreadsheet ke Numbers API .
Langkah 1. Buka Lembar baru. .
Langkah 2. Buka editor Skrip Aplikasi. .
Langkah 3. Beri nama proyek Anda. .
Langkah 4. Tambahkan kode contoh API. .
Langkah 5. Jalankan fungsi Anda. .
Langkah 6. Otorisasi skrip Anda. .
Langkah 7. Lihat log. .
Langkah 8. Tambahkan data ke Lembar
Bagaimana cara menambahkan data ke Lembar Google menggunakan Python?
Inilah yang Anda perlukan untuk membuat koneksi Python ke Google Sheets. .
Piton 2. 6 atau lebih baru
Paket pip
API (Application Programming Interface) mengaktifkan proyek Google Cloud Platform
Kredensial otorisasi
Akun Google
