HI
I am trying to create a script that will update a google sheets document using a csv file.
the update should only insert the rows that have a later date than the date in the last row of the sheets document.
I do not know where to start with this so any help will be appreciated.
David
Script to update google sheets using a CSV file
-
- 3StarLounger
- Posts: 248
- Joined: 05 Nov 2012, 19:40
-
- Administrator
- Posts: 79526
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Script to update google sheets using a CSV file
I'm afraid I don't know anything about Google Apps Scripts.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 631
- Joined: 27 Jun 2021, 10:46
Re: Script to update google sheets using a CSV file
Starting with some assumptions
1. You know your fileIDs
2 Date is in first column
3 CSV file does not have a header row
Then something like the following Google Apps script:
Note I am not a google apps script expert by any means, and credit for the core of this lies with ChatGPT
1. You know your fileIDs
2 Date is in first column
3 CSV file does not have a header row
Then something like the following Google Apps script:
Code: Select all
function updateGoogleSheetWithCSV() {
// Set your CSV file ID and Google Sheets ID
const csvFileId = '1gvpPXvM8mnum5GN4SEU3Jg99EGfrRCxy'; // Replace with your CSV file ID
const sheetId = '1L2HR4GWqO9Txy1omrAyDeDkzK9H45HIW8KUGmWZ4XYk'; // Replace with your Google Sheets ID
// Open the CSV file and read its content
const csvFile = DriveApp.getFileById(csvFileId);
const csvData = Utilities.parseCsv(csvFile.getBlob().getDataAsString());
// Logger.log(csvData)
// Open the Google Sheets document
const sheet = SpreadsheetApp.openById(sheetId).getActiveSheet();
// Get the last row in the existing sheet
const lastRow = sheet.getLastRow();
const lastDate = lastRow > 0 ? new Date(sheet.getRange(lastRow, 1).getValue()) : null; // Assuming dates are in the first column
// Iterate through the CSV data and check dates
const rowsToInsert = [];
for (let i = 0; i < csvData.length; i++) { // Skip header row
const row = csvData[i];
const rowDate = new Date(row[0]); // Assuming the date is in the first column of the CSV
// Check if the row date is later than the last date in the sheet
if (lastDate === null || rowDate > lastDate) {
rowsToInsert.push(row);
}
}
// Insert the new rows into the Google Sheet
if (rowsToInsert.length > 0) {
sheet.getRange(lastRow + 1, 1, rowsToInsert.length, rowsToInsert[0].length).setValues(rowsToInsert);
} else {
Logger.log("No new rows to insert.");
}
}