Hi @Tu_Bui I think you may be using an older version of the script since I removed the toUpperCase function from the script. This is the latest Coda->GSheets script, mind trying this version and see if you’re still running into errors?
@Al_Chen_Coda awesome let me know because it seems it only take the filtered table atm.
As for the deleting rows, I mean in google sheet to keep the row no matter what and juste update it if value changes. If the rows get deleted in Coda, it keep the row in google shit as archive.
Thanks,
@Marc_Fitt I see the way the script is set up, the Google Sheet will always stay in sync with your Coda table (so if you delete a row in Coda, it will also get deleted in Google Sheets). One workaround is to just delete the “Coda Source Row UR” column in your Google Sheet and I believe that should prevent the script from deleting anything on your Google Sheet.
@Al_Chen_Coda okay but will it update the row or create a new row every time if I delete the code source row ur?
Thanks,
hi there,
No longer see the UpperCase error but still getting:
The starting column of the range is too small. (line 38, file “Code”)
The data still seems to sync though.
@Marc_Fitt Ah not it won’t. If you’re comfortable editing the script, you could just delete the parts of the script that relate to deleting rows. Then the script will only update and add new rows to Google Sheets, but won’t delete anything.
@Tu_Bui this might be because your don’t have a source row URL column your Google Sheet. The column name you have for the source row URL (spelling and case) have to match exactly what you put in the script for the TARGET_SHEET_SOURCE_ROW_COLUMN variable.
1 Like
It worked flawlessly now. I thought we do not need that column for sync from coda to gsheet so I didn’t input.
Thanks a lot!
1 Like
Hi Al,
I followed your video tutorial about syncing data one-way from Google Sheets to a Coda Doc using GAS, and it “worked” (the data made it into my Coda table), but I received an error message:
> Exception: Unexpected error while getting the method or property openById on object SpreadsheetApp. (line 27, file "Code")
In addition, nothing appeared in the Coda Source Row URL column in my Google Sheet.
Any suggestions? (I’m a total beginner to all of this, so please don’t assume I know anything technical about GAS 
)
Thanks!
@Al_Chen_Coda, I discovered my error: I hadn’t included enough of the SOURCE_SHEET_ID. I corrected that, and the error went away. Alas, I then received this error:
Exception: Unexpected error: https://coda.io/apis/v1/docs/KYu49HH6MV/tables/grid-UXtnT9EeLh (line 409, file “Coda”)
Any ideas on how I can get past this?
Based on the error it looks like you didn’t paste the proper Doc ID into the script. The doc ID is everything after the “_d” in the URL of the doc. You can read the tutorial and/or watch the video to see an example of this.
@Al_Chen_Coda, that solved it! Can you answer another question for me?
The table that is syncing from Sheets to Coda is a little under 3000 rows. For some reason, the Coda Source Row URL only filled out for the first 1000 rows though all the data made it to Coda. Is it just a matter of time before the remaining Coda Source Row URLs are filled out? Or did my table exceed a limit of some sort?
Thanks,
David
@David_Lawrence I think sometimes it doesn’t copy over all the source row URLs when you have thousands of rows. I haven’t figured out why it doesn’t copy over all the time, but would try running the script a few times to see if all the URLs copy over.
@Al_Chen_Coda, I have a much smaller spreadsheet that I am synching (130 rows), and it seems to have synched properly, but I continue to get the following error:
TypeError: Cannot read property '0' of undefined (line 103, file "Code")
Any idea what could be causing this?
Hi @David_Lawrence not really sure that’s a pretty generic error. I can help debug further if you share a screenshot of your Google Sheet and/or Coda doc. I would double check you’ve matched all the columns in terms of spelling, capitalization, and there aren’t any leading or trailing spaces in the column names.
Hi @Al_Chen_Coda , First of all i would like to say how much i appreciate that you have shared this information and shared so much. Your script is the exact thing i need atm and it works like a charm. Thank you for sharing it with the community .
I have a question regarding the script though. I currently using the “Coda to Google Sheets” script and I’m trying to add a code that will delete all Coda doc rows after coping them to the Google Sheets. Unfortunately, i have limited understanding of what is going on in the script and would appreciate some help. Here is what i have so far in AddDelete function. (FYI i commented out the Delete from Sheets part as i need the script to save an archived rows to the Sheets and then remove them from Coda doc).
// Add rows to Sheets only if new rows exist
if (sourceRows.length != 0) {
Logger.log('::::: Adding %s new rows from Coda => "%s" in Google Sheets...', sourceRows.length, TARGET_WORKSHEET_NAME);
var sortedSourceRows = sortCodaTableCols(sourceRows)
var convertedSourceRows = convertValues(sortedSourceRows);
toWorksheet.getRange(toWorksheet.getLastRow() + 1, 1, convertedSourceRows.length, convertedSourceRows[0].length).setValues(convertedSourceRows)
// =======================================
// Remove deleted rows
var deleteTheseCodaRows = [];
allRows['sourceRows'].map(function(row) {
deleteTheseCodaRows.push(row['cells'].slice(-2)[0]['value'])
})
var body = {
'rowIds': deleteTheseCodaRows,
};
CodaAPI.deleteRows(SOURCE_DOC_ID, SOURCE_TABLE_ID, body);
// =======================================
}
NVM, got it working with this (might look messy, but at least it works):
// =======================================
// Remove deleted rows
var deleteThisCodaRows = [];
const regexRowID = /[^\/]*$/;
allRows['sourceRows'].map(function(row) {
rowLink = row['cells'].slice(-1)[0]['value'];
deleteThisCodaRows.push(regexRowID.exec(rowLink)[0].slice(3))
})
var body = {
'rowIds': deleteThisCodaRows,
};
CodaAPI.deleteRows(SOURCE_DOC_ID, SOURCE_TABLE_ID, body);
// =======================================
2 Likes
Thank you for your wonderful work! I’m able to get the Google Sheets to Coda sync working. However, something about the Script causes this error to appear:
Exception: You do not have permission to call SpreadsheetApp.openById. Required permissions: https://www.googleapis.com/auth/spreadsheets (line 27)
When you click on that URL, it doesn’t exist. When I delete the code, everything returns to normal.
Hi @agoodman, I’ve never seen this error before but it sounds like you may need to re-authorize your Google account with Google Apps Script. There may also be issues with your Google Apps Script project manifest according to this post. Hope this helps!
Thanks! I kept reading that it was this line that is causing the hiccup:
Spreadsheet = SpreadsheetApp.openById(SOURCE_SHEET_ID);
When I commented it out, it broke your code. However, as soon as I deleted your code, everything began to work again. I checked permissions and saw that I had two requests from Google Sheets to Coda. Deleting one got it to execute on the backend but then it still broke my Google Sheet. 
I’ll keep tinkering and let you know if I figure it out! Thanks again.